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.