I have a fairly simple problem, I think. I want to use the result of a LINQ query with some kind of groupby in another query, but I cannot figure this out. I have a database with a Company table, an Office table, a Department table and an OrderDetails table. Companies have offices, offices have departments. Orders are registered by department, but I need them by office (per date).
These are my models:
public class Company
{
public string Name { get; set; }
public int Id { get; set; }
public IEnumerable<Office> offices { get; set; }
}
public class Office
{
public string Name { get; set; }
public int NumberOfEmployees { get; set; }
public IEnumerable<Order> ordersPerDate { get; set; }
}
public class Order
{
public int OfficeId;
public DateTime Date { get; set; }
public Size Size { get; set; }
public IEnumerable<OrderDetail> Details { get; set; }
}
I want to make a collection of companies. Every company has a lists of offices and in that list I want another list with all orders on the same Date that belong to that office. So first I create a collection of orders per date, per office:
//Orders are registred by department, but I need them by office (per date)
var orderDetails = _context.TblOrderDetails
.Join(_context.TblDepartments, p => p.TblDepartmentId, department => department.Id, (p, department) => new { p, department })
.Join(_context.TblOffices, department2 => department2.department.TblOfficeId, office => office.Id, (department2, office) => new { department2, office })
.GroupBy(x => new { x.office.Id, x.department2.p.OrderDate.Date })
.Select(output => new Order
{
OfficeId = output.Key.Id,
Date = output.Key.Date,
Size = new Size
{
Unknown = output.Sum(s => s.department2.p.QuantityTotal1),
Large = output.Sum(s => s.department2.p.QuantityTotal2),
Medium = output.Sum(s => s.department2.p.QuantityTotal3),
Small = output.Sum(s => s.department2.p.QuantityTotal4),
Other = output.Sum(s => s.department2.p.QuantityTotal5)
},
Details = new List<OrderDetail>()
}).ToList();
Now I want to use these orderDetails in another query and I cannot figure out how I have to do this. So far I've came up with 3 "solutions", but they all fail.
var companyData = _context.TblCompanies
.Include("TblOffices")
.Where(x => x.TblOffices.Any(x => x.Description.Length > 0))
.Select(company => new Company
{
Name = company.CompanyName,
Id = company.Id,
//list of offices
offices = company.TblOffices
.Select(office => new Office
{
Name = office.Description,
NumberOfEmployees = 30,
//solution 1: error: Processing of the LINQ expression [..] by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core
//ordersPerDate = _context.TblOffices
// .Where(off => off.Id == office.Id)
// .Join(orderDetails, of => of.Id, od => od.OfficeId, (of, od) => new Order
// {
// OfficeId = od.OfficeId,
// Date = od.Date,
// Size = new Size(), // to be filled in
// Details = new List<OrderDetail>() // to be filled in
// }).ToList()
//solution 2: error: Object reference not set to an instance of an object
//ordersPerDate = orderDetails.ToList().Where(od => od.OfficeId == office.Id).Select(output => new Order
//{
//OfficeId = output.OfficeId,
//Date = output.Date,
//Size = new Size(), // to be filled in
//Details = new List<OrderDetail>() // to be filled in
//}).ToList()
//solution 3: System.Private.CoreLib: Exception while executing function: Test. System.Linq.Expressions: When called from 'VisitLambda', rewriting a node of type 'System.Linq.Expressions.ParameterExpression' must return a non-null value of the same type. Alternatively, override 'VisitLambda' and change it to not visit children of this type.
ordersPerDate = ( orderDetails.Where(od => od.OfficeId == office.Id) == null ) ? new List<Order> () : orderDetails.Where(od => od.OfficeId == office.Id).ToList()
//maybe use an empty list and fill it later (but how?)
//ordersPerDate = new List<Order>()
}).ToList()
}).ToList();
How can I solve this problem?
edit
fixed it by creating an empty orderlist in the companyData projection:
ordersPerDate = new List<Order>()
and do this after the LINQ query:
foreach (var company in companyData)
{
foreach (var c in company.offices)
c.ordersPerDate = orderDetails.Where(x => x.OfficeId == c.Id).ToList();
}
But I would like to know if this is possible in the same query.
edit2
I'm working with EF Core 3. When I use this line in the "var companyData" query to add the orders:
ordersPerDate = orderDetails.Where(od => od.OfficeId == office.Id).ToList()
I get this error: "System.Private.CoreLib: Exception while executing function: Test. Core Microsoft SqlClient Data Provider: Column 'TblPurchaseOrderDetails.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
When I switch to EF Core 5 the errormessage changes to: "System.InvalidOperationException: 'Unable to translate collection subquery in projection since it uses 'Distinct' or 'Group By' operations and doesn't project key columns of all of it's tables which are required to generate results on client side. Missing column: t.Id. Either add column(s) to the projection or rewrite query to not use 'GroupBy'/'Distinct' operation.'"
