Entity Framework 4 – LockTar’s Blog

Use .Where().Select().Single() rather than .Single()

09/11/201114/05/2013 Ralph Jansen Leave a comment

When you use the .Single() lambda statement, you will only get one result. When you only use 1 property of the whole object it is totally waste of the performance. Only get want you want. So you could better use the .Select() statement to get only the property you want.

See the difference below where we have an example for getting the profile picture of an unique user.

.Where().Select().Single()

byte[] imageData = Context.Profiles.Where(p => p.Id == profileId).Select(p => p.Picture).Single();

SELECT
[Limit1].[Picture] AS [Picture]
FROM ( SELECT TOP (2)
[Extent1].[Picture] AS [Picture]
FROM [dbo].[Profiles] AS [Extent1]
WHERE [Extent1].[Id] = @p__linq__0
) AS [Limit1]

.Single()

byte[] imageData = Context.Profiles.Single(p => p.Id == profileId).Picture;

SELECT TOP (2)
[Extent1].[Id] AS [Id],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent1].[Birthday] AS [Birthday],
[Extent1].[HideBirthdayYear] AS [HideBirthdayYear],
[Extent1].[Picture] AS [Picture],
[Extent1].[ModifiedOn] AS [ModifiedOn],
[Extent1].[UserId] AS [UserId]
FROM [dbo].[Profiles] AS [Extent1]
WHERE [Extent1].[Id] = @p__linq__0

Imagine what the difference would be with nested queries, joins, or even bigger objects (tables) than this example.

Default cascading delete in EF Code First

12/05/201112/05/2011 Ralph Jansen 4 Comments

In the new Entity Framework 4.1 Code First you can easily create a database on your POCO objects. But the down side (sometimes) is that cascading delete is turned on by default.

Say you have two objects called Department and Manager.

A manager needs a department and a department can have multiple managers. This is done because we want to have a one-many relationship in this demo. To do this we can make navigation properties. A navigation property is created by the virtual keyword. If you want to create a many relationship, you should use the ICollection<> type.

If you generate the database now, the tables are generated and a foreign key relationship is made. To do this, EF has generated another column in your Manager table. The column name should be Department_Id. If you open the foreign key relationship you see that the cascading delete relationship is disabled.

But our POCO objects are not really complete. Because how would we know what the key of the Deparment is when I only want to query the Manager object? You can’t because you need the other object. To solve this, you can add a column that is similar as your generated Foreign key column. So, we change our object to this code:

If you generate your database again, you see that some things are changed. The automatically generated column Department_Id is no longer there and the DepartmentId column is now used for your foreign key relationship. Only, this is not the only difference in your project. Because if you look at your foreign key relationship, you see that the cascading delete is now enabled.

But why do we want a cascading delete in this example? Because say in example, that if a department is merged with another department and the department record would be deleted from the database, your managers would be deleted as well. Why will they be deleted? Are the fired?!?!?! Can’t they go to another department?

Luckily you can change this default behavior. You can do this with the next line of code in your OnModelCreating override:

If you generate your database again with the latest classes (class with DepartmentId) your Foreign Key relationship would not have a cascading delete enabled.

Entity Framework: Different loading capabilities

06/05/201106/05/2011 Ralph Jansen Leave a comment

The Entity Framework is in the new stage of evolving. We are now in stage 4.1 where the Code First functionality is released. Entity Framework enables just like LINQ to SQL different kind of loading types. But what are the loading types and what are the different capabilities? In this blog I will explain in a simple way what they are and what they can do.

There are 3 different kind of loading types for related data called lazy, Eager and explicit loading. In the next example you see two entities which are related.

Department can have many Courses and a Course can only have one Department.

Lazy Loading:

Eager Loading:

Explicit Loading:

Because they don’t immediately retrieve the property values, lazy loading and explicit loading are also both known as deferred loading.

In general, if you know you need related data for every entity retrieved, eager loading offers the best performance, because a single query sent to the database is typically more efficient than separate queries for each entity retrieved. For example, in the above examples, suppose that each department has ten related courses. The eager loading example would result in just a single (join) query. The lazy loading and explicit loading examples would both result in eleven queries.

On the other hand, if you need to access an entity’s navigation properties only infrequently or only for a small portion of a set of entities you’re processing, lazy loading may be more efficient, because eager loading would retrieve more data than you need. Typically you’d use explicit loading only when you’ve turned lazy loading off. One scenario when you might turn lazy loading off is during serialization, when you know you don’t need all navigation properties loaded. If lazy loading were on, all navigation properties would all be loaded automatically, because serialization accesses all properties.