Aggregates

(See Bulk Updates)

Another convenience feature is provided with strongly-typed aggregate methods. Instead of selecting a bunch of data and performing aggregate operations, you can call one of the aggregate functions with one line of code. There are functions for Min, Max, Count, Sum, Average, and Distinct. They are all strongly-typed to the data type of the field begin queried. In other words, if you are querying the maximum cost (decimal) field, the result is returned as a decimal not an object. The where clause can be as complex as you wish based on relationships defined in the model. The ways in which you can define the where clause is not predefined. It is completely free-form based on the model.

The code below will return the maximum value of the “basename” field for all Customers with a “personid” less then 100.

string name = CustomerCollection.GetMax(x => x.BaseName, x => x.PersonId < 100);
int customerId = CustomerCollection.GetMax(x => x.CustomerId);


There is also an UpdateData aggregate method that will set exactly one field in one or more database rows that match a where condition. This is a convenience method that allows you to set many database rows without having to actually load the rows, set them and save them back to the database. You can update all matching rows in one line of code.

CustomerCollection.UpdateData(x => x.BaseName, x => x.BaseName == "Name1", "Name2");


The code above changes all Customers with a “basename” field value of “Name1” to the new value of “Name2”. This functionality is also strongly-typed. You new value cannot be an “object”, but must be a string since the field is defined as a string. This ensures reliable code.
There is also a method for deleting in bulk. Using much the same syntax as above, you can call the DeleteData method with a where expression to remove all matching records. This functionality allows you to delete in bulk without selecting data to the client.
The ability to pass in LINQ queries is actually even more useful than just implementing strongly-typed data access based on a model. It actually allows you to define complex queries and use them in multiple places. In the following example, there is a complex where clause for accessing and updating data. Instead of defining the conditional twice, I have defined it once and used it in two aggregate statements.
//Build the “where” statement for later use

Expression<Func<MessageQuery, bool>> where = x =>
	x.RecipientUserId == physician.UserId &&
	x.ViewedDate == null &&
	x.SenderUserId != null &&
	x.IsDeleted == false &&
	x.NotificationSent == null &&
	x.CreatedDate > threshold;

//Use the where statement to get the count
int count = MessageCollection.GetCount(where);
if (count > 0)
{
	//Do something...

	//Update the notification date to now with where statement
	MessageCollection.UpdateData(x => x.NotificationDate, where, DateTime.Now);
}


These are static methods on all generated entity collection types to get aggregate information. Notice this functionality allows you to get information with advanced filtering with 1-line of code.

//Get the count
int count = CustomerCollection.GetCount(x => x.City == "Berlin");

//Get the Average
decimal? average = OrderDetailCollection.GetAverage(x => x.UnitPrice, x => x.UnitPrice < 50);

//Get the Min
decimal? minimum = OrderDetailCollection.GetMin(x => x.UnitPrice, x => x.UnitPrice < 50);

//Get the Max
decimal? maximum = OrderDetailCollection.GetMax(x => x.UnitPrice, x => x.UnitPrice < 50);

//Get Global Max (whole table, no filter)
maximum = OrderDetailCollection.GetMax(x => x.UnitPrice);

	//Get Global Count (whole table, no filter)
	count = CustomerCollection.GetCount();

Last edited Sep 12, 2009 at 2:27 AM by codetools, version 3

Comments

No comments yet.