Before I began working on a recent .Net Windows application, I was checking out some of the new data access methods in .Net Framework 4.0 and came across LINQ to Entities, which was available in .Net Framework 3.x, but was vastly improved in Framework 4.0. Today, I’m going to spend a few moments talking about the LINQ language and, more specifically, LINQ to Entities and what it means for object-oriented C# programmers.
Basic LINQ Querying
LINQ (Language Integrated Query) is a formal programming model from Microsoft that brings querying capabilities to most collections and arrays in the .NET Framework. Using LINQ, you’re able to treat any collection as a set of data, querying them using a specialized syntax. For instance, given an array of integers, you could use LINQ to filter that array and only return integers greater than five:
1: int[] intNumbers = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
2:
3: IEnumerable<int> qryNumbers = from number in intNumbers
4: where number > 5
5: select number;
6:
7: foreach (int intNumber in qryNumbers) {
8:
9: Console.WriteLine(intNumber);
10:
11: }
The resulting output of our for each() loop would be: 6, 7, 8, 9, 10.
The syntax beginning with the from keyword kind of looks like a conglomerate of C# and SQL. Even though the query syntax looks kind of like SQL, it’s important to remember that it is not SQL. If you allow yourself to think in terms of SQL, you will limit your ability to understand more advanced LINQ usage. When working with LINQ, it’s important to think through your queries through the object-oriented prism and not with a “how do you do this in SQL” mindset.
Basic Entity Querying Using LINQ
Now, that was a very simple example. But, what if you could apply these concepts to a database? What if, without writing SQL code, you could query a collection of business entities from the database to return only what you’re looking for? This is where LINQ to Entities comes in to play. With LINQ to Entities, we’re taking the same concepts we used when querying a collection of integers and applying them to our database entities. Check this out:
1: IEnumerable<person> qryPeeps = from peep in objDataContext.people
2: where peep.lastName.StartsWith("B")
3: select peep;
4:
5: foreach (person objPeep in qryPeeps) {
6:
7: Console.WriteLine(string.Format("{0} - {1}", objPeep.firstName, objPeep.lastName));
8:
9: }
What we’re saying here is, give me any person from our database whose name begins with the uppercase letter “B”. Note that when using LINQ—unlike in SQL—I can use the method String.StartsWith() in my predicate. One of the things I like most about LINQ is that no matter what you’re querying, you can make use of any object methods to help you filter or shape your results.
Setting up the data context
You’re probably wondering what objDataContext is. Before you can begin querying your database objects, you have to tell .Net about your database and, more importantly, what is inside of it. To do this, you’ll add a new data source and choose “Entity Data Model” as the type. Then, you can select your tables, stored procedures, etc and Visual Studio will create the data model and generate all your business classes automatically. For instance, the Entity Framework took my people table and automatically created singular business classes. In my last code snippet, that’s where the person type and the lastName property came from. Those are strongly typed classes that are generated for you by the Entity Framework. Pretty neat, huh?
Shaping and Projection
With the last code snippet, we returned every property of the person entity along with our results—even though we only used the firstName and lastName properties. Pragmatically, you’ll probably be dealing with large data sets and will want to limit the data that is returned from your queries. This is especially true when you’re connecting to a remote data store and efficiency is key. Fortunately, LINQ to Entities makes this very easy to do with the use of shaping and projection.
Shaping Results
To illustrate shaping your results, check out the following bit of code:
1: IEnumerable<person> qryPeeps = from peep in objDataContext.people
2: where peep.lastName.StartsWith("B")
3: select new person {
4: firstName = peep.firstName,
5: lastName = peep.lastName
6: };
7:
8: foreach (person objPeep in qryPeeps) {
9:
10: Console.WriteLine(string.Format("{0} - {1}", objPeep.firstName, objPeep.lastName));
11:
12: }
This code is exactly the same as the last bit of code except we added a new bit to the end of the select keyword. This portion of the code tells the framework to select a new person entity and only fill the firstName and lastName properties. Now, when your query is executed, the SQL that is generated for you and sent to the database will only select those two properties.
Computed Properties Using Projection
Earlier, I pointed out that the Entity Framework automatically generates your business classes for you when configuring the data model in Visual Studio. This is great and all, but what if you wanted to change things up a bit? With Entity Framework, you’re not constrained to only using the business classes generated for you by the framework. Let’s say you wanted to add a property called fullName that concatenated the firstName and lastName properties so that you didn’t have to do it all the time in your code. With projection, Entity Framework allows you to define plain ‘ole classes (termed POCO classes), complete with custom properties, that you can use in your LINQ code:
1: public class DisplayPerson {
2:
3: public string firstName { get; set; }
4: public string lastName { get; set; }
5: public string fullName { get; set; }
6:
7: public DisplayPerson() { }
8:
9: }
Now, to illustrate projection, let’s look at the following LINQ query:
1: IEnumerable<DisplayPerson> qryPeeps = from peep in objDataContext.people
2: where peep.lastName.StartsWith("B")
3: select new DisplayPerson {
4: firstName = peep.firstName,
5: lastName = peep.lastName,
6: fullName = string.Format("{0} {1}", peep.firstName, peep.lastName)
7: };
8:
9: foreach (DisplayPerson objPeep in qryPeeps) {
10:
11: Console.WriteLine(objPeep.fullName);
12:
13: }
Now, this code is exactly like the previous code, except instead of selecting a new person object, we’re using the class we just created to select a new DisplayPerson object. We’re actually projecting the person object’s properties onto the DisplayPerson object, hence the name projection. Now when we access objPeep.fullName, we’ll get something like “Jesse Bunch”. It’s important to note that when you project onto another object, Entity Framework will only load the properties you specified. Therefore, if your DisplayPerson class had a hairColor property and you didn’t map it in your query, accessing it later would return null.
Anonymous Types
One other thing to note about projection: you don’t necessarily have to define the POCO class before you write your query. Instead, you can make use of anonymous types in .Net to create your classes on-the-fly. This method and it’s caveats are beyond the scope of this article, but information about them can be found by visiting the links at the end of this tutorial.
Wrapping Up
LINQ to Entities is so much more than what was described here. As this was only intended to be a quick, “get your feet wet” primer to the Entity Framework, I’m sure you’re left with a million what ifs. To aid you on your quest to Entity Framework mastery, I’ve included a few links below that I found were very useful in my own understanding of LINQ. I hope that this has opened your eyes as to the power of LINQ and hopefully will make your life easier. As always, feel free to tell me what you think by leaving a comment below.
1 comment