How to extract select statement details to different method still maintain eager loading?

Question

In entityframework I can explicitly load properties when adding them to the select method, like this:

var workhours = dbContext.WorkHours
    .Select(w => new WorkHourLookupDto()
        {
            Id = w.Id,
            StartTime = w.StartTime,
            EndTime = w.EndTime,
            RecreationInMinutes = w.RecreationInMinutes,
            TotalWorkHoursInMinutes = w.TotalWorkHoursInMinutes,
            Customer = new CustomerMiniDto
            {
                Id = w.Customer.Id,
                Name = w.Customer.Name
            },
            Description = w.Description
        })
    .ToList();

var workhours2 = dbContext.WorkHours
    .Select(w => _factory.CreateLookUpDto(w))
    .ToList();

Now workhours creates the following select query:

exec sp_executesql N'SELECT [w].[Id], [w].[StartTime], [w].[EndTime], [w].[RecreationInMinutes], [w].[CompanyId], [w].[Created], [w].[CreatedBy], [w].[CustomerId], [w].[Description], [w].[LastModified], [w].[LastModifiedBy], [w].[UserId], [t].[Id], [t].[Name]
FROM [WorkHours] AS [w]
INNER JOIN (
    SELECT [c].[Id], [c].[Name]
    FROM [Customers] AS [c]
    WHERE [c].[CompanyId] = @__ef_filter__CompanyId_2
) AS [t] ON [w].[CustomerId] = [t].[Id]
WHERE ([w].[CompanyId] = @__ef_filter__CompanyId_0) AND ([w].[UserId] = @__ef_filter__p_1)',N'@__ef_filter__CompanyId_2 nvarchar(4000),@__ef_filter__CompanyId_0 nvarchar(4000),@__ef_filter__p_1 uniqueidentifier',@__ef_filter__CompanyId_2=N'3AD04E77-9654-4BBB-A8E8-4DE1335A7516',@__ef_filter__CompanyId_0=N'3AD04E77-9654-4BBB-A8E8-4DE1335A7516',@__ef_filter__p_1='8220F8B8-720D-42E8-9357-2256470EA206'

And workhours2 creates the query:

exec sp_executesql N'SELECT [w].[Id], [w].[CompanyId], [w].[Created], [w].[CreatedBy], [w].[CustomerId], [w].[Description], [w].[EndTime], [w].[LastModified], [w].[LastModifiedBy], [w].[RecreationInMinutes], [w].[StartTime], [w].[UserId]
FROM [WorkHours] AS [w]
WHERE ([w].[CompanyId] = @__ef_filter__CompanyId_0) AND ([w].[UserId] = @__ef_filter__p_1)',N'@__ef_filter__CompanyId_0 nvarchar(4000),@__ef_filter__p_1 uniqueidentifier',@__ef_filter__CompanyId_0=N'3AD04E77-9654-4BBB-A8E8-4DE1335A7516',@__ef_filter__p_1='8220F8B8-720D-42E8-9357-2256470EA206'

The factory is like this:

public WorkHourLookupDto CreateLookUpDto(WorkHour workHour)
{
    return new()
    {
        Id = workHour.Id,
        StartTime = workHour.StartTime,
        EndTime = workHour.EndTime,
        RecreationInMinutes = workHour.RecreationInMinutes,
        TotalWorkHoursInMinutes = workHour.TotalWorkHoursInMinutes,
        Customer = _customerFactory.CreateMiniDto(workHour.Customer),
        Description = workHour.Description
    };
}

public CustomerMiniDto CreateMiniDto(Customer customer)
{
    if(customer != null)
        return new()
        {
            Id = customer.Id,
            Name = customer.Name
        };

    return new();
}

Why does the query differ when extracting it to a different class, thus method?

Because EF cannot decompile body `CreateLookUpDto` and understand which fields are needed. It see that required whole entity for function. There are several ways how to avoid this, for example this [my answer AsDto](https://stackoverflow.com/questions/66378438/can-i-reuse-code-for-selecting-a-custom-dto-object-for-a-child-property-with-ef/66386142#66386142) — Svyatoslav Danyliv, Aug 17 '23 at 10:09
Have you read the documentation [EF Core : Eager Loading of Related Data](https://learn.microsoft.com/en-us/ef/core/querying/related-data/eager)? It's helpful. — vernou, Aug 17 '23 at 13:14

score 2 · Accepted Answer · answered Aug 17 '23 at 11:47

2

You need to return an Expression of type Func<WorkHour, WorkHourLookupDto> ie it takes a WorkHour and returns a WorkHourLookupDto.

You don't need a function for this, you can put it into a static field or property.

private static Expression<Func<WorkHour, WorkHourLookupDto>> _createLookUpDto =
    workHour => new()
    {
        Id = workHour.Id,
        StartTime = workHour.StartTime,
        EndTime = workHour.EndTime,
        RecreationInMinutes = workHour.RecreationInMinutes,
        TotalWorkHoursInMinutes = workHour.TotalWorkHoursInMinutes,
        Customer = _customerFactory.CreateMiniDto(workHour.Customer),
        Description = workHour.Description
    };

private static Expression<Func<Customer, CustomerMiniDto>> _createMiniDto = 
    customer =>
    {
        if(customer != null)
            return new()
            {
                Id = customer.Id,
                Name = customer.Name
            };

        return new();
    }
}

Now you simply pass in the expression directly.

var workhours2 = dbContext.WorkHours
    .Select(_createLookUpDto)
    .ToList();

answered Aug 17 '23 at 11:47

Charlieface

52,284
6
19
43

But adding this to my interface: `public Expression> CreateLookUpDtoExp;` Gives the following compile error: Interfaces can not contain instance fields. And it has to be the interface, since I'm calling all methods from the interface. – Salomé Aug 17 '23 at 13:06
1

Make it `static` and maybe just put it into a `static` helper class. Why do you need an interface, there is nothing this expression is doing that needs access to any instance fields anyway. – Charlieface Aug 17 '23 at 13:11
The application is talking to interfaces rather than concretes – Salomé Aug 17 '23 at 13:14
1

So just make an interface function which returns this expression (or picks it up from the helper class). `public Expression> CreateLookUpDtoExp() => helper.CreateLookUpDtoExp;` or something like that. – Charlieface Aug 17 '23 at 13:16

How to extract select statement details to different method still maintain eager loading?

1 Answers1