to put Take() in the different place will get different SQL

[xuzhg]

If we put "Take()" in the different place, we will get different SQL.

Steps to reproduce

1. Just create a basic EF Core console project,
2. Create some POCO class and DBContext class, for example "CustomerOrderContext".
3. Do the following query/project.
4. Notice the different Take(1) method call.
5. Verify the log output.

1

using (CustomerOrderContext db = new CustomerOrderContext())
{
    var aa = db.Customers.Select(e => new Customer
    {
        FirstName = e.FirstName,
        LastName = e.LastName,
        UserName = e.UserName
    })
    .Take(1)
    .Select(e => new CusomterDto { FullName = e.FirstName + " " + e.LastName });
    foreach (var a in aa)
    {
        Console.WriteLine(a.FullName);
    }
}

If output the log, I can get the following information:

Be noted that it retrieves the "UserName" property, however that property doesn't used in the later.

2

using (CustomerOrderContext db = new CustomerOrderContext())
{
    var aa = db.Customers.Select(e => new Customer
    {
        FirstName = e.FirstName,
        LastName = e.LastName,
        UserName = e.UserName
    })    
    .Select(e => new CusomterDto { FullName = e.FirstName + " " + e.LastName })
    .Take(1);
    foreach (var a in aa)
    {
        Console.WriteLine(a.FullName);
    }
}

If output the log, I can get:

SELECT TOP(@__p_0) ([e].[FirstName] + N' ') + [e].[LastName] AS [FullName]
      FROM [Customers] AS [e]

Be noted that "UserName" property doesn't retrieve from the DB.

So, that's by design or did I make some mistaken or missed something? Thanks.

[maumar]

This is by design, Take operator forces us to produce a subquery, which complicates the overall query. You should still get correct results for both queries, difference is that in first case the second projection is performed on the client.

In general you will get better queries if operators like Skip/Take/Distinct are used as late as possible

[divega]

@maumar maybe I am missing something but it seems reasonable to expect Take to be lifted here. I get that we don't do it, and that maybe this scenario alone doesn't justify changing that, but is there any fundamental reason we can't?

[divega]

Reopening this so we can discuss it in triage.

[maumar]

We can optimize this (i.e. move Take() further if there are no filters in the query after the place where Take was originally.

This can be optimized:

var aa = db.Customers.Select(e => new Customer
    {
        FirstName = e.FirstName,
        LastName = e.LastName,
        UserName = e.UserName
    })
    .Where(e => e.UserName != "Foo")
    .Take(1)
    .Select(e => new CusomterDto { FullName = e.FirstName + " " + e.LastName });

This can't be optimized:

var aa = db.Customers.Select(e => new Customer
    {
        FirstName = e.FirstName,
        LastName = e.LastName,
        UserName = e.UserName
    })
    .Take(1)
    .Where(e => e.UserName != "Foo")
    .Select(e => new CusomterDto { FullName = e.FirstName + " " + e.LastName });