Evaluate Scalar Values and Fetch a Portion of Data
- 4 minutes to read
Custom business logic may involve a limited number of business class properties or a limited number of business objects. You may need to evaluate a scalar value calculated at the database level without fetching real objects. To improve the performance and memory consumption, it’s possible to fetch business class property value and calculated values needed for the task.
Use LINQ
To fetch data in small portions or evaluate scalar values, use the IObjectSpace.GetObjectsQuery method to get an IQueryable<T> object and apply a LINQ expression to it.
Get a Portion of Data From a Database
You can fetch only a necessary number of first records. To fetch them, use the Take method.
IQueryable<Product> query = this.ObjectSpace.GetObjectsQuery<Product>();
List<Product> top500 = query.Take(500).ToList();
Get Only Certain Properties from a Database
To fetch only particular properties of your business class, use Projection Operations to specify what properties you need to fetch.
The following example fetches the ID and Name properties of the Product business class and calculates the total amount of sold products from the Sales collection property.
IQueryable<Product> query = this.ObjectSpace.GetObjectsQuery<Product>();
var list = query.Select(p => new { p.ID, p.Name, Total = p.Sales.Sum(s => s.Count * s.Price) }).ToList();
int id = list[0].ID;
decimal total = list[0].Total;
This code generates the following SQL query to fetch the required data that does not fetch the rest properties of the Product business class.
SELECT N0."ID",
N0."Name",
(SELECT sum((Cast((N1."Count") AS MONEY) * N1."Price")) AS Res0
FROM "dbo"."Sale" N1
WHERE ((N0."ID" = N1."Product")
AND N1."GCRecord" IS NULL))
FROM "dbo"."Product" N0
Evaluate a Scalar Value
You can use LINQ’s Projection Operations to fetch a scalar value. The following example illustrates how to calculate a number of completed Tasks of a current Employee.
Employee employee = (Employee)View.CurrentObject;
IQueryable<Employee> query = ObjectSpace.GetObjectsQuery<Employee>();
int result = query.Where(e => e.ID == employee.ID).Select(e => e.Tasks.Where(t => t.Status == Status.Completed).Count()).Single();
This expression produces the following SQL query to calculate a scalar value at the database level.
SELECT top 2
(SELECT count(*) AS Res0
FROM "dbo"."Task" N1
WHERE ((N0."ID" = N1."Employee")
AND (N1."Status" = 0)))
FROM "dbo"."Employee" N0
WHERE (N0."OID" = 1)
Use ObjectSpace API
Get a Portion of Data from a Database
When you get a collection of objects using the IObjectSpace.GetObjects method, use the IObjectSpace.SetTopReturnedObjectsCount method to limit a number of objects to return.
IList objects = objectSpace.GetObjects(typeof(Product));
objectSpace.SetTopReturnedObjectsCount(objects, 500);
Get Only Certain Properties from a Database
Use XafDataView to fetch particular properties of a business class. Use the IObjectSpace.CreateDataView method to create an XafDataView instance. The XafDataView.Expressions list specifies what properties and expressions to fetch.
The following example creates an XafDataView instance to fetch the ID and Name properties of the Product business class and calculated the total amount of sold products from the Sales collection property.
XafDataView dataView = (XafDataView)objectSpace.CreateDataView(
typeof(Product), "ID;Name;Sales.Sum([Count] * Price)", null, null);
After this, access a data record by its index. An XafDataViewRecord object is returned.
To get a column value from a data record, use the XafDataViewRecord.Item property as follows:
Evaluate a Scalar Value
Use the IObjectSpace.Evaluate method to calculate an expression. The following example illustrates how to calculate a number of completed Tasks of a current Employee.