EntityFramework Core Project Joined Rows For Skip/Take Pagination

Question

Using Asp.Net 3.1 Core EntityFramework Core LINQ, let's say I have an Order table and a Customer table:

public class Order
{
     public long Id { get; set; }
     public string CustomerId { get; set; }
     public int Total {get; set;}
     public virtual Customer Customer{ get; set; }
}

public class Customer : ApplicationUser
{
   public long Id {get; set;}
   public virtual ICollection<Order> Orders { get; set; }
}

Ultimately, I want to return a list of every Customer in the universe, even if they have no order (left outer?), but I also want a row for every order. So something like:

Customer    Order    Total
--------    -----    -----
1           null     null
2           100      5
2           101      199
3           null     null
4           200      299
4           201      399

The complication I ran into is that I need to do this on the server, because I need to paginate this data using skip/take. Doing a straight Context.Customer.Include(x => x.Order) doesn't project the rows the way I need them for pagination with skip/take and I'm stuck on the syntax.

Is this possible in straight LINQ? If so, what would the LINQ look like?

Thanks in advance!

Answer 1

The query you are looking for using LINQ query syntax is something like this

var query =
    from c in context.Customers
    from o in c.Orders.DefaultIfEmpty()
    select new
    {
        CustomerId = c.Id,
        OrderId = (long?)o.Id,
        Total = (int?)o.Total
    };

Some things to note.

First, DefaultIfEmpty() is what produces left outer join. Without it it would be treated as inner join.

Second, since now Order data is coming from the (optional) right side of the left outer join, you need to take into account that it could be null. In LINQ to Object that would require using conditional operator with null check, or null coalescing operator. In LINQ to Entities this is handled naturally by SQL, but you need to change the result type of non nullable fields to their nullable equivalent. Which in anonymous projections is achieved with explicit cast as shown above.

Finally, why query syntax? Of course it can be written with method syntax (SelectMany as in Steve Py's answer), but since EF Core team seems to be testing against compiler generated LINQ constructs, you can easily hit EF Core bug if you use the "wrong" overload / pattern. "Wrong" here is not really wrong, just something EF Core translator does not take into account. The "proper" here is to use the SelectMany overload with result selector:

var query = context.Customers
    .SelectMany(c => c.Orders.DefaultIfEmpty(), (c, o) => new 
    {
        CustomerId = c.Id,
        OrderId = (long?)o.Id,
        Total = (int?)o.Total
    });

With query syntax you just don't have such issues.

Answer 2

This is possible, however from what I've tested it doesn't appear to be working in EF Core 3.1.

Normally you could do this:

var results = context.Customers
    .SelectMany(c => c.Orders.DefaultIfEmpty()
        .Select(o => new { c.CustomerId, o.OrderId, o.Total }) 
    );

Then from there use the pagination /w .Skip and .Take.

The above works fine with EF6, however for EF Core 3.1 (3.1.15) c.CustomerId was returned as #null for some reason. It seems that inside the inner Select EF Core cannot / will not resolve back to the outer SelectMany customer reference. I've seen some references to issues with the DefaultIfEmpty implementation in EF Core. Seems to be another feature the Core team has been overlooking.

I've posted this in case someone out there knows of an explanation or work-around for the behaviour or can verify whether this is still the case with EF Core 5.

What I could get to work with EF Core 3.1 is a lot uglier. It requires a defined type for the return value, which you may already have or not:

[Serializable]
public class OrderData 
{
    public int CustomerId { get; set; }
    public int? OrderId { get; set; }
    public int? Total { get; set; }
}

var results = context.Customers
    .Where(c => !c.Orders.Any())
    .Select(c => new OrderData { CustomerId = c.CustomerId, OrderId = null, Total = null })
    .Union(context.Customers
        .SelectMany(c => c.Orders.Select(o => new OrderData
        {
            CustomerId = c.CustomerId,
            OrderId = o.OrderId,
            Total = o.Total
        })));

What is interesting is that the return object type is needed, as it won't union anonymous types (expected) though it does appear to require you to explicitly initialize each empty property or you get a projection exception.

From there you can order the results (After the Union) and apply the pagination.

Answer 3

What you want is a left outer join. Microsoft has information on performing a left outer join in LINQ at https://learn.microsoft.com/en-us/dotnet/csharp/linq/perform-left-outer-joins.

Adapting for your code looks like this:

from customer in context.Customers
  join order in context.Orders on customer equals order.Customer into gj
  from suborder in gj.DefaultIfEmpty()
  select new { CustomerId = customer.Id, OrderId = suborder?.Id, Total = suborder?.Total };

From there you can apply pagination.

If you prefer to use lambdas and extension methods, see How do you perform a left outer join using linq extension methods