Entity Framework subqueries with EXISTS

45 Views Asked by At

I have tables Customers (CustomerID, Name, etc.) and Orders (OrderID, CustomerID, etc).

How do I select customers that have at least one order in EF, if possible using a subquery?

I'm using LINQ.

I know how to do it in SQL with a subquery (case when exists (select 1 from Order ...)).

I tried the following with grouping and summarising, but it could be expensive when there are a lot of orders.

context.Customers
   .Where(c => c.TypeID == 5)
   .Include(x => x.AdditionalParameters)
   .Join(context.Orders
                .GroupBy(p => p.CustomerID)
                .Select(p => new { p.Key, Sum = p.Sum(s => s.TotalPrice)}),
         x => (int)x.Id,
         x => (int)x.Key,
         (customer, order) => new
                              {
                                  customer.CustomerID,
                                  customer.Name,
                                  order.Sum
                              })
   .AsNoTracking()
   .ToListAsync(cancellationToken))
   .Select(x => new Result
        {
            CustomerID = x.CustomerID,
            CustomerName = x.Name,
            HasOrders = x.Sum > 0
        })
   .ToList();

I don't want to de-normalize the DB.

0

There are 0 best solutions below