Click here to Skip to main content
Click here to Skip to main content
Go to top

Using LINQ to create aggregates with nHydrate

, , 26 Jan 2010
Rate this:
Please Sign up or sign in to vote.
Using LINQ with an nHydrate API for aggregation and bulk operations.

Introduction

There are numerous articles on using the nHydrate code generator for general purpose development using domain driven design (DDD). This article will delve in the specialized realm of aggregation, and by extension, other bulk operations. The tool can obviously be used to create a strongly-typed, compile-time checked data access layer (DAL); however, the DAL has some really great aggregation features as well.

The full Open Source project can be found at http://nhydrate.codeplex.com.

First, let us review. The nHydrate code generator creates collection object entities that you have defined in your model. These collections are containers of specific types of entities as defined by their name. They also have many overloaded, static methods that allow you to easily query, aggregate, update, and delete data with one line of code. For example, the code below can be used to select all Job objects in a database.

JobCollection jobCollection = JobCollection.RunSelect();

Of course, that is of limited use unless you want to select all jobs. A more real-world scenario is to define filter criteria. The code below shows how to select a group of job entities with a filter:

JobCollection jobCollection = JobCollection.RunSelect(x =>
    x.CreatedDate > DateTime.Now.Date &&
    x.Description.Contains("lollypop"));

Now, that was the refresher; however, it is not an aggregation. Following the usage above, we can use various static methods to aggregate data. The code below shows how to call various aggregations:

int count = JobCollection.GetCount(x =>
    x.CreatedDate > DateTime.Now.Date &&
    x.Description.Contains("lollypop"));

//The min value is nullable because there maybe no 
//matching records, in which case null is returned
//the return value is an INT because JobId is an INT
int? min = JobCollection.GetMin(x => x.JobId,
    x => x.CreatedDate > DateTime.Now.Date &&
    x.Description.Contains("lollypop"));

//Similar to above
int? max = JobCollection.GetMax(x => x.JobId,
    x => x.CreatedDate > DateTime.Now.Date &&
    x.Description.Contains("lollypop"));

//Get a list of all UserIDs who posted a job that meets the criteria
IEnumerable<int> idList = JobCollection.GetDistinct(x => x.OwnerId,
    x => x.CreatedDate > DateTime.Now.Date &&
    x.Description.Contains("lollypop"));

These samples show how to get aggregated data from a customer collection. This can be extended across all collections, of course. All collections are strongly-typed, and contain exactly one type of entity. This is not strictly true if you count inherited objects; however, the collection will only pull back an entity of a specified type. Notice that the aggregates take a Where clause that is LINQ syntax. This is compile-time checked against your model. This means that at runtime, there are no surprises, i.e., run-time errors. You do not and should not use magic numbers or literal strings. You also should not define relationships inside of LINQ. You have already done this in the model. The above examples were simple in that LINQ was defined for only the customer table. In the real-world, our query requirements span multiple entity types, of course.

Now, we will look at a more complex scenario of spanning tables based on dependency walking. This is more complex conceptually, though not much in code. We simply augment our LINQ syntax a bit. In the example, we have a three table database. In the database image, you can see that the database has a user table, and each user can have many jobs posted. Each job can have many job applications. Finally, each user can have many job applications. This is a real-world scenario where you have users, some are posters, like recruiters, and some are applying for jobs, like candidates.

The C# code needed to aggregate this table layout is not that difficult. The first query returns a unique list of user IDs where the user has posted at least one job and one or more of those jobs posted received an application yesterday. The second query returns just the count of users that match the criteria. Notice that these two queries are run against two different collection objects. The first could actually be run against multiple object types since the LINQ query is defining the field to be aggregated. You could have defined that field from any collection object that is related in any way to the table entity containing the field aggregated. This is possible with a strongly-typed LINQ syntax, because all relationships are defined in the model. The second query can only be run from the UserCollection object since we are counting users. Notice that we never join or define walking criteria in LINQ code. The model is king and the domain designer has already defined the entity interactions pre-generation.

DateTime startDate = DateTime.Now.Date.AddDays(-1);
DateTime endDate = startDate.AddDays(1);

//Get distinct user ID list for people who posted jobs
//and there was a job application for 1 or more of 
//these jobs yesterday. The LINQ knows that Job 
//relates to JobApplication so the syntax
//shows a JobApplication object has 
//1 related Job entity object
IEnumerable idList =
 JobApplicationCollection.GetDistinct(
 x => x.JobEntity.OwnerId,
 x => startDate <= x.CreatedDate && 
     x.CreatedDate < endDate);

//Same criteria as above but just get the number of the
//users with 1 or more jobs that had applications 
//yesterday. Notice that we are counting users so 
//we must use that collection
int count = UserAccountCollection.GetCount(x => 
    startDate <= x.JobApplicationEntity.CreatedDate && 
    x.JobApplicationEntity.CreatedDate < endDate);

Updates

Another important grouping functionality is updating in bulk. You may need to update the status of a group of users, or change the expiration date of a group of job posts. In either case, you need to specify some field to be updated, a filter to select records, and a new value for the specified field. The generated DAL API created and maintained by nHydrate allows you to do this very easily in a strongly-typed fashion. The following code snippet updates a job table's expiration date field to 30-days out if there were any job applications yesterday. Notice that the filter is complex, and spans multiple tables.

DateTime startDate = DateTime.Now.Date.AddDays(-1);
DateTime endDate = startDate.AddDays(1);

//Update the expiration date of all jobs to 30 days out
//if they had at least one application yesterday
int count = JobCollection.UpdateData(x => x.ExpirationDate,
    x => startDate <= x.JobApplicationEntity.CreatedDate &&
        x.JobApplicationEntity.CreatedDate < endDate,
    DateTime.Now.AddDays(30));

You will notice that this query joins the job and job application tables based on the defined model relationship. There is no need to specify the joining criteria in the LINQ filter. Also notice that the value specified field and the new value are the same type. Since the expiration date is a date, the new value can only be of this type too. The new data is not passed in as an object. The code will not compile if the data type does not match. This is even true for nullable objects. If the expiration date can accept nulls in the database, then the data type in code is a nullable date. The number of rows affected is returned from the UpdateData method.

Deletes

The final bulk operation is that of delete. There are times when we need to remove a group of records based on a complex filter. It may not be reasonable, and certainly not efficient, to load all of this data from the database just to mark it for deletion. Again, a collection's static method gives us great power and efficiency. The following code will remove all job application records where the related JobID is less than 100 in value, and the user that applied lives in Springfield (in any state), and the posted job is in the state "IL".

//Remove all job applications if the related JobID is less then 100
//and the user that applied lives in Springfield
//and the posted job is in the state IL
int count = JobApplicationCollection.DeleteData(x =>
    x.JobId < 100 &&
    x.UserAccountEntity.City == "Springfield" &&
    x.JobEntity.State == "IL");

This is a complex filter that spans three different tables. The generated SQL is complex, no doubt, but your C# code is elegant and simple. Even a non-developer could read this almost in natural language.

Summary

As you can see, there is great power in the advanced aggregate functionality provided with nHydrate. The greatest thing about it is that this is out of the box functionality. There is no special setup to get it. When you define your model, all of this functionality is derived from the entities and relationships you define. I cannot iterate too many times the superior aspect that compile-time checked syntax gives you. You can view this as an extension of the compiler. Once you define an nHydrate model, it is validated against various rules. When you generate your code, it is checked by the compiler. When you write your custom code with all the complexity and user-defined fields and values, it too is checked by the compiler in that all interaction with the database is done through LINQ syntax and the generated Select command. There is no free-form SQL or lazy typing. This gives your application a greatly reduced chance of having runtime errors.

License

This article, along with any associated source code and files, is licensed under The Microsoft Public License (Ms-PL)

Share

About the Authors

Christopher R Davis
Software Developer (Senior) Hewlett Packard
United States United States
I currently work in security at HP and create developer tools. I have been working with technology since the VIC 20. I am currently co-authoring the nHydrate code generator (and other projects) with Michael Knight in Atlanta, GA. We have built a faceted navigation engine that allows you to list any set of objects (cars, jobs, apartments, etc) on a website that gives users the click-through experience. My goal is no more advanced search pages.
 
I use nHydrate at my day job every day and use it manage a a complex system for HP.
 
Check out the current nHydrate site on Codeplex. http://nhydrate.codeplex.com
 


My LinkedIn Profile

LinkedIn nHydrate Group
Follow on   Twitter

Michael S Knight
Architect
United States United States
No Biography provided
Follow on   Twitter

Comments and Discussions

 
GeneralMy vote of 4 PinmvpKanasz Robert5-Nov-12 2:48 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 26 Jan 2010
Article Copyright 2010 by Christopher R Davis, Michael S Knight
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid