Query: Optimize navigation property translation in projections

[rowanmiller]

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:

• LINQ Join to Top(1) - expected SQL? #5754
• Query Perf: Selecting entire table for navigation properties in projections #5738
• EF is generating SQL that returns the entire table without join #5791

[sethreidnz]

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?

[rowanmiller]

@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.

[petreikis]

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

[rowanmiller]

@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 })

[blackbelt080]

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

[maumar]

Adding for visibility: @divega suggested in #6953 (comment) 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

[riezebosch]

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/

[maumar]

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

[simeyla]

@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!

[maumar]

@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]

@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

[smitpatel]

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

[maumar]

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

[MaklaCof]

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.

[maumar]

@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]

addressed by #9282 (commit b95f23f)