// Basic LINQ query with implicit joins
var activeCustomersWithOrders = await context.Customers
.Where(c => c.IsActive)
.Where(c => c.Orders.Any(o => o.OrderDate > DateTime.Now.AddMonths(-6)))
.Select(c => new {
c.Name,
c.Email,
RecentOrderCount = c.Orders.Count(o => o.OrderDate > DateTime.Now.AddMonths(-6)),
TotalSpent = c.Orders.Sum(o => o.Total)
})
.ToListAsync();
// Complex query with multiple levels of joins and grouping
var productSalesSummary = await context.Products
.Where(p => p.IsActive)
.SelectMany(p => p.OrderItems)
.Where(oi => oi.Order.OrderDate >= startDate && oi.Order.OrderDate <= endDate)
.GroupBy(oi => new {
oi.Product.Category.Name,
oi.Product.Name,
Month = oi.Order.OrderDate.Month,
Year = oi.Order.OrderDate.Year
})
.Select(g => new {
Category = g.Key.Name,
Product = g.Key.Name,
Period = $"{g.Key.Year}-{g.Key.Month:00}",
TotalQuantity = g.Sum(oi => oi.Quantity),
TotalRevenue = g.Sum(oi => oi.Quantity * oi.UnitPrice),
OrderCount = g.Select(oi => oi.OrderId).Distinct().Count(),
AvgOrderValue = g.GroupBy(oi => oi.OrderId).Average(og => og.Sum(oi => oi.Quantity * oi.UnitPrice))
})
.OrderByDescending(x => x.TotalRevenue)
.ToListAsync();
// Subquery with conditional logic - ERP invoice processing example
var overdueInvoicesWithCustomerInfo = await context.Invoices
.Where(inv => inv.DueDate < DateTime.Now && inv.Status != InvoiceStatus.Paid)
.Where(inv => inv.Customer.CreditLimit <
context.Invoices
.Where(i => i.CustomerId == inv.CustomerId && i.Status == InvoiceStatus.Outstanding)
.Sum(i => i.Amount))
.Select(inv => new {
InvoiceNumber = inv.Number,
Customer = inv.Customer.Name,
Amount = inv.Amount,
DaysOverdue = (DateTime.Now - inv.DueDate).Days,
CustomerTotalOutstanding = context.Invoices
.Where(i => i.CustomerId == inv.CustomerId && i.Status == InvoiceStatus.Outstanding)
.Sum(i => i.Amount),
CustomerCreditLimit = inv.Customer.CreditLimit,
CustomerLastPayment = inv.Customer.Payments
.OrderByDescending(p => p.PaymentDate)
.Select(p => p.PaymentDate)
.FirstOrDefault()
})
.ToListAsync();
// Complex ERP reporting query with multiple conditional joins
var salesPerformanceReport = await context.SalesReps
.Where(sr => sr.IsActive)
.Select(sr => new {
SalesRep = sr.Name,
Territory = sr.Territory.Name,
// This quarter's performance
Q1Sales = sr.Orders
.Where(o => o.OrderDate >= q1Start && o.OrderDate <= q1End)
.Sum(o => o.Total),
Q1CustomerCount = sr.Orders
.Where(o => o.OrderDate >= q1Start && o.OrderDate <= q1End)
.Select(o => o.CustomerId)
.Distinct()
.Count(),
// Compared to same quarter last year
Q1LastYearSales = sr.Orders
.Where(o => o.OrderDate >= q1LastYearStart && o.OrderDate <= q1LastYearEnd)
.Sum(o => o.Total),
// Top performing product categories
TopCategories = sr.Orders
.Where(o => o.OrderDate >= q1Start && o.OrderDate <= q1End)
.SelectMany(o => o.OrderItems)
.GroupBy(oi => oi.Product.Category.Name)
.OrderByDescending(g => g.Sum(oi => oi.Quantity * oi.UnitPrice))
.Take(3)
.Select(g => new { Category = g.Key, Sales = g.Sum(oi => oi.Quantity * oi.UnitPrice) })
.ToList(),
// Performance against quota
QuotaAttainment = sr.Quotas
.Where(q => q.Period >= q1Start && q.Period <= q1End)
.Sum(q => q.Amount) > 0
? (sr.Orders
.Where(o => o.OrderDate >= q1Start && o.OrderDate <= q1End)
.Sum(o => o.Total) /
sr.Quotas
.Where(q => q.Period >= q1Start && q.Period <= q1End)
.Sum(q => q.Amount)) * 100
: 0
})
.ToListAsync();
// Seamless transition between database and in-memory operations
var customersNeedingAttention = await context.Customers
.Include(c => c.Orders)
.Include(c => c.SupportTickets)
.Where(c => c.IsActive)
.ToListAsync();
// Now work with the data in-memory using the same LINQ syntax
var riskAnalysis = customersNeedingAttention
.Where(c => c.Orders.Any(o => o.OrderDate > DateTime.Now.AddMonths(-12))) // Active in last year
.Select(c => new {
Customer = c,
RiskScore = CalculateRiskScore(c), // Custom business logic
RecentOrderTrend = c.Orders
.Where(o => o.OrderDate > DateTime.Now.AddMonths(-6))
.OrderBy(o => o.OrderDate)
.Select(o => o.Total)
.ToList(), // In-memory enumerable
SupportTicketTrend = c.SupportTickets
.Where(t => t.CreatedDate > DateTime.Now.AddMonths(-3))
.GroupBy(t => new { t.CreatedDate.Year, t.CreatedDate.Month })
.Select(g => new { Period = $"{g.Key.Year}-{g.Key.Month}", Count = g.Count() })
.ToList()
})
.Where(x => x.RiskScore > 70) // Apply business logic filter
.OrderByDescending(x => x.RiskScore)
.ToList();
// Example of conditional query building - common in ERP systems
IQueryable<Order> ordersQuery = context.Orders.Include(o => o.Customer).Include(o => o.OrderItems);
if (filterBy.CustomerId.HasValue)
ordersQuery = ordersQuery.Where(o => o.CustomerId == filterBy.CustomerId);
if (filterBy.StartDate.HasValue)
ordersQuery = ordersQuery.Where(o => o.OrderDate >= filterBy.StartDate);
if (filterBy.MinAmount.HasValue)
ordersQuery = ordersQuery.Where(o => o.Total >= filterBy.MinAmount);
if (filterBy.ProductCategoryId.HasValue)
ordersQuery = ordersQuery.Where(o => o.OrderItems.Any(oi => oi.Product.CategoryId == filterBy.ProductCategoryId));
if (filterBy.SalesRepId.HasValue)
ordersQuery = ordersQuery.Where(o => o.Customer.SalesRepId == filterBy.SalesRepId);
// The final query is only executed here, and EF optimizes the entire expression tree
var filteredOrders = await ordersQuery
.Select(o => new {
o.Id,
o.OrderDate,
CustomerName = o.Customer.Name,
o.Total,
ItemCount = o.OrderItems.Count(),
SalesRep = o.Customer.SalesRep.Name
})
.OrderByDescending(o => o.OrderDate)
.Take(100)
.ToListAsync();
// Helper method for risk calculation (would contain complex business logic)
private static int CalculateRiskScore(Customer customer)
{
// Complex business logic that might involve multiple factors
var daysSinceLastOrder = (DateTime.Now - customer.Orders.Max(o => o.OrderDate)).Days;
var avgOrderValue = customer.Orders.Average(o => o.Total);
var supportTicketCount = customer.SupportTickets.Count(t => t.CreatedDate > DateTime.Now.AddMonths(-6));
// Simplified risk calculation
var riskScore = Math.Min(100, daysSinceLastOrder / 3 + supportTicketCount * 10);
return riskScore;
}