Reusing parts of LINQ queries is easily done by chaining methods returning
IQueryable. Unfortunately that doesn’t cover some senarios like reusing member initialization lists. To handle such a situation I wrote a
Merge extension method for select expressions.
For this post we’ll load the data required for a list of cars and a detail view for one car. When loading the details, more fields are required than when just loading the list. With the merging helper method we can reuse the query for the basic list and just extend it with the added properties.
The basic info for the list view is held in a
CarBasicInfo DTO class. The data for the detail view is held in a
CarExtendedInfo class which is derived from the
CarBasicInfo class. A helper method contains the
Select() call to map
Car entities to the basic DTO. Now the
Merge extension method can be used to merge an expression with the initialization of the additional fields for the extended DTO with the existing helper method for the basic fields.
private Expression<Func<Car, CarBasicInfo>> basicSelect =
c => new CarBasicInfo
CarId = c.CarId,
RegistrationNumber = c.RegistrationNumber
var car = ctx.Cars.Select(basicSelect.Merge(c => new CarExtendedInfo
Color = c.Color,
BrandName = c.Brand.Name
})).Single(c => c.RegistrationNumber == "ABC123");
RegistrationNumber field is not mentioned in the init list – it is populated by the basic select in the
SelectBasicInfo helper method. To accomplish this some (non trivial) rewriting of expression trees is in the
The Entity Framework DbContext (or LINQ-to-SQL DataContext) is a Unit Of Work implementation. That means that the same DbContext should be used for all operations (both reading and writing) within a single web or service request. That means that there are a lot of different places where the DbContext have to be accessed. To avoid having to pass the DbContext around as a parameter, I’ve created a
UnitOfWorkScope that makes the DbContext ambient and easily accessible.
A common beginners problem when working with Entity Framework or LINQ-to-SQL is to have too short life times of the DbContext. A problem that I’ve seen many questions about on Stack Overflow is when questions are encapsulated in repositories or helper methods. Inside each method a new DbContext is created for that specific read. Later, when the returned entity has been updated and is to be saved the problem occurs. The entity should be saved using the same DbContext that once read it from the database to allow change tracking to work properly. Clearly, having separate DbContexts is a problem.
The first attempt to solve it is usually to pass the DbContext around. That only solves half the problem though, that of accessing it. The other half of the problem is to decide where to call
SaveChanges to persist the changes done. Calling it from every method making changes spoils the entire unit of work concept. Trusting the creator of the context to know when any of a myriad of called functions have made changes seems risky.
I’ve been looking for a better way to handle the DbContext and have come up with an ambient DbContext, using a
UnitOfWorkScope which is similar to
The main features of the
- The first method in the call chain opening a
UnitOfWorkScope creates an ambient DbContext.
- Subsequent methods in the call chain utilizes the same DbContext.
- Changes are only saved if all participating scopes called
- Read only mode is available, where data is read using the existing DbContext, but no changes need to be saved. This is useful for GetSomeThing methods that are used both for pure reading and for reading for update.
C# LINQ code is not always executed as C# code. With LINQ-to-SQL or Entity Framework it is translated into SQL instead. This can bring unpleasant surprises when things that can’t happen do happen.
One day when you’re deep in the zone coding a marketing specialist shows up with a haunted look because the CEO wants some numbers right now. You know, the CEO wants it NOW as in IMMEDIATELY, within 60 seconds. Well, you’re already kicked out of the zone so let’s fix those numbers. The CEO wants to know the total number of seats in the company’s blue cars (I don’t understand why that’s relevant – but the CEO is the boss so let’s just do it (besides it makes a good example of what I want to show. All right?))
The explanation already took 15 seconds – so now you’re down to 45 seconds to produce the numbers for the CEO. Being a C#/LINQ hacker the fastest tool is of course LINQPad.
5 seconds later (you’re fast at the keyboard!) you’ve got the query done:
Cars.Where(c => c.Color == "Blue").Sum(c => c.Seats)
5 more seconds to compile and run and you’ve got the magic number – 14. The marketing specialist runs off, happy to make it within the required 60 seconds. You get back to coding. The moment when you’ve got into the zone the marketing specialist shows up again (interesting how they always show up exactly when the solution to that hard problem is within reach).
Now the CEO wants the numbers for Green cars as well to tomorrow’s meeting. As it looks like a minor thing, it’s best to do it right away. You switch back to LINQPad and change the color in the
Where(...) call and hits F5. This is when everything blows up in your face with an
The null value cannot be assigned to a member with type System.Int32 which is a non-nullable value type.
At least the deadline is more than 60 seconds this time. You’ve got all night to figure out what happened. To save you from a sleepless night, I’ll let you know both what happened and what to do about it.
OR-Mappers are great for making data access easier, but they are not an excuse for learning SQL. As an application developer, I’m responsible for all the SQL queries issued by the application. If I use an ORM, it’s my responsibility to check the generated SQL.
I’ve already written another post named Always Check Generated SQL back in November last year, but this is not a duplicate. Back then I examined LINQ to SQL. In this post I test the same behaviour in Entity Framework (the code won’t be exactly the same, because my EF and LINQ to SQL test databases are not identical).
The behaviour that’s up to test is how LINQ’s
group by clause is translated into SQL. The problem for the SQL generator is that LINQ is more powerful than SQL. When doing a
group by in LINQ, nested collections are produced which SQL can’t handle.
from car in Cars
group car by car.Color into colors
The query produces nested collections (graphics generated in LINQPad). When faced with that query, LINQ to SQL creates a horrible SQL translation. First the colors are fetched, then the set of cars is fetched in a separate query for each car.
Entity Framework clearly has a better query generator. It creates one single query.
This post is written by guest blogger Albin Sunnanbo. He’s a great friend and source of inspiration and also one of the best developers I know. It was Albin that first introduced me to LINQ, EF Migrations and jQuery that I now use daily.
I often find myself in situations where I need to update a collection property on some object in LINQ2SQL to match another collection property. Usually I get those collections to my data layer either from an import or from a complex update in the UI (via my business layer).
The most straight forward way to solve this is to clear all old items in the collection in the database and generate new items. Sometimes this is not possible when you have foreign key constrains and rows in other tables referring your collection items.
Many times I have written code where I try to figure out what items that are new, deleted or just updated and process them accordingly.
Today I finally wrote an extension method that encapsulates this pattern.