EF Core 2.1 Making multiple DB calls

.net-core c# entity-framework entity-framework-core

Question

Is there a way to prevent EF Core from doing multiple DB round trips on single enumeration function call?

Take into consideration this relatively simple LINQ expression:

var query2 = context.CheckinTablets.Select(ct => new
            {
                Id = ct.Id,
                DeviceName = ct.Name,
                Status = ct.CheckinTabletStatuses
                    .OrderByDescending(cts => cts.TimestampUtc).FirstOrDefault()
            }).ToList();

In the past expactation was that "One enumeration call translates to one DB call" (if you disable lazy loading). In EF Core this is no longer the case!

In EF 6.2.0 this LINQ is translated to

SELECT [Extent1].[CheckinTabletID] AS [CheckinTabletID],
   [Limit1].[TimestampUtc] AS [TimestampUtc]
  --...
FROM [dbo].[CheckinTablet] AS [Extent1] OUTER APPLY (
SELECT TOP (1) [Project1].[CheckinTabletStatusID] AS [CheckinTabletStatusID],
               [Project1].[CheckinTabletID] AS [CheckinTabletID],
               [Project1].[TimestampUtc] AS [TimestampUtc]
FROM (
SELECT [Extent2].[CheckinTabletStatusID] AS [CheckinTabletStatusID],
       [Extent2].[CheckinTabletID] AS [CheckinTabletID],
       [Extent2].[TimestampUtc] AS [TimestampUtc]
     --...
FROM [dbo].[CheckinTabletStatus] AS [Extent2]
WHERE [Extent1].[CheckinTabletID] = [Extent2].[CheckinTabletID]
) AS [Project1] ORDER BY [Project1].[TimestampUtc] DESC
) AS [Limit1];

While quite ugly, it was something that followed POLA quite nicely. Even more it was something we could work with to optimize DB side (indexes).

With EF Core 2.1.0 we get something like this:

SELECT [ct].[CheckinTabletID] AS [Id], [ct].[strName] AS [DeviceName] FROM [CheckinTablet] AS [ct]

exec sp_executesql N'SELECT TOP(1) [cts].[CheckinTabletStatusID], [cts].[CheckinTabletID], [cts].[TimestampUtc] FROM [CheckinTabletStatus] AS [cts] WHERE @_outer_Id = [cts].[CheckinTabletID] ORDER BY [cts].[TimestampUtc] DESC',N'@_outer_Id int',@_outer_Id=1

exec sp_executesql N'SELECT TOP(1) [cts].[CheckinTabletStatusID], [cts].[CheckinTabletID], [cts].[TimestampUtc] FROM [CheckinTabletStatus] AS [cts] WHERE @_outer_Id = [cts].[CheckinTabletID] ORDER BY [cts].[TimestampUtc] DESC',N'@_outer_Id int',@_outer_Id=2

exec sp_executesql N'SELECT TOP(1) [cts].[CheckinTabletStatusID], [cts].[CheckinTabletID], [cts].[TimestampUtc] FROM [CheckinTabletStatus] AS [cts] WHERE @_outer_Id = [cts].[CheckinTabletID] ORDER BY [cts].[TimestampUtc] DESC',N'@_outer_Id int',@_outer_Id=3

exec sp_executesql N'SELECT TOP(1) [cts].[CheckinTabletStatusID], [cts].[CheckinTabletID], [cts].[TimestampUtc] FROM [CheckinTabletStatus] AS [cts] WHERE @_outer_Id = [cts].[CheckinTabletID] ORDER BY [cts].[TimestampUtc] DESC',N'@_outer_Id int',@_outer_Id=4

exec sp_executesql N'SELECT TOP(1) [cts].[CheckinTabletStatusID], [cts].[CheckinTabletID], [cts].[TimestampUtc] FROM [CheckinTabletStatus] AS [cts] WHERE @_outer_Id = [cts].[CheckinTabletID] ORDER BY [cts].[TimestampUtc] DESC',N'@_outer_Id int',@_outer_Id=5

Yes, that is one call to first get all entities (CheckinTablets) and then call per row to get status for each entity...

So in one call ToList() Entity Framework is making n+1 calls to database. This is extremely undesirable, is there a way to disable this behaviour or workaround?

Edit 1:

.Include() is not helping the issue... It still makes n+1 DB requests.

Edit 2 (credit @jmdon):

Not returning object but simple value make only one call! Of course this doesn’t really help if you don't want to flatten your entity, or if you want multiple values from second table. Never the less good to know!

var query2 = _context.CheckinTablets.Select(ct => new
{
    Id = ct.Id,
    DeviceName = ct.Name,
    Status = new CheckinTabletStatus
    {
        Id = ct.CheckinTabletStatuses.OrderByDescending(cts => cts.TimestampUtc).FirstOrDefault().Id,
        CheckinTabletId = ct.CheckinTabletStatuses.OrderByDescending(cts => cts.TimestampUtc).FirstOrDefault().CheckinTabletId,
    }
}).ToList();

Produces one call to DB:

SELECT [ct].[intCheckinTabletID] AS [Id0],
   [ct].[strName] AS [DeviceName],
(
    SELECT TOP (1) [cts].[intCheckinTabletStatusID]
    FROM [tCheckinTabletStatus] AS [cts]
    WHERE [ct].[intCheckinTabletID] = [cts].[intCheckinTabletID]
    ORDER BY [cts].[dtmTimestampUtc] DESC
) AS [Id],
(
    SELECT TOP (1) [cts0].[intCheckinTabletID]
    FROM [tCheckinTabletStatus] AS [cts0]
    WHERE [ct].[intCheckinTabletID] = [cts0].[intCheckinTabletID]
    ORDER BY [cts0].[dtmTimestampUtc] DESC
) AS [CheckinTabletId]
FROM [tCheckinTablet] AS [ct];
1
4
6/8/2018 9:45:55 AM

Accepted Answer

I asked this questions during .Net Conf 2018 to Diego Vega and Smit Patel... This was their answer (paraphrased).

EF Core is not only for relational DB... Customers did not want to see Exception if something cannot be translated to SQL... "If it needs more then one query, that is fine"... By default multiple queries per enumeration are enabled. There is a warning system that will output a warning if this happens. They are thinking about adding a method that will upgrade warning to exception if multiple round-trips are executed. They are working on optimizing (n+1) queries to a few (fixed size) queries based on data structure.

It is possible to force EF Core to throw exception when it evaluates part of the query client side by adding this to OnConfiguring method.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EFQuerying;Trusted_Connection=True;")
        .ConfigureWarnings(warnings => warnings.Throw(RelationalEventId.QueryClientEvaluationWarning));
}

More info: https://docs.microsoft.com/en-us/ef/core/querying/client-eval

1
9/12/2018 11:21:07 PM

Popular Answer

I've noticed it does that when you try to return nested objects.

You can try flattening the Status object in your projection, eg. something like:

var query2 = context.CheckinTablets.Select(ct => new
        {
            Id = ct.Id,
            DeviceName = ct.Name,
            StatusName = ct.CheckinTabletStatuses
                .OrderByDescending(cts => cts.TimestampUtc).FirstOrDefault().Name
        }).ToList();


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow