Webapi: expand causes N +1 query problem

[smasherprog]

[Table("Tracker", Schema = "HR")]
public class Tracker 
{ 
   [System.ComponentModel.DataAnnotations.Key]
    public int Id { get; set; }
    [Required, MinLength(6), MaxLength(48), Column(TypeName = "varchar(48)")]
    public string EmployeeSid { get; set; }
    public virtual ICollection<TrackerInfo> TrackerInfos { get; set; }
}
[Table("TrackerInfo", Schema = "HR")]
public class TrackerInfo 
{       
    [System.ComponentModel.DataAnnotations.Key]
    public int Id { get; set; }
    public virtual Tracker Tracker { get; set; }
    [Required]
    public int TrackerId { get; set; }
}

When expanding multiple queries are executed
Trackers?$expand=TrackerInfos

Assemblies affected

7.0 Beta 4, .net 2.1 ef core 2.1

Reproduce steps

Create two classes with one having a collection of the other. Try to do a Get with expand on the collection and the SQL generated will not be good. The query's that are generated are as below, https://github.com/aspnet/EntityFrameworkCore/issues/8531

Expected result

The query should be a join on the collection and finish but it executes a where for each child row eventhough the data has already been retrieved.

Actual result

SELECT [a.TrackerInfos].[Id], [a.TrackerInfos].[CompletedBy], [a.TrackerInfos].[CompletedDate], [a.TrackerInfos].[CreatedDate], [a.TrackerInfos].[Key_Id], [a.TrackerInfos].[SupportType], [a.TrackerInfos].[TrackerId]
FROM [HR].[TrackerInfo] AS [a.TrackerInfos]
INNER JOIN (
SELECT [a0].[Id]
FROM [HR].[Tracker] AS [a0]
) AS [t0] ON [a.TrackerInfos].[TrackerId] = [t0].[Id]
ORDER BY [t0].[Id]
[07:11:07 INF] Executed DbCommand (3ms) [Parameters=[@_outer_Id='1'], CommandType='"Text"', CommandTimeout='30']
SELECT [t].[Id], [t].[CompletedBy], [t].[CompletedDate], [t].[CreatedDate], [t].[Key_Id], [t].[SupportType], [t].[TrackerId]
FROM [HR].[TrackerInfo] AS [t]
WHERE @_outer_Id = [t].[TrackerId]
[07:11:07 INF] Executed DbCommand (2ms) [Parameters=[@_outer_Id='2'], CommandType='"Text"', CommandTimeout='30']
SELECT [t].[Id], [t].[CompletedBy], [t].[CompletedDate], [t].[CreatedDate], [t].[Key_Id], [t].[SupportType], [t].[TrackerId]
FROM [HR].[TrackerInfo] AS [t]
WHERE @_outer_Id = [t].[TrackerId]
[07:11:07 INF] Executed DbCommand (1ms) [Parameters=[@_outer_Id='3'], CommandType='"Text"', CommandTimeout='30']
SELECT [t].[Id], [t].[CompletedBy], [t].[CompletedDate], [t].[CreatedDate], [t].[Key_Id], [t].[SupportType], [t].[TrackerId]
FROM [HR].[TrackerInfo] AS [t]
WHERE @_outer_Id = [t].[TrackerId]
[07:11:07 INF] Executed DbCommand (1ms) [Parameters=[@_outer_Id='4'], CommandType='"Text"', CommandTimeout='30']
SELECT [t].[Id], [t].[CompletedBy], [t].[CompletedDate], [t].[CreatedDate], [t].[Key_Id], [t].[SupportType], [t].[TrackerId]
FROM [HR].[TrackerInfo] AS [t]
WHERE @_outer_Id = [t].[TrackerId]
[07:11:07 INF] Executed DbCommand (2ms) [Parameters=[@_outer_Id='5'], CommandType='"Text"', CommandTimeout='30']
SELECT [t].[Id], [t].[CompletedBy], [t].[CompletedDate], [t].[CreatedDate], [t].[Key_Id], [t].[SupportType], [t].[TrackerId]
FROM [HR].[TrackerInfo] AS [t]
WHERE @_outer_Id = [t].[TrackerId]
... on and on for each row

Additional detail

[xuzhg]

@smasherprog I am trying to understand your problem. In my testing, I have the following debug information:

When I issue a request: http://localhost:5000/odata/Trackers?$expand=TrackerInfos

I can get the following debug information:

So, two DB executions is your concern? It should be one execution with inner join? Please correct me if I am wrong. Any way, if that's the case, do you think that's EFCore problem or OData side problem.

[smasherprog]

Ill try to get you more information on this

[Meyce]

I am seeing a similar behavior, when adding a $expand={ChildEntity}. A query is issued that retrieves the parent record(s). Then for each parent record a query is issued for the child records. So if I use /parent?$top=10&$expand=Children, 11 queries would be issued assuming 10 parents came back. 1 query for all the parent records, and a query for the children by parent id.

Ideally this would be one inner joined query. Using EF this would be accomplished with an Include(context.Parents.Include(p => p.Children).Take(10)), but this is not behavior I've seen with Beta4.

@xuzhg your screenshot seems to support this finding. Your screenshot shows a query for Trackers and then a query for TrackerInfos by TrackerId. Assuming the Tracker table had more than 1 record I would expect you'll see a query against TrackerInfo for each Tracker, however without a sample I can only guess by what I am seeing in the screenshot.

[KyryloLehonkov]

I faced with same issue in 7.0 Release

[techniq]

Could this useful in this situation? Optimization of correlated sub-queries

[NetTecture]

Hardly. Actually OData right now is willfully ignoring dotnetcore entity framework functionality it seems.

I execute the same query

THe Lambda runs down to:

.Call System.Linq.Queryable.Select(
.Call Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.Include(
.Constant1[Data.Core.IDTLogin]>(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable1[Data.Core.IDTLogin]),
"Account"),
'(.Lambda #Lambda1

.Lambda #Lambda12[Data.Core.IDTLogin,Api.Odata.User]>(Data.Core.IDTLogin $dtoIDTLogin) { .New Api.Odata.User(){ Account = .Call System.Linq.Enumerable.ToList(.Call System.Linq.Enumerable.Select( $dtoIDTLogin.GTMandators, .Lambda #Lambda2<System.Func2[Data.Core.GTMandator,Api.Odata.Account]>)),
Comment = $dtoIDTLogin.SComment,
Created = $dtoIDTLogin.DCreateDate,
Email = $dtoIDTLogin.SEMail,
FirstName = $dtoIDTLogin.SFirstName,
Id = $dtoIDTLogin.PkLoginID,
Identity = .Call ($dtoIDTLogin.GIdentity).ToString(),
LastName = $dtoIDTLogin.SLastName,
LoginSource = $dtoIDTLogin.ILoginSource,
MailingAddress = $dtoIDTLogin.SMailingAddress,
PhoneBusiness = $dtoIDTLogin.SPhoneBusiness,
PhoneMobile = $dtoIDTLogin.SPhoneMobile,
Position = $dtoIDTLogin.SPosition,
Title = $dtoIDTLogin.STitle,
TuevEmployee = $dtoIDTLogin.BIsTuevEmployee
}
}

.Lambda #Lambda2 .New Api.Odata.Account(){
Created = $dtoGTMandator.DCreateDate,
Id = $dtoGTMandator.PkMandatorID,
Identity = $dtoGTMandator.GIdentity,
Name = $dtoGTMandator.SName
}
}

I execute once:

var data = q2.ToList()
-> Executes ONE sql query, with the (one) expand nicely in as join and EF doing the proper deconstruction.

Api.Odata.Web> info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Api.Odata.Web> Executed DbCommand (67ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
Api.Odata.Web> SELECT [t].[pkLoginID], [f.GTMandators].[dCreateDate] AS [Created], [f.GTMandators].[pkMandatorID] AS [Id], [f.GTMandators].[gIdentity] AS [Identity], [f.GTMandators].[sName] AS [Name], [f.GTMandators].[fkLoginID]
Api.Odata.Web> FROM [dbo].[G_TMandator] AS [f.GTMandators]
Api.Odata.Web> INNER JOIN (
Api.Odata.Web> SELECT [f0].[pkLoginID]
Api.Odata.Web> FROM [dbo].[ID_TLogin] AS [f0]
Api.Odata.Web> WHERE CHARINDEX(N'{deleted}', [f0].[sEMail]) <= 0
Api.Odata.Web> ) AS [t] ON [f.GTMandators].[fkLoginID] = [t].[pkLoginID]
Api.Odata.Web> ORDER BY [t].[pkLoginID]

return Ok(q2);
-> N+1 nightmare with every single relationship manually requeried.

I am not sure what OData does to EFCore, but it actually REMOVES the includes. The following SQL Sequences are executed:

Api.Odata.Web> info: Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker[2]
Api.Odata.Web> Executed action method Api.Odata.Web.Controllers.UserController.Get (Api.Odata.Web), returned result Microsoft.AspNetCore.Mvc.OkObjectResult in 5468.481ms.
Api.Odata.Web> info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Api.Odata.Web> Executed DbCommand (56ms) [Parameters=[@__p_1='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
Api.Odata.Web> SELECT TOP(@__p_1) [f].[sComment] AS [Comment], [f].[dCreateDate] AS [Created], [f].[sEMail] AS [Email], [f].[sFirstName] AS [FirstName], [f].[pkLoginID] AS [Id], CONVERT(VARCHAR(36), [f].[gIdentity]) AS [Identity], [f].[sLastName] AS [LastName], [f].[iLoginSource] AS [LoginSource], [f].[sMailingAddress] AS [MailingAddress], [f].[sPhoneBusiness] AS [PhoneBusiness], [f].[sPhoneMobile] AS [PhoneMobile], [f].[sPosition] AS [Position], [f].[sTitle] AS [Title], [f].[bIsTuevEmployee] AS [TuevEmployee]
Api.Odata.Web> FROM [dbo].[ID_TLogin] AS [f]
Api.Odata.Web> WHERE CHARINDEX(N'{deleted}', [f].[sEMail]) <= 0
Api.Odata.Web> ORDER BY [Identity], [f].[pkLoginID]
Api.Odata.Web> info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Api.Odata.Web> Executed DbCommand (66ms) [Parameters=[@__p_1='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
Api.Odata.Web> SELECT [t].[c], [t].[pkLoginID], [f.GTMandators].[dCreateDate] AS [Created], [f.GTMandators].[pkMandatorID] AS [Id], [f.GTMandators].[gIdentity] AS [Identity], [f.GTMandators].[sName] AS [Name], [f.GTMandators].[fkLoginID]
Api.Odata.Web> FROM [dbo].[G_TMandator] AS [f.GTMandators]
Api.Odata.Web> INNER JOIN (
Api.Odata.Web> SELECT TOP(@__p_1) CONVERT(VARCHAR(36), [f0].[gIdentity]) AS [c], [f0].[pkLoginID]
Api.Odata.Web> FROM [dbo].[ID_TLogin] AS [f0]
Api.Odata.Web> WHERE CHARINDEX(N'{deleted}', [f0].[sEMail]) <= 0
Api.Odata.Web> ORDER BY [c], [f0].[pkLoginID]
Api.Odata.Web> ) AS [t] ON [f.GTMandators].[fkLoginID] = [t].[pkLoginID]
Api.Odata.Web> ORDER BY [t].[c], [t].[pkLoginID]
Api.Odata.Web> info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Api.Odata.Web> Executed DbCommand (42ms) [Parameters=[@_outer_PkLoginID='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
Api.Odata.Web> SELECT [dtoGTMandator].[dCreateDate] AS [Created], [dtoGTMandator].[pkMandatorID] AS [Id], [dtoGTMandator].[gIdentity] AS [Identity], [dtoGTMandator].[sName] AS [Name]
Api.Odata.Web> FROM [dbo].[G_TMandator] AS [dtoGTMandator]
Api.Odata.Web> WHERE @_outer_PkLoginID = [dtoGTMandator].[fkLoginID]
Api.Odata.Web> info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Api.Odata.Web> Executed DbCommand (43ms) [Parameters=[@_outer_PkLoginID='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
Api.Odata.Web> SELECT [dtoGTMandator].[dCreateDate] AS [Created], [dtoGTMandator].[pkMandatorID] AS [Id], [dtoGTMandator].[gIdentity] AS [Identity], [dtoGTMandator].[sName] AS [Name]
Api.Odata.Web> FROM [dbo].[G_TMandator] AS [dtoGTMandator]
Api.Odata.Web> WHERE @_outer_PkLoginID = [dtoGTMandator].[fkLoginID]
Api.Odata.Web> info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Api.Odata.Web> Executed DbCommand (43ms) [Parameters=[@_outer_PkLoginID='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
Api.Odata.Web> SELECT [dtoGTMandator].[dCreateDate] AS [Created], [dtoGTMandator].[pkMandatorID] AS [Id], [dtoGTMandator].[gIdentity] AS [Identity], [dtoGTMandator].[sName] AS [Name]
Api.Odata.Web> FROM [dbo].[G_TMandator] AS [dtoGTMandator]
Api.Odata.Web> WHERE @_outer_PkLoginID = [dtoGTMandator].[fkLoginID]

OData is ACTIVELY FIGHTING EF. As you can see, EF is actually doing the same SQL a in the first query, with joiningi n the expand (and yes, that is because I manually parse the ODataOptions and extract the includes needed). THEN it goes on and does subquery by subquery.

I would be totally happy if OData would stop fighting - if the relationship has a collection FROM EF then use it and do not issue another SQL.

[bdebaere]

I concur. I'm almost completely sure the problem is related to one to many relationships. OData generates a single query per relation. This is a real performance killer at the moment.

This generates 3 queries with EF Core:

    _dbSet<Product>
                .Include(i => i.ProductProductInformationGroups)
                .ThenInclude(ti => ti.ProductInformationGroup)
                .ThenInclude(ti => ti.ProductInformationGroupProductAttributeValues)
                .ThenInclude(ti => ti.ProductAttributeValue)
                .ThenInclude(ti => ti.ProductAttribute)
                .ThenInclude(ti => ti.ProductAttributeGroup)
                .ToList();

The same written as OData query generates multiple singular queries per relationship:
odata/Products?$expand=ProductProductInformationGroups($expand=ProductInformationGroup($expand=ProductInformationGroupProductAttributeValues($expand=ProductAttributeValue($expand=ProductAttribute($expand=ProductAttributeGroup)))))

[ysmoradi]

@bdebaere If you're calling ToList and then returning the materialized result to oData, then those 3 queries are being performed by ef core, no matter you use OData or not. Test that in a simple console app first.

There are some problems with Associations+OData+EntityFrameworkCore too, but as long as you call ToList, your issue is Associations+EntityFrameworkCore only.

[joshcomley]

This is an issue with OData, please see my PR to fix this:

https://github.com/OData/WebApi/pull/1653

[Mhirji]

Hello,
Just wondering if the PR submitted by joshcomley will be merged? If you could please let us know it would be great as there seem to be a few of us whom are suffering a bit from the issues this PR fixes.
Thanks!!

[Mhirji]

@joshcomley thanks for the PR. Do you have any insights on why it is still waiting for review?

[Mhirji]

Does anyone have any more news on this?

[smasherprog]

I havent heard anything.. I wish this would get merged already

[bdebaere]

For your information, the problem is not resolved when upgrading to EF Core 3.0.0-preview4.19216.3.

[yadavvineet]

this is a serious issue. please approve the PR or provide your own fix, but do something.

[vazavi]

How long will it take to resolve this problem?

[KanishManuja-MS]

@vazavi This is on our list for the next WebAPI release unless something unexpected pops up.

[mikeoconnor1980]

Do you guys know when you're planning on releasing the next release? When might it be available in Preview?

Cheers,
Mike

[RMHR]

@vazavi This is on our list for the next WebAPI release unless something unexpected pops up.

Do we know when this release is, any information available?

[PaulGilchrist]

We are waiting on this fix also. It has critically slowed down expands for us since switching from full framework to .Net core, and making OData almost unusable for us.

[erjicles]

I'm shocked this isn't receiving a higher priority. This bug can result in thousands of round trips to the database and kill performance. Until this gets fixed, $expand and in turn OData is effectively unusable in many situations.

[NetTecture]

This is actually a non issue. Took me 2 days to extract all possible includes from the query graph. Now I get nice efficiency because I can INCLUDE what I need on the query ;)

I use AutoMapper ProjectTo, so I can take the business level expands and directly hand them in as array of includes from the business side. Voila. As I said - about 2 days of work.

MUCH worse are the significant bugs on the EFCore side which force me to ignore all filters and conditions and pull all data in memory. Global filters basically result often excpetions as they do some bad optimizations for some foreign key scenarios. I possibly could handle that too, but it is a little more complex.

[erjicles]

This is actually a non issue. Took me 2 days to extract all possible includes from the query graph. Now I get nice efficiency because I can INCLUDE what I need on the query ;)

I use AutoMapper ProjectTo, so I can take the business level expands and directly hand them in as array of includes from the business side. Voila. As I said - about 2 days of work.

MUCH worse are the significant bugs on the EFCore side which force me to ignore all filters and conditions and pull all data in memory. Global filters basically result often excpetions as they do some bad optimizations for some foreign key scenarios. I possibly could handle that too, but it is a little more complex.

If I'm understanding this correctly, you're manually parsing the expands in your controller and adding Includes to the LINQ query before it even hits the OData pipeline so they're already built into the query?

[NetTecture]

Actually no. I am intercepting the generated LINQ decision tree and parsing it for all necessary expansions ;)

[erraineon]

Actually no. I am intercepting the generated LINQ decision tree and parsing it for all necessary expansions ;)

this could help greatly until the issue is addressed. could you please share excerpts of your code here

[jtrout]

Why was this closed? Has it been resolved somehow?

[rodrigocmoreira]

Even with the suggested fix, I could not make it work. The proposed fix really worked for someone?

[vvopenka]

We managed to fix it by upgrading to Microsoft.AspNetCore.OData version 7.2.2 and adding EnableQuery to the Get method in the controller:

[EnableQuery(EnableCorrelatedSubqueryBuffering = true)]
public IQueryable<T> Get()

The global fix as described in the pull request didn't work for us.

[rodrigocmoreira]

We managed to fix it by upgrading to Microsoft.AspNetCore.OData version 7.2.2 and adding EnableQuery to the Get method in the controller:

[EnableQuery(EnableCorrelatedSubqueryBuffering = true)]
public IQueryable<T> Get()

The global fix as described in the pull request didn't work for us.

Works great 👍

[ankitpatel5989]

I have tried same updated version and able to run with given parameter EnableCorrelatedSubqueryBuffering = true but still generating N+1 queries.

Please guide me if I missed something.

[NetTecture]

I do not think you will be lucky. We had it working but with EXTREME problems with Ef Core (and basically had an inspector that decided whether to have EfCore do the filtering or loading all data and filter in memory).

Roight now moving back to Ef Classic - yeah, older, slower blablabla but IT WORKS. And it works in .NET core since Ef Classic 6.4 is netstandard.

I use odata -> Automapper (ProjectTo) -> Ef (classic) and have a module that gets me all the includes that I need from the filter condition.