Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

LINQ to Entities Projection

, 13 Jul 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
A few common SQL query patterns, and their Linq to Entities (EF) equivalents

For anyone coming from a background using T-SQL as their primary query language, Linq to Entities can be quite daunting. I, for one, am a huge fan of the Entity Framework. I consider it another step towards keeping data driven application developers inside the .NET Framework. And LINQ is a perfect way for programmers to think about data queries. SQL is another language for most developers to learn, and each database has a slightly different syntax for operations. LINQ and Entity Framework make it much easier for programmers to think in their native programming language while writing queries.

In this post, I am going to be showcasing a few common SQL query patterns, and explaining their Linq to Entities (EF) equivalents. This will hopefully show some of the power of LINQ to developers who have yet to start learning the LINQ syntax.

Single Column T-SQL Query

First, we are going to take a look at a simple T-SQL query that selects one column from a table named Employees. This query is used in a DataAdapter to fill a DataTable then bound to a WinForms GridView.

select Age from employees

The column “Age” is of type Int, so the query will return a list of all Ages(ints) from the employees table. This simple query can be edited in several ways to return different results to the naming of the Age context.

select AGE from employees
select employees.Age as NewAge from employees

2010-07-07_1414-NewAge

T-SQL of course is for the most part case insensitive, but when it comes to LINQ column projection, case is taken into consideration. The first query (above) will return a list of Ints in a column named “AGE”, the second will return a column named “NewAge” that contains the same result.

This process is called projection. All of the queries return the same results, but the projected schema naming has been altered dynamically.

Linq to Entities Projection

Projection in Linq to Entities (Linq with Entity Framework models) is very similar to the way it is done in T-SQL, a column or columns are queried and set to return naming different than the original column name. The great thing about Linq to Entities is that the query can be debugged to see what exactly is being returned. We will start off again with a simple single column query.

var query = from emps in context.Employees select emps.Age;

By examining the query above, you would think for sure it's the same as our first T-SQL query. The Linq query does return the same results, a list of Ints, but the result set does not contain any contextual information. The query above will not bind directly to a GridView because it is only a list of Ints, not a list of type “Age” (Ints).

2010-07-07_1446-LinqAge

The image to the right is what the result view looks like in Visual Studio of the executed query. The result is of type System.Data.Objects.ObjectQuery<int?> which you can see has no reference to the Age column at all. There is a different approach needed to project columns into a new value.

Anonymous Type Projection

var query = from emps in context.Employees select new { emps.Age };
var query = from emps in context.Employees select new { NewAge = emps.Age };

Both queries above will perform the proper projection, similar to the T-SQL queries. The first will return a list of Ints under the column “Age” and the second “NewAge”. The Select new syntax is how Linq to Entities handles projecting data into an anonymous type.

2010-07-07_1551-LinqPro

The image to the right is what the result view looks like for the new projected query. The results are returned not only as Ints, but still contain their contextual information which will allow them to be bound directly to a GridView. The returned type has also changed to System.Data.Objects.ObjectQuery<<>f__AnonymousType0<int?>>. The anonymous type would be “Age” which is of type int?.

Summary

To developers that are new to LINQ and the Entity Framework, functional differences like these can be confusing. With enough research and testing, almost any T-SQL query can be converted to Linq to Entities and it's often very rewarding.

Entity Framework is here to stay and it is definitely something you will want to become well versed in. Microsoft is continuing to pour huge amounts of resources into Entity Framework for the future. Take the time to become versed in the basic concepts and terms will serve you well for the future.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

JasonShort
Software Developer (Senior)
United States United States
I hold a PhD in computer science, and have been a practicing developer since the early 90's.
 
I used to be the owner for VistaDB, but sold the product to another company in August 2010.
 
I have recently moved to Redmond and now work for Microsoft. Any posts or articles are purely my own opinions, and not the opinions of my employer.
Follow on   Twitter

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141220.1 | Last Updated 13 Jul 2010
Article Copyright 2010 by JasonShort
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid