Returning a list inside Linq query from left outer join-CodePudding

I'm new with Linq and hoping for some clarity on a particular query.

I have two tables (simplified for demonstration):

Table: Customer

CustomerId | Name
1          | John Smith
2          | Peter James

Table: Order

id | CustomerId | Total
1  | 1          | $100
2  | 1          | $200

Sample CustomerDto:

public class CustomerDto
{
    public long CustomerId { get; set; }
    public string Name{ get; set; }
    public CustomerOrder[] CustomerOrderList{ get;set;}
}

Linq example for left outer join in the select here, they return string.empty if the join fails, I'm not sure the equivalent when I'm returning an object rather than a string.

My Linq query looks as follows. I've used the DefaultIfEmpty() to assist in a left outer join, however given I'm dealing with my object, I'm not sure how to return null if there isn't anything.

            IQueryable<CustomerDto> search =
            from customer in _database.Customer
            join customerOrder in _database.CustomerOrder on customer.CustomerId equals customerOrder.CustomerId into CS
                from subCustomerSale in CS.DefaultIfEmpty()
            select new CustomerDto
            {
                CustomerId = customer.CustomerId,
                Name = customer.Name,
                CustomerOrderList = subCustomerSale
            };

As I mentioned, I want to return a list of orders rather than one row per order. So there should be two records returned (the two customers), one with a list of orders and the other without any.

How do I achieve this?

CodePudding user response：

just put the ternary condition to achieve it like follows:

IQueryable<CustomerDto> search =
            from customer in _database.Customer
            join customerOrder in _database.CustomerOrder on customer.CustomerId equals customerOrder.CustomerId into CS
                from subCustomerSale in CS.DefaultIfEmpty()
         select new CustomerDto
         {
           CustomerId = customer.CustomerId,
           Name = customer.Name,
           CustomerOrderList = subCustomerSale == null ? null : subCustomerSale // add this line and you will get the null as well if there is no record
          };

CodePudding user response：

The first step to make the entities easier to work with is to ensure that navigation properties are set up. If the table is called "Order" then the entity can be Order, or renamed to CustomerOrder if you like. A Customer entity can have an ICollection<Order> collection which EF can automatically map provided a consistent naming convention and normalization is used. (Otherwise explicit mapping can be provided)

For example:

public class Customer
{
    [Key]
    public int CustomerId { get; set; }

    // other customer fields.

    public virtual ICollection<Order> Orders { get; set; } = new List<Order>();
}

From here you are projecting Customers to a CustomerDTO, so we should also project the Orders to an OrderDTO. Note that when using navigation properties we don't have to explicitly join entities. We don't even have to eager load related data via Include(). The later would apply if/when we want to work with the entities rather than projections.

The resulting query would end up looking like:

IQueryable<CustomerDto> search = _database.Customer
    .Select(c => new CustomerDto
    {
        CustomerId = c.CustomerId,
        Name = c.Name,
        Orders = c.Orders.Select(o =>
        {
            OrderId = o.OrderId,
            Total = o.Total
        }).ToList()
    });

The benefit is no need to explicitly write Join expressions. EF can help simplify accessing related data considerably rather than just facilitating using Linq as an alternative to SQL. This would return an empty list rather than #null if there are no Orders for that customer. It may be possible to substitute a #null if there aren't any orders, though worst case it could be post-processed after the results are materialized Ie:

var customers = await search.ToListAsync();
var noOrderCustomers = customers.Where(c => !c.Orders.Any()).ToList();
foreach(var customer in noOrderCustomers)
    customer.Orders = null;

It really just boils down to whether the consumer is Ok knowing there is always an Orders collection that will be empty if there are no orders, or in the Orders collection is only present if there are orders. (via JSON etc. serialization)

The important details to consider: When filtering, such as filling in search criteria, do this before the Select as the IQueryable is working with entities so you have full access to the table fields. Adding Where clauses after the Select will limit the available fields to the ones you have selected for the DTO. (They will still bubble down into the SQL) There is a ToList inside the Select to build the Orders collection. This may look "bad" that it might be materializing data synchronously, but it will be executed only when the main query is. (Such as an awaited async operation on the IQueryable)

When projecting to DTOs be sure not to mix DTOs and entities such as:

IQueryable<CustomerDto> search = _database.Customer
    .Select(c => new CustomerDto
    {
        CustomerId = c.CustomerId,
        Name = c.Name,
        Orders = c.Orders
    });

... which can be tempting. The issue here is that "c.Orders" would return a collection of Order entities. Those Orders may have references to other entities or information you don't need to/want to expose to the consumer. Accessing references could result in lazy load costs, null references, or exceptions (i.e. disposed DbContext) depending on when/where they occur.