Efcore: Query: Optimize navigation property translation in projections

Created on 8 Dec 2015 · 16Comments · Source: dotnet/efcore

For example, customers.Select(c => new { c.Id, Orders = c.Orders }) results in n+1 at the moment.

Make sure to consider the following scenarios when looking at this:

closed-fixed type-enhancement

Source

rowanmiller

👍2

Most helpful comment

@MaklaCof I'm working on this feature right now. Its scheduled to be shipped with the next release of EF Core. Unless I encounter some unexpected issues the code should be in our dev branch in a next couple weeks or so - ready to be tested

maumar on 21 Nov 2017

👍12 🎉3 ❤1

All 16 comments

Is this improvement likely to be in the RTM? Does this same issue exist in EF6?

As another note do you know any way we can work around this issue or basically we are stuck getting the whole entity back even if we only want 1 of 10 fields for example?

sethreidnz on 7 Jan 2016

@justsayno the N+1 only occurs when you are including navigation properties. The workaround would be to retrieve the scalar properties in one query and then pull back the children in a separate query. If you are just pulling back 1 scalar field then there is no N+1.

rowanmiller on 7 Jan 2016

How likely is it that this issue will be fixed in the RTM?

petreikis on 20 Jan 2016

@SoftwareFactor it won't be (purely due to time constraints) - we've already put this in the post-1.0.0 milestone.

Note you can do this, which will pull a little more data back from the database but avoid the N+1 issue.

customers.Include(c => c.Orders).ToList().Select(c => new { c.Id, Orders = c.Orders })

rowanmiller on 20 Jan 2016

👍1

Is a query to the database going to automagically in the SQL? Why not use python to create an auto increment feature?

blackbelt080 on 30 Jan 2016

Adding for visibility: @divega suggested in https://github.com/aspnet/EntityFramework/issues/6953#issuecomment-261137544 that we could use ToList added explicitly to the projected collection that customer wants to opt-out of streaming and bring all the results at once instead

maumar on 27 Nov 2016

I've created this small framework to scan for N+1 queries and included the unit tests that validates this exact behaviour.

Examples: https://github.com/riezebosch/efcore-practices/blob/master/src/EFCore.Practices.Tests/SelectPlusOneVerifierTests.cs

Framework: https://www.nuget.org/packages/EFCore.Practices/

riezebosch on 6 Jan 2017

his is partially addressed by #8584 - if the collection navigation is not composed on, we re-use include pipeline which creates 2 queries, rather than N+1. If the collection is filtered however, we still use the old rewrite and N+1 queries are being issued

maumar on 6 Jun 2017

👍1

@maumar Could you help me understand a little what exactly would cause the 'regression' here.

Here's what I'm wanting to do :

Emails.Select(e => new Email { Subject = e.Subject, Tags = e.Tags.Select( t => new Tag { Name = t.Name }) } )

Are you saying that I can filter, or order the Emails but not the Tags? Are there specific operations you know for sure would or wouldn't break it.

I'm planning whether to revert to EF6 or wait for an RC for EF Core 2.

Thanks!

simeyla on 27 Jun 2017

@simeyla problem is when you are projecting a collection navigation, so each email has a collection of Tags. EF core is not able to create a single TSQL query for this in general. We mitigate this in #8584 for the cases where the navigation is projected directly (i.e. doesn't have a filter or a projection) - we then convert it to include call.

To answer your question specifically: yes, you can filter emails, and apply custom projection on it (as you do in the example), however Tags (which are the nested collection) can't have any of those operations.

You can use include pattern instead:

Emails.Include(e => e.Tags).ToList().Select(e => new Email { Subject = e.Subject, Tags = e.Tags.Select( t => new Tag { Name = t.Name }) } )

This will issue only 2 queries (one for emails and one for associated tags, however will fetch all columns in tags entity and you can't apply any filters on tags (you can apply them on emails however)

In preview 2 (with #8584) you will be able to do:

Emails.Select(e => new Email { Subject = e.Subject, Tags = e.Tags })

and get the same result, since it will be internally rewritten to:

Emails.Select(e => new Email { Subject = e.Subject, _Include(e, e.Tags).Tags })

So depending on your project, if Tag entity doesn't have many columns and you don't need to filter them (i.e. you want to return all tags for a given email) then perhaps using include is fine. Otherwise you might need to construct the query manually using joins, or revert to EF6.

maumar on 27 Jun 2017

❤1

@simeyla here is a sample of how a manually created query (using joins) would look like:

        public class MyContext : DbContext
        {
            public DbSet<Email> Emails { get; set; }

            public DbSet<Tag> Tags { get; set; }

            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                optionsBuilder.UseSqlServer(@"Server=.;Database=Sample4007;Trusted_Connection=True;MultipleActiveResultSets=True");
            }
        }

        public class EmailDto
        {
            public string Subject { get; set; }

            public List<TagDto> Tags { get; set; }
        }

        public class TagDto
        {
            public int Id { get; set; }
            public string Name { get; set; }
        }

        public class Email
        {
            public int Id { get; set; }
            public string Subject { get; set; }

            public List<Tag> Tags { get; set; }
        }

        public class Tag
        {
            public int Id { get; set; }
            public string Name { get; set; }
            public int EmailId { get; set; }

            public Email Email { get; set; }
        }

        public void ManualQuerySample()
        {
            using (var ctx = new MyContext())
            {
                ctx.Database.EnsureDeleted();
                ctx.Database.EnsureCreated();

                var t11 = new Tag { Name = "t11" };
                var t12 = new Tag { Name = "t12" };
                var t13 = new Tag { Name = "t13" };
                var t21 = new Tag { Name = "t21" };
                var t22 = new Tag { Name = "t22" };
                var t31 = new Tag { Name = "t31" };

                var e1 = new Email { Subject = "email1", Tags = new List<Tag> { t11, t12, t13 } };
                var e2 = new Email { Subject = "email2", Tags = new List<Tag> { t21, t22 } };
                var e3 = new Email { Subject = "email3", Tags = new List<Tag> { t31 } };

                ctx.Tags.AddRange(t11, t12, t13, t21, t22, t31);
                ctx.Emails.AddRange(e1, e2, e3);
                ctx.SaveChanges();

                var emails = ctx.Emails.ToList();
            }

            using (var ctx = new MyContext())
            {
                var query = from e in ctx.Emails
                            where e.Id < 3
                            join t in ctx.Tags on e.Id equals t.EmailId into grouping
                            from t in grouping.Where(g => !g.Name.EndsWith("1")).DefaultIfEmpty()
                            select new { Email = e, t.Name };

                var result = query.ToList()
                        .GroupBy(key => key.Email, element => element.Name)
                        .Select(g => new EmailDto { Subject = g.Key.Subject, Tags = g.Select(t => new TagDto { Name = t }).ToList() });
            }
        }

It's quite tricky to write, but you can do filtering and custom projections on inner and outer collections. Basically the idea is to create a groupjoin, which pairs up the email entities and their tags - you can apply filter on emails directly (see: where e.Id >3, and filters to tags are applied on the grouping, see from t in grouping.Where(g => !g.Name.EndsWith("1")).DefaultIfEmpty()

When you apply all the filters, you project both into anonymous type. This will produce the following SQL, that fetches all the (filtered) emails and tags in one query:

SELECT [e].[Id], [e].[Subject], [t0].[Name]
FROM [Emails] AS [e]
LEFT JOIN (
    SELECT [t].*
    FROM [Tags] AS [t]
    WHERE RIGHT([t].[Name], LEN(N'1')) <> N'1'
) AS [t0] ON [e].[Id] = [t0].[EmailId]
WHERE [e].[Id] < 3

now you need to group by the results by email, and shape the result into DTOs

maumar on 27 Jun 2017

👍2 ❤1

@maumar @anpete - Is it different from the work Maurycy doing?

smitpatel on 10 Nov 2017

@smitpatel the work I'm doing will cover this case

maumar on 11 Nov 2017

Does anyone can provide any information, when N+1 will be fixed? I really hope that after 2 years, this problem will get attention it deserves.
I gladly volunteer for any pre-release.