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.