How to solve a big EDM performance problem?
Last week, a customer asked me how to solve a big EDM performance problem? In his case, his model was mapped on around 700 tables. I am amused and happy to see that I proposed what Srikanth proposed yesterday:
- Do distinct model for unlinked tables
- Break the relation in the EDM
Go back on this last point. Imagine you have a Category module and a Product module (using Northwind). It is possible to have one EDM per module. So we will have an int CategoryID property on Product entity type.
If you want to add or modify a product, you will probably use a combobox (or another selection control) to choose the category. This scenario isn’t a problem. Indeed, you just need to set combo.DataSource with the categories, the ValueMember with CategoryID and the DisplayMember with CategoryName.
Now what happens if you want to create a form which creates a category and a product together? No problem: you will add the category with the category module which will return the CategoryID and then we will add the new product with the CategoryID. Now we miss something: the transaction. Indeed, when you do a SaveChanges, a transaction is automatically created. In this case, there are two different SaveChanges. If you want to cancel the category creation if the product creation failed, we can use a transactionscope around the two creations.
In my opinion, I find absurd to have so many entity types in an EDM. Indeed, it will be very difficult to maintain it because of its size. Moreover, conceptually, we often use module architecture so it makes sense to do the same with EDM.
A last point: it is possible to have entity types mapped on the same table and the same columns in two different EDM. Imagine that you want to be able to get the categories which have any products in the category module. You can use an SSDL View (which can or cannot be a view in your DB) but you can also integrate the product entity type in your category EDM. If you only want to use it to get the categories with products, it is possible to define the product table with only two columns (in your SSDL): ProductID and CategoryID. Doing this reduces the flow with your DB to get only what you really need. However, be careful as doing this generates an important constraint: in DB, ProductName doesn’t allow null value. This means that if you try to add a product in the category EDM, you will get an exception on the SaveChanges. But this can be an advantage because you will impose to use the product EDM which is done for this.