How to split your EDM?

One of my customers wants to code an ERP. To make it, he wants to use EF. His DB has more than 600 tables with a lot of relationships between all of them. The problem is the fact that Entity Framework has some difficulties with big models. You should look at Srikanth Mandadi two posts about it (working-with-large-models-in-entity-framework-part-1.aspx et working-with-large-models-in-entity-framework-part-2.aspx).


If you have a big model, the best is to split it in multiple EDM.


An ERP is composed of several modules. The idea is to make one model per module. The problem is the fact that an entity can be useful in several modules. For example, with Northwind (I know it isn’t a big DB, comment joke are useless [:)]), a product can be used:


  • by a stock management module

image


  • by a supplier module

image


  • by an order module

image


So, in theory, it implies that we shouldn’t be able to split Category (Stocks module), Product (Stocks, Suppliers and Orders modules), Supplier (Suppliers module), OrderDetail (Orders module), Order (Orders module) and Customer (Orders module).


We will add a fourth EDM used by an employee management module. This EDM will contain an entity Employee, Order and OrderDetail.


image


So, how to work with four EDM?


First point, we will make these four EDM. To reduce the conflict risk, we will consider that an entity can be modified by only one module. For this, we will set all the properties except the entity key properties private in other EDM.


It is also possible to “reduce” the entity types. For example, in Supplier module, we don’t need the stock management information.


Ok nice! [:)]


So the problem now is when we want to do some queries relative to several modules.


At first, we will get the best supplier per category.


The supplier and the category entities aren’t in the same model. If this query is called often, it’s better to add Category entity in Suppliers module EDM (or the opposite). Then it will be possible to map it on the real table or on an SSDL view.


If the query speed isn’t extremely important and we can do two SQL queries instead of only one, we can use the following way.


The first idea is, for each “double” entity, to implement an interface which has only the properties of the entity key:


public interface IProduct

{

    int ProductID { get; }

}

 


public interface IOrder

{

    int OrderID { get; }

}

 


public interface IOrderDetail

{

    int ProductID { get; }

    int OrderID { get; }

}


Then we can use methods to get all the entity relationships:


public static class Product

{

    public static Category GetCategory(this IProduct product)

    {

        return GetCategory(product.ProductID);

    }

    public static Category GetCategory(int productID)

    {

        using (var context = new StocksEntities())

        {

            var p = new Stocks.Product { ProductID = productID };

            context.AttachTo(“Products”, p);

            p.CategoryReference.Load();

            return p.Category;

        }

    }

 

    public static Supplier GetSupplier(this IProduct product)

    {

        return GetSupplier(product.ProductID);

    }

    public static Supplier GetSupplier(int productID)

    {

        using (var context = new SuppliersEntities())

        {

            var p = new Suppliers.Product { ProductID = productID };

            context.AttachTo(“Products”, p);

            p.SupplierReference.Load();

            return p.Supplier;

        }

    }

 

    public static IEnumerable<IOrderDetail> GetOrderDetails(this IProduct product)

    {

        return GetOrderDetails(product.ProductID);

    }

    public static IEnumerable<IOrderDetail> GetOrderDetails(int productID)

    {

        using (var context = new OrdersEntities())

        {

            var p = new Orders.Product { ProductID = productID };

            context.AttachTo(“Products”, p);

            p.OrderDetails.Load();

            return p.OrderDetails.AsEnumerable().OfType<IOrderDetail>();

        }

    }

}

 


public static class Order

{

    public static Employee GetEmployee(this IOrder o)

    {

        return GetEmployee(o.OrderID);

    }

    public static Employee GetEmployee(int orderID)

    {

        using (var context = new EmployeesEntities())

        {

            var o = new Employees.Order { OrderID = orderID };

            context.AttachTo(“Orders”, o);

            o.EmployeeReference.Load();

            return o.Employee;

        }

    }

 

    public static Customer GetCustomer(this IOrder o)

    {

        return GetCustomer(o.OrderID);

    }

    public static Customer GetCustomer(int orderID)

    {

        using (var context = new OrdersEntities())

        {

            var o = new Orders.Order { OrderID = orderID };

            context.AttachTo(“Orders”, o);

            o.CustomerReference.Load();

            return o.Customer;

        }

    }

}

 


public static class OrderDetail

{

    public static IProduct GetProduct(this IOrderDetail od)

    {

        return GetProduct(od.OrderID, od.ProductID);

    }

    public static IProduct GetProduct(int orderID, int productID)

    {

        using (var context = new OrdersEntities())

        {

            var od = new Orders.OrderDetail { OrderID = orderID, ProductID = productID };

            context.AttachTo(“OrderDetails”, od);

            od.ProductReference.Load();

            return od.Product;

        }

    }

 

    public static Category GetCategory(this IOrderDetail od)

    {

        return Product.GetCategory(od.ProductID);

    }

 

    public static Employee GetEmployee(this IOrderDetail od)

    {

        return Order.GetEmployee(od.OrderID);

    }

 

    public static Customer GetCustomer(this IOrderDetail od)

    {

        return Order.GetCustomer(od.OrderID);

    }

}


Here, we can write the query like this:


using (var stockContext = new StocksEntities())

{

    var bestSupplierPerCategory = from c in stockContext.Categories.Include(“Products”).AsEnumerable()

                                  let productSuppliers = from p in c.Products

                                                         select p.GetSupplier()

                                  let suppliers = from s in productSuppliers.Distinct()

                                                  orderby productSuppliers.Count(su => su.SupplierID == s.SupplierID) descending

                                                  select s

                                  select new { Category = c, Supplier = suppliers.FirstOrDefault() };

}


However, it isn’t great because the most part of the query is a LINQ To Objects query. We can do better than this.


To illustrate it, we will take a more complex case: get the best employee per category.


To optimize the query, it’s better not to do the group by with LINQ To Object (getting the category products OrderDetails, then OrderDetail Employee). So we won’t use the extension methods defined previously. Instead of these, we will use two contexts and we will mix them in a LINQ query like this:


using (var stockContext = new StocksEntities())

{

    using (var employeesContext = new EmployeesEntities())

    {

        var bestEmployeePerCategory =

            from c in

                (from c in stockContext.Categories

                 select new

                 {

                     Category = c,

                     ProductsID = (from p in c.Products

                                   select p.ProductID)

                 }).AsEnumerable()

            let bestEmployee = (from od in employeesContext.OrderDetails.Where(BuildContainsExpression<Entities.Employees.OrderDetail, int>(od2 => od2.ProductID, c.ProductsID))

                                let e = od.Order.Employee

                                group od by new { e.EmployeeID, e.LastName, e.FirstName } into g

                                let sold = g.Sum(od => (double)od.UnitPrice * (double)od.Quantity * (1D – (double)od.Discount))

                                orderby sold descending

                                select new { g.Key.EmployeeID, g.Key.LastName, g.Key.FirstName, Sold = sold }).FirstOrDefault()

            select new { Category = c.Category, Employee = bestEmployee };

    }

}


You can find the DB generation script, the project and the unit tests here.

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

3 Responses to How to split your EDM?

  1. hot you handle co currencies here as like suppose there 1000 user tables and suppose there is users table same time we are adding users we are modifying existing users and we are display user data.

    How you manage all this

  2. Alejandro says:

    What about the performance in these cases?

  3. Smithc493 says:

    Im genuinely enjoying the style and layout of one’s website. Its a very uncomplicated on the eyes which makes it a great deal much more enjoyable for me to come here and pay a visit to far more typically. Did you hire out a designer to make your theme? Excellent perform! ddgfdbfedccgegec

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>