Entity Framework Code First Succinctly^®
by Ricardo Peres

CHAPTER 4

Getting Data from the Database

Overview

As you might expect, Entity Framework offers a number of APIs to get data from the database into objects. These are designed to cover different scenarios; we will see them one by one.

By Id

All data access layers really must support loading by primary key, and certainly EFCF does so. For that, we have the Find method.

//retrieving a record by a single primary key consisting of an integer

var project = ctx.Projects.Find(1);

This method can take any number of parameters; it supports entities having composite primary keys. The following example shows just that.

//retrieving a record by a composite primary key consisting of two integers

var project = ctx.SomeEntities.Find(1, 2);

Find will return null if no matching record is found; no exception will be thrown since this is a perfectly valid result.

Tip: The order and type of the parameters should be the same as the keys defined in the mapping for this entity.

LINQ

Since its introduction with .NET 3.5, Language Integrated Querying (LINQ) has become the de facto standard for querying data of any kind, so it’s no surprise that EF has LINQ support. It will probably be your API of choice for most scenarios. It is strongly typed, meaning you can tell at compile time that some things are not right, refactor friendly, and its syntax is easy to understand. Let’s see some examples.

A LINQ query is built from the entity collection properties of DbSet<T> type exposed by the context and it consists of an IQueryable<T> implementation.

Tip: Don’t forget that LINQ is about querying. You won’t find any way to change data here.

If you don’t include a terminal operator, such as ToList, ToArray, ToDictionary, Any, Count, LongCount, Single, SingleOrDefault, First, FirstOrDefault, Last, LastOrDefault, a query is not actually executed. You can pick up this query and start adding restrictions such as paging or sorting.

//create a base query

var projectsQuery = from p in ctx.Projects select p;

//add sorting

var projectsSortedByDateQuery = projectsQuery.OrderBy(x => x.Start);

//execute and get the sorted results

var projectsSortedByDateResults = projectsSortedByDateQuery.ToList();

//add paging and ordering (required for paging)

var projectsWithPagingQuery = projectsQuery.OrderBy(x => x.Start).Take(5).Skip(0);

//execute and get the first 5 results

var projectsWithPagingResults = projectsWithPagingQuery.ToList();

//add a restriction

var projectsStartingAWeekAgoQuery = projectsQuery.Where(x => x.Start >= EntityFunctions.AddDays(DateTime.Today, -7));

//execute and get the projects that started a week ago

var projectsStartingAWeekAgoResults = projectsStartingAWeekAgoQuery.ToList();

You will get at most a single result.

//retrieving at most a single record with a simple filter var project = ctx.Projects.Where(x => x.ProjectId == 1).SingleOrDefault();

Restricting by several properties is just as easy.

//retrieving multiple record with two filters var projects = ctx.Projects.Where(x => x.Name.Contains("Something") && x.Start >= DateTime.Today.AddDays(-7)).ToList();

Or having one of two conditions matched.

//or

var resourcesKnowingVBOrCS = ctx.Technologies.Where(t => t.Name == "VB.NET"

|| t.Name == "C#").SelectMany(x => x.Resources).Select(x => x.Name).ToList();

Count results.

//count

var numberOfClosedProjects = ctx.Projects

.Where(x => x.End != null && x.End < DateTime.Now).Count();

Check record existence.

//check existence

var existsProjectBySomeCustomer = ctx.Projects

.Any(x => x.Customer.Name == "Some Customer");

Perform a projection, that is, only get some parts of an entity.

//get only the name of the resource and the name of the associated project

var resourcesXprojects = ctx.Projects.SelectMany(x => x.ProjectResources)

.Select(x => new { Resource = x.Resource.Name, Project = x.Project.Name }).ToList();

Do aggregations.

//average project duration

var averageProjectDuration = ctx.Projects.Where(x => x.End != null)

.Average(x => EntityFunctions.DiffDays(x.Start, x.End));

//sum of project durations by customer

var sumProjectDurationsByCustomer = ctx.Projects.Where(x => x.End != null)

.Select(x => new { Customer = x.Customer.Name, Days = EntityFunctions

.DiffDays(x.Start, x.End) }).GroupBy(x => x.Customer)

.Select(x => new { Customer = x.Key, Sum = x.Sum(y => y.Days) }).ToList();

Get distinct values.

//distinct roles performed by a resource

var roles = ctx.Resources.SelectMany(x => x.ProjectResources)

.Where(x => x.Resource.Name == "Ricardo Peres").Select(x => x.Role).Distinct()

.ToList();

You can also group on a property.

//grouping and projecting

var resourcesGroupedByProjectRole = ctx.Projects.SelectMany(x => x.ProjectResources)

.Select(x => new { Role = x.Role, Resource = x.Resource.Name }).GroupBy(x => x.Role)

.Select(x => new { Role = x.Key, Resources = x }).ToList();

//grouping and counting

var projectsByCustomer = ctx.Projects.GroupBy(x => x.Customer)

.Select(x => new { Customer = x.Key.Name, Count = x.Count() }).ToList();

//top 10 customers having more projects in descending order

var top10CustomersWithMoreProjects = ctx.Projects.GroupBy(x => x.Customer.Name)

.Select(x => new { x.Key, Count = x.Count() }).OrderByDescending(x => x.Count)

.Take(10).ToList();

Or use the results of a subquery.

//subquery

var usersKnowingATechnology = (from r in ctx.Resources where r.Technologies.Any(x => (from t in ctx.Technologies where t.Name == "ASP.NET" select t).Contains(x)) select r).ToList();

Finally, check for one of a set of values.

//contains

var customersToFind = new String[] { "Some Customer", "Another Customer" };

var projectsOfCustomers = ctx.Projects

.Where(x => customersToFind.Contains(x.Customer.Name)).ToList();

Note: In case you are wondering, all literals present in LINQ queries—strings, numbers, dates, etc.—will be turned into parameters for proper execution plan reusing.

You might have noticed that whenever we had date operations, we made use of some EntityFunctions methods such as EntityFunctions.DiffDays. This class has some extension methods for operations that LINQ does not offer. These can be grouped as:

Date and time: add time intervals, truncate a date or time, calculate the time interval between two dates.
String: get the left or right part of a string, reverse it, convert it to ASCII or UNICODE.
Statistics: variance and variance based on the whole population.

There’s also a similar class, SqlFunctions, that offers extension methods for invoking SQL Server specific functions, namely:

Mathematical: trigonometric, logarithmic, power, conversion to and from radians, random numbers.
String: get ASCII and UNICODE code from a string, calculate the difference between two strings, the position of a character in a string, inserts a string into another, gets the SOUNDEX value for a string.
Checksums: calculate checksums for columns.
Date and time: calculate time intervals between dates, add time interval.
System: get the database date and time, the current user, host name.
Data types: checks if a column can be converted to another data type, does conversions.

As flexible as LINQ is, there are some limitations. It’s not easy to build queries dynamically, for example, when the filters or desired sorting property is not known at compile time. Let’s look at the other APIs to see how we can deal with these situations.

Tip: Do not query over calculated columns, only mapped ones, because otherwise you will get an error, since Entity Framework knows nothing about these.

Entity SQL

Entity SQL (or ESQL) is Entity Framework’s query language. It is very similar to SQL, but it has some great advantages.

It is very similar to SQL, so we can reuse some of our knowledge of it.
It is database-independent, which means it works the same regardless of the actual database we are targeting.
It is object, not set, oriented, so in some ways it is similar to what we do in code.
It knows about entity relations, so we don’t need to specify them.
Unlike LINQ, queries can be created dynamically, at runtime.

Tip: Entity SQL only supports querying, not updating.

Entity SQL commands are strings, which means we can do the usual string manipulation operations, and will only know if the syntax is correct when we actually execute it.

While Entity Framework Code First can certainly use Entity SQL, it can’t do it directly: neither DbContext or any of the other Code First classes expose methods for working with it. We first need to get its underlying ObjectContext.

//get the ObjectContext from the DbContext

ObjectContext octx = (ctx as IObjectContextAdapter).ObjectContext;

And the actual action occurs on the CreateQuery method.

//all values from the Projects collection

var allProjects = octx.CreateQuery<Resource>("SELECT VALUE p FROM Projects AS p")

.ToList();

Parameters are explicitly named with the @ prefix before the name and must be given a value.

//simple filter

var usersInProject = octx.CreateQuery<Resource>(

"SELECT VALUE pr.Resource FROM ProjectResources AS pr WHERE pr.Project.Name = @name", new ObjectParameter("name", "Big Project")).ToList();

Using a subquery for the following.

//contains

var usersKnowingATechnology = octx.CreateQuery<Resource>(

"SELECT VALUE r FROM Resources AS r WHERE EXISTS (SELECT VALUE t FROM Technologies AS t WHERE t.Name = @name AND r IN t.Resources)",

new ObjectParameter("name", "ASP.NET")).ToList();

Use the following for paging.

//paging

var pagedResources = octx.CreateQuery<Resource>(

"SELECT VALUE r FROM Resources AS r ORDER BY r.Name SKIP 5 LIMIT(5)").ToList();

//paging with parameters

var pagedResourcesWithParameters = octx.CreateQuery<Resource>(

"SELECT VALUE r FROM Resources AS r ORDER BY r.Name SKIP @skip LIMIT(@limit)",

new ObjectParameter("skip", 5), new ObjectParameter("limit", 5)).ToList();

//single first record ordered descending

var lastProject = octx.CreateQuery<Project>(

"SELECT VALUE TOP(1) p FROM Projects AS p ORDER BY p.Start DESC").SingleOrDefault();

Use the following for ranges.

//between with parameters

var projectsStartingInADateInterval = octx.CreateQuery<Project>(

"SELECT VALUE p FROM Projects AS P WHERE p.Start BETWEEN @start AND @end",

new ObjectParameter("start", DateTime.Today.AddDays(14)),

new ObjectParameter("end", DateTime.Today.AddDays(-7))).ToList();

//in with inline values

var projectsStartingInSetOfDates = octx.CreateQuery<Project>(

"SELECT VALUE p FROM Projects AS P WHERE p.Start IN MULTISET(DATETIME

'2013-12-25 0:0:0', DATETIME '2013-12-31 0:0:0')").ToList();

Use the following for count records.

//count records

var numberOfClosedProjects = octx.CreateQuery<Int32>(

"SELECT VALUE COUNT(p.ProjectId) FROM Projects AS p WHERE p.[End] IS NOT NULL AND p.[End] < @now", new ObjectParameter("now", DateTime.Now)).Single();

Use the following for projections.

//projection with date difference

var projectNameAndDuration = octx.CreateQuery<Object>(

"SELECT p.Name AS Name, DIFFDAYS(p.Start, p.[End]) FROM Projects AS p WHERE p.[End]

IS NOT NULL").ToList();

//projection with count

var customersAndProjectCount = octx.CreateQuery<Object>(

"SELECT p.Customer.Name, COUNT(p.Name) FROM Projects AS p GROUP BY p.Customer")

.ToList();

//projection with case

var customersAndProjectCountIndicator = octx.CreateQuery<Object>(

"SELECT p.Customer.Name, CASE WHEN COUNT(p.Name) > 10 THEN 'Lots' ELSE 'Few' END AS Amount FROM Projects AS p GROUP BY p.Customer").ToList();

Tip: Property names with the same name as reserved keywords must be escaped inside [ ].

Tip: When performing projections with more than one property, the template argument type of CreateQuery must be Object.

When projecting, we lose strong typing, so we must directly access the returned IDataRecord instances.

if (customersAndProjectCountIndicator.Any() == true)

{

var r = customersAndProjectCountIndicator.OfType<IDataRecord>().First();

var nameIndex = r.GetOrdinal("Name");

var nameValue = r.GetString(nameIndex);

}

As for some built-in functions.

//max number of days

var maxDurationDays = octx.CreateQuery<Int32?>(

"SELECT VALUE MAX(DIFFDAYS(p.Start, p.[End])) FROM Projects AS p WHERE p.[End] IS NOT NULL").SingleOrDefault();

//string matching (LIKE)

var technologiesContainingDotNet = octx.CreateQuery<String>(

"SELECT VALUE t.Name FROM Technologies AS T WHERE CONTAINS(t.Name, '.NET')")

.ToList();

SQL

Try as we might, the truth is, it’s impossible to escape from using SQL. This may be because performance is typically better or because some query is difficult or even impossible to express using any of the other APIs, but that’s just the way it is. Entity Framework Code First has full support for SQL, including:

Getting entities and values.
Executing INSERTs, UPDATEs and DELETEs.
Calling functions and stored procedures.

It does have all the disadvantages you might expect:

Not strongly typed.
No compile-time checking.
If you use database-specific functions and you target a new database, you have to rewrite your SQL.
You have to know the right syntax for even simple things, such as paging or limiting the number of records to return.

The first case I’m demonstrating is how to execute a SELECT and convert the result into an entity. For that we shall use the SqlQuery method of the DbSet<T>.

//simple select

var projectFromSQL = ctx.Projects.SqlQuery(

"SELECT * FROM Project WHERE Name = @p0", "Big Project").SingleOrDefault();

Tip: Notice how we pass parameters directly after the SQL; each must be named @p0, @p1, and so on.

If we wanted to retrieve an entity from a table-valued function, we would use.

//table-valued function

var projectFromFunction = ctx.Projects.SqlQuery(

"SELECT * FROM dbo.GetProjectById @p0", 1).SingleOrDefault();

Where the GetProjectById function might be.

CREATE FUNCTION dbo.GetProjectById

(

@ProjectID INT

)

RETURNS TABLE

RETURN

(

SELECT *

FROM Project

WHERE ProjectId = @ProjectID

)

Tip: Methods that return entities from SQL cannot return complex types, only scalar and enumerated types. This is a known problem with Entity Framework and there is an open issue for it at http://entityframework.codeplex.com/workitem/118.

Tip: Don’t forget that if you want to return entities, your SQL must return columns that match the properties of these entities, as specified in its mapping.

Although the SqlQuery was primarily design to work with entities, we can also use it to retrieve scalars.

//current date and time

var now = ctx.Database.SqlQuery<DateTime>("SELECT GETDATE()").Single();

If we want to execute arbitrary SQL commands (UPDATE, DELETE, INSERT), we will need the ExecuteSqlCommand like in the following example.

//update records

var updateCount = ctx.Database.ExecuteSqlCommand(

"UPDATE ProjectDetail SET Budget = Budget * 1.1 WHERE ProjectId = {0}", 1);

Finally, for really special situations, we can always resort to the underlying DbConnection.

//create the connection in a using block so that it is disposed at the end

using (var cmd = ctx.Database.Connection.CreateCommand())

{

if (ctx.Database.Connection.State == ConnectionState.Closed)

{

ctx.Database.Connection.Open();

}

cmd.CommandText = "Some weird SQL command";

//the number of affected records, if the query returns it

var result = cmd.ExecuteNonQuery();

//or a single scalar value

//var result = cmd.ExecuteScalar();

//or even a data reader

var result = cmd.ExecuteReader();

ctx.Database.Connection.Close();

}

Lazy, Explicit, and Eager Loading

Lazy Loading

By default, all references (one-to-one, many-to-one) and collections (one-to-many, many-to-many) are lazy loaded, which means that Entity Framework won’t actually try to load its values until someone tries to access them. For instance, consider this query where we load a Project by its id.

//load a project by id

var p = ctx.Projects.Find(1);

This query will produce this SQL.

SELECT TOP (2)

[Extent1].[ProjectId] AS [ProjectId],

[Extent1].[Description_Description] AS [Description_Description],

[Extent1].[Name] AS [Name],

[Extent1].[Start] AS [Start],

[Extent1].[End] AS [End],

[Extent1].[Customer_CustomerId] AS [Customer_CustomerId]

FROM [dbo].[Project] AS [Extent1]

WHERE [Extent1].[ProjectId] = @p0

-- p0 (dbtype=Int32, size=0, direction=Input) = 1

Note: You might have noticed the TOP(2) clause: this is merely for assuring that a single record is selected, as the Find method expects; if that is not the case, an exception will be thrown.

As you can see, the only table that is touched is the Project one. However, when we access the Customer property.

//access the customer

var c = p.Customer;

The Customer reference property will then be loaded, For that, EF will issue another query.

SELECT

[Extent2].[CustomerId] AS [CustomerId],

[Extent2].[Contact_Email] AS [Contact_Email],

[Extent2].[Contact_Phone] AS [Contact_Phone],

[Extent2].[Name] AS [Name]

FROM [dbo].[Project] AS [Extent1]

INNER JOIN [dbo].[Customer] AS [Extent2] ON [Extent1].[Customer_CustomerId] = [E

xtent2].[CustomerId]

WHERE [Extent1].[ProjectId] = @EntityKeyValue1

-- EntityKeyValue1 (dbtype=Int32, size=0, direction=Input) = 1

Or if we go the other way and first load a customer.

//load a customer by id

var customer = ctx.Customers.Find(1);

The following SQL is executed.

SELECT TOP (2)

[Extent1].[CustomerId] AS [CustomerId],

[Extent1].[Contact_Email] AS [Contact_Email],

[Extent1].[Contact_Phone] AS [Contact_Phone],

[Extent1].[Name] AS [Name]

FROM [dbo].[Customer] AS [Extent1]

WHERE [Extent1].[CustomerId] = @p0

-- p0 (dbtype=Int32, size=0, direction=Input) = 1

Do note that only the Customer data is retrieved, and this is consistent with the previous query. Now let’s access the Projects collection.

//load all projects

var projects = customer.Projects;

This SQL is sent to the server.

SELECT

[Extent1].[ProjectId] AS [ProjectId],

[Extent1].[Description_Description] AS [Description_Description],

[Extent1].[Name] AS [Name],

[Extent1].[Start] AS [Start],

[Extent1].[End] AS [End],

[Extent1].[Customer_CustomerId] AS [Customer_CustomerId]

FROM [dbo].[Project] AS [Extent1]

WHERE [Extent1].[Customer_CustomerId] = @EntityKeyValue1

-- EntityKeyValue1 (dbtype=Int32, size=0, direction=Input) = 1

The Projects of this Customer are all loaded into memory. Entity Framework takes care of generating the appropriate SQL for us, opening and closing connections behind our back and instancing the entities from the returned resultsets. Keep in mind that navigation properties are only loaded from the database the first time they are accessed, so it is likely that you will notice a delay on the first access, but after that they are always returned from memory.

Note: Never mind the actual SQL details; they are generated from a generic algorithm and even if they are not exactly what you’d expect. They will work!

This is possible because when EF returns entities from a query, it doesn’t return instances of the exact declared class but from a derived, special class. This class, called a proxy class, is generated automatically by EF.

A proxy to an entity

Figure 39: A proxy to an entity

See the funny class name starting with System.Data.Entity.DynamicProxies.Project_ in the inspector? That is the class that is generated automatically. This class has a reference to the context from which it came from, and has overrides for all navigation properties so that when we try to access them by code, it will only then load the associated entities. For this to happen, you must simultaneously assure that:

The class is not sealed.
The class is not private or internal.
All navigation properties (references and collections) meant to be lazy loaded are virtual.
All navigation properties are implemented as auto properties, with no backing field and no custom logic.

Seems easy, don’t you think? If any of this conditions is not verified, then lazy loading cannot be used. As an example, if we would pick the Project entity and change its Customer property so that it is not virtual, then loading a Project would always have the consequence of having a null value for its Customer property.

Tip: Navigation properties that haven’t been explicitly loaded always have value null when lazy loading is disabled.

There are properties in the DbContext that allow configuring lazy loading: Database.Configuration.LazyLoadingEnabled and ProxyCreationEnabled. Please note that even though there are two properties, both must be simultaneously true to have lazy loading working, which they are by default.

To see if a reference property has been loaded, we use code such as the following.

//load a project

var project = ctx.Projects.Find(1);

//see if the Customer property is loaded

var customerLoaded = ctx.Entry(project).Reference(x => x.Customer).IsLoaded;

If you try to load a lazy navigation property after you have disposed of its originating context, you will get an ObjectDisposedException, because the entity relies on an active context for getting data from the database.

Explicit Loading

Say you disable lazy loading.

//disable lazy loading

ctx.Configuration.LazyLoadingEnabled = false;

If lazy loading is turned off, either globally or for a certain property, we can still force a navigation property to load explicitly.

//explicitly load the Customer property

ctx.Entry(project).Reference(x => x.Customer).Load();

Don’t forget that this is only necessary if lazy loading is disabled. The same also applies to collections.

//see if the ProjectResources collection is loaded

var resourcesLoaded = ctx.Entry(project).Collection(x => x.ProjectResources)

.IsLoaded;

if (resourcesLoaded == false)

{

//explicitly load the ProjectResources collection

ctx.Entry(project).Collection(x => x.ProjectResources).Load();

}

Another interesting case is where you want to load just a part of some collection by filtering out the entities that do not match a given condition, or even count its members without actually loading them. It is possible to do it with EF, and for that you would issue queries like this.

//count an entity's collection entities without loading them

var countDevelopersInProject = ctx.Entry(project).Collection(x => x.ProjectResources).Query().Where(x => x.Role == Role.Developer).Count();

//filter an entity’s collection without loading it

var developersInProject = ctx.Entry(project).Collection(x => x.ProjectResources)

.Query().Where(x => x.Role == Role.Developer).ToList();

The difference between lazy and eager loading is that with lazy loading, you don’t need to do anything explicit, you just access the navigation properties without even thinking about it; whereas with explicit loading you have to perform some action.

Eager Loading

Even if lazy and explicit loading is good for most occasions, you only load the data you need, when you need it, there may be cases where you want all data from the main entity as well as associated entities to be loaded at the same time. This will most likely be because of one of two reasons:

You are certain that you are going to have to access some of the navigation properties, and for performance reasons, you load them beforehand (for example, you need to go through all order’s details).
The entity’s (and its associated entities’) lifecycle will probably outlive the context from which it was obtained from (for example, you are going to store the entity in some cache), so it won’t have access to it and thus lazy loading will not be possible.

Enter eager loading. What eager loading means is, when issuing a query, you explicit declare the expansion paths that Entity Framework will bring along with the root entities. EF will then generate a different SQL expression than it would normally would with JOINs for all the required associations.

For example, the following query brings along a Customer and all of its Projects and it introduces the Include method.

//explicitly eager load the Customer for each project

var projectsAndTheirCustomers = ctx.Projects.Include(x => x.Customer).ToList();

For the record, this will produce the following SQL statement.

SELECT

[Extent1].[ProjectId] AS [ProjectId],

[Extent1].[Name] AS [Name],

[Extent1].[Start] AS [Start],

[Extent1].[End] AS [End],

[Extent2].[CustomerId] AS [CustomerId],

[Extent2].[Contact_Email] AS [Contact_Email],

[Extent2].[Contact_Phone] AS [Contact_Phone],

[Extent2].[Name] AS [Name1]

FROM [dbo].[Project] AS [Extent1]

INNER JOIN [dbo].[Customer] AS [Extent2] ON

[Extent1].[Customer_CustomerId] = [Extent2].[CustomerId]

The Include method can also take a String as its parameter, which must be the name of a navigation property (a reference or a collection).

//explicitly eager load the Customer for each project

var projectsAndTheirCustomers = ctx.Projects.Include("Customer").ToList();

Multiple paths can be specified.

//two independent include paths

var resourcesProjectResourcesAndTechnologies = ctx.Resources

.Include(x => x.ProjectResources).Include(x => x.Technologies).ToList();

In this case, the SQL will look like.

SELECT

[UnionAll1].[ResourceId] AS [C1],

[UnionAll1].[ResourceId1] AS [C2],

[UnionAll1].[ResourceId2] AS [C3],

[UnionAll1].[Contact_Email] AS [C4],

[UnionAll1].[Contact_Phone] AS [C5],

[UnionAll1].[Name] AS [C6],

[UnionAll1].[C1] AS [C7],

[UnionAll1].[ProjectResourceId] AS [C8],

[UnionAll1].[ProjectResourceId1] AS [C9],

[UnionAll1].[Role] AS [C10],

[UnionAll1].[Project_ProjectId] AS [C11],

[UnionAll1].[Resource_ResourceId] AS [C12],

[UnionAll1].[C2] AS [C13],

[UnionAll1].[C3] AS [C14]

FROM (SELECT

CASE WHEN ([Extent2].[ProjectResourceId] IS NULL) THEN CAST(NULL AS int)

ELSE 1 END AS [C1],

[Extent1].[ResourceId] AS [ResourceId],

[Extent1].[ResourceId] AS [ResourceId1],

[Extent1].[ResourceId] AS [ResourceId2],

[Extent1].[Contact_Email] AS [Contact_Email],

[Extent1].[Contact_Phone] AS [Contact_Phone],

[Extent1].[Name] AS [Name],

[Extent2].[ProjectResourceId] AS [ProjectResourceId],

[Extent2].[ProjectResourceId] AS [ProjectResourceId1],

[Extent2].[Role] AS [Role],

[Extent2].[Project_ProjectId] AS [Project_ProjectId],

[Extent2].[Resource_ResourceId] AS [Resource_ResourceId],

CAST(NULL AS int) AS [C2],

CAST(NULL AS varchar(1)) AS [C3]

FROM [dbo].[Resource] AS [Extent1]

LEFT OUTER JOIN [dbo].[ProjectResource] AS [Extent2] ON [Extent1].[ResourceId] = [Extent2].[Resource_ResourceId]

UNION ALL

SELECT

2 AS [C1],

[Extent3].[ResourceId] AS [ResourceId],

[Extent3].[ResourceId] AS [ResourceId1],

[Extent3].[ResourceId] AS [ResourceId2],

[Extent3].[Contact_Email] AS [Contact_Email],

[Extent3].[Contact_Phone] AS [Contact_Phone],

[Extent3].[Name] AS [Name],

CAST(NULL AS int) AS [C2],

CAST(NULL AS int) AS [C3],

CAST(NULL AS int) AS [C4],

CAST(NULL AS int) AS [C5],

CAST(NULL AS int) AS [C6],

[Join2].[TechnologyId] AS [TechnologyId],

[Join2].[Name] AS [Name1]

FROM [dbo].[Resource] AS [Extent3]

INNER JOIN (SELECT [Extent4].[Resource_ResourceId] AS [Resource_ResourceId], [Extent5].[TechnologyId] AS [TechnologyId], [Extent5].[Name] AS [Name]

FROM [dbo].[TechnologyResource] AS [Extent4]

INNER JOIN [dbo].[Technology] AS [Extent5] ON [Extent5].[TechnologyId] = [Extent4].[Technology_TechnologyId] ) AS [Join2] ON [Extent3].[ResourceId] = [Join2].[Resource_ResourceId]) AS [UnionAll1]

ORDER BY [UnionAll1].[ResourceId1] ASC, [UnionAll1].[C1] ASC

A final example with multilevel inclusion:

//multilevel include paths

var resourcesProjectResourcesCustomers = ctx.Resources.Include(x => x.ProjectResources.Select(y => y.Project.Customer)).ToList();

And the generated SQL will look like this.

SELECT

[Project1].[ResourceId] AS [ResourceId],

[Project1].[Contact_Email] AS [Contact_Email],

[Project1].[Contact_Phone] AS [Contact_Phone],

[Project1].[Name] AS [Name],

[Project1].[C1] AS [C1],

[Project1].[ProjectResourceId] AS [ProjectResourceId],

[Project1].[Role] AS [Role],

[Project1].[ProjectId] AS [ProjectId],

[Project1].[Name1] AS [Name1],

[Project1].[Start] AS [Start],

[Project1].[End] AS [End],

[Project1].[CustomerId] AS [CustomerId],

[Project1].[Contact_Email1] AS [Contact_Email1],

[Project1].[Contact_Phone1] AS [Contact_Phone1],

[Project1].[Name2] AS [Name2],

[Project1].[Resource_ResourceId] AS [Resource_ResourceId]

FROM ( SELECT

[Extent1].[ResourceId] AS [ResourceId],

[Extent1].[Contact_Email] AS [Contact_Email],

[Extent1].[Contact_Phone] AS [Contact_Phone],

[Extent1].[Name] AS [Name],

[Join2].[ProjectResourceId] AS [ProjectResourceId],

[Join2].[Role] AS [Role],

[Join2].[Resource_ResourceId] AS [Resource_ResourceId],

[Join2].[ProjectId] AS [ProjectId],

[Join2].[Name1] AS [Name1],

[Join2].[Start] AS [Start],

[Join2].[End] AS [End],

[Join2].[CustomerId] AS [CustomerId],

[Join2].[Contact_Email] AS [Contact_Email1],

[Join2].[Contact_Phone] AS [Contact_Phone1],

[Join2].[Name2] AS [Name2],

CASE WHEN ([Join2].[ProjectResourceId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]

FROM [dbo].[Resource] AS [Extent1]

LEFT OUTER JOIN (SELECT [Extent2].[ProjectResourceId] AS [ProjectResourceId], [Extent2].[Role] AS [Role], [Extent2].[Resource_ResourceId] AS [Resource_ResourceId], [Extent3].[ProjectId] AS [ProjectId], [Extent3].[Name] AS [Name1], [Extent3].[Start] AS [Start], [Extent3].[End] AS [End], [Extent4].[CustomerId] AS [CustomerId], [Extent4].[Contact_Email] AS [Contact_Email], [Extent4].[Contact_Phone] AS [Contact_Phone], [Extent4].[Name] AS [Name2]

FROM [dbo].[ProjectResource] AS [Extent2]

INNER JOIN [dbo].[Project] AS [Extent3] ON [Extent2].[Project_ProjectId] = [Extent3].[ProjectId]

INNER JOIN [dbo].[Customer] AS [Extent4] ON [Extent3].[Customer_CustomerId] = [Extent4].[CustomerId] ) AS [Join2] ON [Extent1].[ResourceId] = [Join2].[Resource_ResourceId]

) AS [Project1]

ORDER BY [Project1].[ResourceId] ASC, [Project1].[C1] ASC

As you can imagine, EF goes into a lot of work to JOIN all data that it needs to fetch at the same time, hence the quite complicated SQL.

Local Data

Entities known by an Entity Framework context—either loaded from it or marked for deletion or for insertion—are stored in what is called local or first level cache. Martin Fowler calls it Identity Map, and you can read more about the concept at http://martinfowler.com/eaaCatalog/identityMap.html. Basically, the context keeps track of all these entities, so that it doesn’t need to materialize them whenever a query that returns their associated records is executed. It is possible to access this cache by means of the Local property of the DbSet<T>:

//all local Projects

var projectsAlreadyLoaded = ctx.Projects.Local;

//filtered local Projects

var projectsAlreadyLoadedBelongingToACustomer = ctx.Projects.Local.Where(x => x.Customer.Name == "Some Customer");

Neither of these queries goes to the database, they are all executed in memory. The Local property is actually an instance of ObservableCollection<T>, which means we have an event that notifies us whenever new items are added or removed from the local cache, CollectionChanged.

ctx.Projects.Local.CollectionChanged += (sender, e) =>

{

if (e.Action == NotifyCollectionChangedAction.Add)

{

//an entity was added to the local cache

}

else if (e.Action == NotifyCollectionChangedAction.Remove)

{

//an entity was removed from the local cache

}

};

//discard all known projects (stop tracking their changes)

ctx.Projects.Local.Clear();

It is possible to know all entities that are present in the local cache, and to see their state, as seen by the context. It’s the ChangeTracker’s responsibility to keep track of all these entities.

//get the projects in local cache that have been modified

var modifiedProjects = ctx.ChangeTracker.Entries<Project>()

.Where(x => x.State == EntityState.Modified).Select(x => x.Entity);

As you can guess, it’s considerably faster to get an entity from the local cache than it is to load it from the database. With that thought on our mind, we can write a method such as the next one, for transparently returning a local entity or fetching it with SQL.

//retrieve from cache or the database

public static IQueryable<T> LocalOrDatabase<T>(this DbContext context,

Expression<Func<T, Boolean>> expression) where T : class

{

var localResults = context.Set<T>().Local.Where(expression.Compile());

if (localResults.Any() == true)

{

return (localResults.AsQueryable());

}

return (context.Set<T>().Where(expression));

}

Build apps 2X faster

using Syncfusion Essential Studio^® suite

1800+ high-performance UI components.
Includes popular controls such as Grid, Chart, Scheduler, and more.
24x5 unlimited support by developers.

Get Your Free Trial Now

Getting Data from the Database

Overview

By Id

LINQ

Entity SQL

SQL

Lazy, Explicit, and Eager Loading

Lazy Loading

Explicit Loading

Eager Loading

Local Data

DISCLAIMER: Web reader is currently in beta. Please report any issues through our support system. PDF and Kindle format files are also available for download.