Thu 22 March 2018
With Entity Data Model, we built the connection between conceptual models and database schema. The next question would be how we’re going to code with the EDM objects.
Today we’ll have a quick view to LINQ to Entities, which is the main way developing with conceptual models, as well as Entity SQL —a storage independent query language. Either way the queries would eventually be transformed to corresponding sql query to database.
LINQ to Entities
LINQ is an acronym stands for Language-INtegrated Query. It is a set of query technologies that allow you to create an unified query against different data sources. So on language level, it represents a bunch of methods to operate object sets.
When LINQ applied with Entity Framework, we got three different technologies: LINQ to DateSet, LINQ to SQL and LINQ to Entities. This time we'll talk about LINQ to Entities only.
LINQ to Entities supports both query syntax and method syntax of LINQ. Query syntax is similar to SQL query, writing with c#, you can compose you LINQ inline by using from/where/select keywords:
var customers = from c in DbContext.Set<Customers>() where c.Deleted = false select c.Id
The reason why LINQ query starts with
select, it must be for enabling IntelliSense while editing. It's happening all the time in Sql Server Management Tool that column names only get populated after you typed table name -- which always be the second line of your script.
WIth method syntax, on the other hand, the code can be more straight forward:
var customers = DbContext.Set<Customers>() .Where(c => c.Deleted == false) .Select(c => c.Id);
Both methods result to a same data set in
customers variable and both the types of the variables would be
IQueryable. Everyone new to LINQ to Entities must be careful with the differences between
IQueryable contains metadata about the query, such as the query expression and the provider being used, and it only has result returned within the object after the query inside is executed. The easiest way to execute the query and convert a
IQueryable to a
IEnumerable is call
Under the cover,
IQueryableis building an expression tree when you fill your chaining methods. The execution of the
IQueryablevaries based on different data provider. It converts the expression tree into format the provider recongnized and send it to the corresponding server. Potentially it could support all data sources, as long as there is an implementation of
IQueryablefrom those providers.
The expression above would be executed on our DB server and return the result into a
List<T> object. This query can be easily monitored with SQL Server Profiler if you're using MS SQL Server. It would look like:
SELECT [Extent1].[Id] AS [Id] FROM [dbo].[Customers] AS [Extent1] WHERE [Extent1].[Deleted] = 0
It's fairly a simple SQL query that generated from our code but sometimes the queries may look more complex. We might come back and talk to its impact on performance later.
What is Entity SQL?
Well, Entity SQL is another query language Microsoft invented .... to consume your enthusiasms on SQL-like languages.
Here's the way using Entity SQL to implement our query to get customers id:
EntityCommand cmd = conn.CreateCommand(); cmd.CommandText = @"SELECT VALUE c.Id FROM Customers AS c WHERE c.Deleted = 0" EntityDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess) // Extract data from reader
In this post we talked about approaches to query database with Entity Framework. There is no doubt that my favorite one is to use LINQ chaining methods. It helps you validate in compile time so you can always focus on the business logics.