How to split your EDM v2?
After my previous post about it, my customer asks me the following question: how to get a complete graph (with categories, suppliers, products, order details, orders, customers and employees)?
To realize it, we have to add “sort of” navigation properties for the entities like this:
private Supplier _supplier;
/// <remarks>
/// Changes aren't saved
/// </remarks>
public Supplier Supplier
{
get
{
if (_supplier == null)
_supplier = this.GetSupplier();
return _supplier;
}
set { _supplier = value; }
}
private IEnumerable<Orders.OrderDetail> _orderDetails;
/// <remarks>
/// Changes aren't saved
/// </remarks>
public IEnumerable<Orders.OrderDetail> OrderDetails
{
get
{
if (_orderDetails == null)
_orderDetails = this.GetOrderDetails();
return _orderDetails;
}
set { _orderDetails = value; }
}
Then, the idea is to set these informations at first.
For this, we can do the following:
using (var stockContext = new StocksEntities())
{
var categories = stockContext.Categories.Include("Products").ToList();
foreach (var p in stockContext.ObjectStateManager.GetObjectStateEntries(EntityState.Unchanged).Select(e => e.Entity).OfType<Entities.Stocks.Product>())
{
using (var supplierContext = new SuppliersEntities())
{
p.Supplier = (from p2 in supplierContext.Products
where p2.ProductID == p.ProductID
select p2.Supplier).FirstOrDefault();
}
using (var orderContext = new OrdersEntities())
{
p.OrderDetails = (from od in orderContext.OrderDetails.Include("Order.Customer")
where od.ProductID == p.ProductID
select od).ToList();
foreach (var o in orderContext.ObjectStateManager.GetObjectStateEntries(EntityState.Unchanged).Select(e => e.Entity).OfType<Entities.Orders.Order>())
{
using (var employeeContext = new EmployeesEntities())
{
o.Employee = (from oe in employeeContext.Orders
where oe.OrderID == o.OrderID
select oe.Employee).FirstOrDefault();
}
}
}
}
}
However, this way can generate a lot of SQL queries and so the execution isn’t very fast. If we want to get only part of the categories (with all their graphs), we can use this code:
using (var stockContext = new StocksEntities())
{
using (var supplierContext = new SuppliersEntities())
{
using (var orderContext = new OrdersEntities())
{
using (var employeeContext = new EmployeesEntities())
{
var categories = stockContext.Categories.Include("Products").ToList();
var products = categories.SelectMany(c => c.Products);
var suppliers = supplierContext.Suppliers.Where(BuildContainsExpression<Supplier, int>(s => s.SupplierID, (from p in products
where p.SupplierID.HasValue
select p.SupplierID.Value).Distinct())).ToList();
var orderDetails = orderContext.OrderDetails.Include("Order.Customer").Where(BuildContainsExpression<Entities.Orders.OrderDetail, int>(od => od.ProductID, products.Select(p => p.ProductID))).ToList();
var orders = orderDetails.Select(od => od.Order);
var employees = employeeContext.Employees.Where(BuildContainsExpression<Employee, int>(e => e.
EmployeeID, (from o in orders
where o.EmployeeID.HasValue
select o.EmployeeID.Value).Distinct())).ToList();
foreach (var p in products)
{
p.Supplier = suppliers.FirstOrDefault(s => s.SupplierID == p.SupplierID);
p.OrderDetails = (from od in orderDetails
where od.ProductID == p.ProductID
select od);
foreach (var o in orders)
o.Employee = employees.FirstOrDefault(e => o.EmployeeID == e.EmployeeID);
}
}
}
}
}
If we want to get all entities, we can directly use the following code:
using (var stockContext = new StocksEntities())
{
using (var supplierContext = new SuppliersEntities())
{
using (var orderContext = new OrdersEntities())
{
using (var employeeContext = new EmployeesEntities())
{
var categories = stockContext.Categories.Include("Products").ToList();
var products = categories.SelectMany(c => c.Products);
var suppliers = supplierContext.Suppliers.ToList();
var orderDetails = orderContext.OrderDetails.Include("Order.Customer").ToList();
var orders = orderDetails.Select(od => od.Order);
var employees = employeeContext.Employees.ToList();
foreach (var p in products)
{
p.Supplier = suppliers.FirstOrDefault(s => s.SupplierID == p.SupplierID);
p.OrderDetails = (from od in orderDetails
where od.ProductID == p.ProductID
select od);
foreach (var o in orders)
o.Employee = employees.FirstOrDefault(e => o.EmployeeID == e.EmployeeID);
}
}
}
}
}