Entity Framework comes with a LINQ provider, allowing you to build and run database queries in C# (or another .NET language) whilst maintaining database-independence and abstracting many of the complexities of object-relational mapping. This makes for a very powerful and expressive tool for building business logic/data access logic.
If used correctly, EF ‘read’ queries specifically can be very efficient: only pulling down the fields you need, for instance.
In many cases in an app, you don’t need to pull down the entire object (equivalent to a SELECT * in SQL) – to populate a product dropdown, you might only need the ID and Name columns. You can achieve this with a projection, which comes in the form of a Select() call:
[code lang=”C#”]Context.Products.OrderBy(p => p.Priority).Select(p => new { p.ID, p.Name });[/code]
You can also use libraries such as AutoMapper to integrate with EF if you choose to define these mappings separately (entity class → view on that data), using the QueryableExtensions it implements:
[code lang=”C#”]Context.Products.OrderBy(p => p.Priority).Project().To<ProductDropdownModel>();[/code]
Both of these will generate an efficient SELECT with just the required columns.
However, problems arise when you try to build more complex expressions or logic into these projections or maps. Say you wanted to use string.Format() to build a full name in your resulting object:
[code lang=”C#”]var allNames = Context.People.Select(p => string.Format(“{0}, {1}”, p.Lastname, p.Firstname));[/code]
This fails with a familiar exception: “LINQ to Entities does not recognise the method ‘System.String Format(…)’”. This is because there is no SQL equivalent for the call to string.Format. One workaround for this is to perform your database-to-final-model mapping in two stages: first to retrieve the fields you need, then to build the final type:
[code lang=”C#”]var nameData = Context.People.Select(p => new { p.Firstname, p.Lastname }).ToArray();
var allNames = nameData.Select(d => string.Format(“{0}, {1}”, d.Lastname, d.Firstname));
[/code]
Note the call to ToArray(), which forces execution of the query at that point – otherwise the projection would still be chained as a part of the database query. The above works, but isn’t as readable, and depends on the developer listing out each required field twice – once to extract, once to use. This can be a maintenance headache too.
To counter this, we put together a little helper library called LinqDefer. The code above becomes simply:
[code lang=”C#”]var allNames = Context.People.Select(p => string.Format(“{0}, {1}”, p.Lastname, p.Firstname)).ThenDoDeferred();[/code]
Behind the scenes, LinqDefer analyses the projection from the Select() call, and looks for which parts of the object are required. It effectively breaks the LINQ query into two, as we did manually in the previous example. It will retrieve an ephemeral object with the required fields, then run any method calls or other expressions it deems non-“databaseable” afterwards on the client.
It works with Entity Framework, but isn’t tied to it. There are some AutoMapper tests there too, so it’s possible to define nice clean maps, and plug them into your repository without worrying about “databaseability”. On that point, the analyser which determines which expressions should be passed to the database can be customised or replaced easily.
The project is open source, MIT-licensed, and available here on GitHub. It can also be added to projects easily using NuGet:
[code]Install-Package LinqDefer[/code]
