CODEWITHSUNDEEP
c#entity-frameworkormef-code-firstentity-framework-6
Alex
Alex

Reputation: 38509

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

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

Upvotes: 2

Views: 142

Answers (1)

Casey
Casey

Reputation: 3353

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.

Upvotes: 2

Related Questions