MVC and Entity Framework scaffolding is powerful, but I prefer to know what actually happens before accepting generated code. The code generated to update an entity when an edit has been done looked interesting, so I decided to have a closer look at it.

When an edit is done to an entity, the data is posted as a simple collection of form elements from the web browser. Remember that when working with MVC, we’re closer to the metal than with Web Forms and we are fully exposed to the stateless nature of the web. The gigantic hidden ViewState form field is gone, leaving us in a clean, but stateless world. When the updated data is posted to the server, the server has no more info about it than available in the request. The server doesn’t know where it came from and has to find out itself where, how and what part of it to update.

Tracking Changes

Tracking changes for objects moving across tiers, being disconnected from their original data source is always tricky. I’m still looking for the silver bullet to this problem and one step in the search is to investigate what other have done. In this post, I’ll take a closer look on how the Scaffolded code of MVC handles updates to EF Entities.

• Continue Reading →

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

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.

• Continue Reading →

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

• Continue Reading →

The normalized data model of the database is often not suitable for reading and displaying data. A separate read model used to represent all the data needed to display a page improves performance.

Defining the read model is only half the work though, to make it really usable the read model should accept queries in the same way as the write model (the DB Entities) does. In .NET/C# that means that the read model should implement IQueryable<T> to enable it to be queried with LINQ. By building the read model on top of the write model it becomes a breeze.

from c in ctx.Cars.SelectCarReadModel()
where c.Car.CarId == id
select c

A call to an extension method is all that’s needed – the query above produces a CarReadModel result, containing the additional data needed for displaying that’s not directly part of the Car entity.

• Continue Reading →

One of the greatest benefits of LINQ to SQL and LINQ to Entities is navigation properties that allows queries across several tables, without the need to use explicit joins. Unfortunately LINQ queries are often written as a direct translation of a SQL query, without taking advantage of the richer features offered by LINQ to SQL and LINQ to Entities.

It is not uncommon to see code doing a join manually.

from p in ctx.Persons
join c in ctx.Cities
on p.BornIn equals c.CityID
select new
{
    p.FirstName,
    c.Name
};

I think that using navigation properties makes the code much easier to read.

from p in ctx.Persons
select new
{
    p.FirstName,
    p.BornInCity.Name
};

• Continue Reading →