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);

                }

            }

        }

    }

}

This entry was posted in 7671, 7674, 7675. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>