CODEWITHSUNDEEP
c#sql-serverentity-framework
McShep
McShep

Reputation: 491

Entity Framework group by and actual SQL not efficiently written

I have written the following EF query which extends from the Person class where person contact numbers is a collection:

var person = await _tmsContext.Person.SingleOrDefaultAsync(p => p.UserID == userId);

person.PersonContactNumbers
          .Where(x => clientNumberTypes.Contains(x.ContactNumberTypeId)
                      && x.ValidFromDate < DateTime.Now
                      && (!x.ValidToDate.HasValue || x.ValidToDate > DateTime.Now))
          .GroupBy(x => x.ContactNumberTypeId, (key, group) => 
               group.OrderBy(cnt => cnt.DateCreated)
          .Select(cnt => new { cnt.ContactNumber, cnt.ContactNumberTypeId })
          .FirstOrDefault())
          .ToList();

The generated SQL looks like this:

exec sp_executesql N'SELECT 
    [Extent1].[PersonContactNumberID] AS [PersonContactNumberID],   
    [Extent1].[ContactNumberTypeID] AS [ContactNumberTypeID], 
    [Extent1].[ContactNumber] AS [ContactNumber], 
    [Extent1].[ValidFromDate] AS [ValidFromDate], 
    [Extent1].[ValidToDate] AS [ValidToDate], 
    [Extent1].[xxxxxxx] AS [xxxxxxx], 
    [Extent1].[xxxxxxx] AS [xxxxxxx], 
    [Extent1].[xxxxxxx] AS [xxxxxxx], 
    [Extent1].[xxxxxxx] AS [xxxxxxx], 
    [Extent1].[xxxxxxx] AS [xxxxxxx], 
    [Extent1].[xxxxxxx] AS [xxxxxxx]
    FROM [dbo].[tjPersonContactNumber] AS [Extent1]
    WHERE [Extent1].[PersonID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=XXXXXXX

This would clearly indicate to me that EF is requesting all contact numbers for the specified person, and then in memory filters the data out which is not required and then groups by and takes the first or default.

On small amounts of data this is alright, however, in some instances where there is large amounts of data (person contact is just an example) not only is it returning columns which are never used (xxxxxx) but it will take a considerable amount of time.

Does anybody know why EF is creating such a simple query and then in memory doing the rest, and does anybody know how to force EF to do the group by and filters in the query?

Upvotes: 2

Views: 95

Answers (1)

cpoDesign
cpoDesign

Reputation: 9153

The issue you are experiencing looks like you have eager loaded child object.

Can you access you context directly and make the call to the child object?

something like

dbContext.PersonContactNumbers.Where(x=>x.PersonId == yourId)

This should produce faster query

If not ensure you have eager loading turned off.

Upvotes: 2

Related Questions