Entity Framework - incorrectly doing 2 select statements instead of a join

Question

I have a fairly simple (code first) model:

Employee

[Table("vEmployee")] //note v - it's a view
public class Employee
{
    [Key]
    public int EmployeeNumber { get; set; }

    public string FirstName { get; set; }
    public string LastName { get; set; }
}

EmployeeHolidayEntitlement

[Table("tblEmployeeHolidayEntitlement")]
public class EmployeeHolidayEntitlement
{
    [Key]
    public int EmployeeNumber { get; set; }

    public virtual Employee Employee { get; set; }

    public decimal StandardEntitlement { get; set; }

    //.....omitted for brevity
}

Note that EmployeeHolidayEntitlement is mapped to a table, and Employee is mapped to a view

When building my context, I do:
(not sure if this is correct!)

modelBuilder.Entity<Employee>()
    .HasOptional(x => x.HolidayEntitlement)
    .WithRequired(x => x.Employee);

Now, when I query, like this:

var db = new ApiContext();
var result = db.Employees.ToList();

It's very slow.

If I look in SQL profiler, I can see that instead of one statement (joining vEmployee and tblEmployeeHolidayEntitlement) I get many statements executed (one per Employee record) - for example:

First, it selects from vEmployee

SELECT 
    [Extent1].[id] AS [EmployeeNumber], 
    [Extent1].[FirstName] AS [FirstName], 
    [Extent1].[LastName] AS [LastName], 
    FROM [dbo].[vEmployee] AS [Extent1]

then one of these for each record returned

exec sp_executesql N'SELECT 
    [Extent1].[EmployeeNumber] AS [EmployeeNumber], 
    [Extent1].[StandardEntitlement] AS [StandardEntitlement]
    FROM [dbo].[tblEmployeeHolidayEntitlement] AS [Extent1]
    WHERE [Extent1].[EmployeeNumber] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=175219

This doesn't seem right to me -

I would of thought it should be doing something more along the lines of a LEFT JOIN like

SELECT *
  FROM [dbo].[vEmployee] employee
  LEFT JOIN
  [dbo].[tblEmployeeHolidayEntitlement employeeEntitlement
  ON
  employee.id = employeeEntitlement.employeenumber

score 2 · Accepted Answer · answered Oct 16 '14 at 19:22

2

You have to use the Include method, like db.Employees.Include(e => e.HolidayEntitlement).ToList(). If you don't and you access the property you'll trigger lazy loading. That's what's happening to you.

For more information check the documentation on loading. The short of it is that if it always joined your entire object graph it'd be unacceptably slow.

answered Oct 16 '14 at 19:22

Casey

3,307
1
26
41

On the .Include, i can't specify a lambda, it wants a string? – Alex Oct 16 '14 at 19:24
2

@Alex You need need a `using` statement to access the lambda overload of `.Include`, see: http://stackoverflow.com/a/10079815/84206 – AaronLS Oct 16 '14 at 19:25
1

Are you using EF6? The lambda overload is newer, but the string overload does the same thing... see here: http://msdn.microsoft.com/en-us/library/system.data.entity.dbextensions.include(v=vs.103).aspx – Casey Oct 16 '14 at 19:25
Got some reading to do on loading, but this has solved it, thank you :) – Alex Oct 16 '14 at 19:28

Entity Framework - incorrectly doing 2 select statements instead of a join

1 Answers1