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
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).
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.
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.
