EDM mapping: How to use Horizontal Entity Splitting
One of the reasons why I love so much Entity Framework is the mapping.
Many developers ignore how powerful EF mapping is.
I just remind them that I realized some videos on it.
Some scenarios are often very useful but some are less frequent. Horizontal Entity Splitting is one of these. However, I will try to demonstrate in this post that it can be useful in some cases.
Imagine Northwind: Products, Orders, OrderDetails. Orders and Products table have an identity primary key. OrderDetails PK is the couple ProductID, OrderID.
Now imagine that to have traceability I decided to implement logical deletion instead of real deletion.
In Orders and Products tables, I just have to add a not nullable bit column IsDeleted.
In my model, I add a condition IsDeleted = false to automatically filter my entities.
Now the problem is for OrderDetails. Indeed, the couple OrderID, ProductID now has to be unique… only when IsDeleted is false.
For this, I add a new not nullable uniqueidentifier colum OrderID with default value to NewId() and I now define the PK on it.
Then, I add an unicity constraint on my old pk using an unique key index:
Then, I use the table creation script to create a new table DeletedOrderDetails without constraint (except foreign keys):
CREATE TABLE [dbo].[DeletedOrderDetails](
[OrderDetailID] [uniqueidentifier] NOT NULL,
[OrderID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[Quantity] [smallint] NOT NULL,
[Discount] [real] NOT NULL,
CONSTRAINT [PK_DeletedOrderDetails] PRIMARY KEY CLUSTERED
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[DeletedOrderDetails] WITH NOCHECK ADD CONSTRAINT [FK_DeletedOrderDetails_Orders] FOREIGN KEY([OrderID])
REFERENCES [dbo].[Orders] ([OrderID])
ALTER TABLE [dbo].[DeletedOrderDetails] CHECK CONSTRAINT [FK_DeletedOrderDetails_Orders]
ALTER TABLE [dbo].[DeletedOrderDetails] WITH NOCHECK ADD CONSTRAINT [FK_DeletedOrderDetails_Products] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Products] ([ProductID])
ALTER TABLE [dbo].[DeletedOrderDetails] CHECK CONSTRAINT [FK_DeletedOrderDetails_Products]
Then, I update my edmx from database to change my OrderDetail entity type, the [Order Details] table in the SSDL and the MSL between them.
Next, I define my OrderDetail entity type key only OrderDetailID.
Now, because I have different default values on my database for OrderDetailID different for each row, I can set StoreGeneratedPattern metadata to Identity for this property and this column in order to let the database generates it (note that only the StoreGeneratedPattern on SSDL is used for this case). I can do it with the designer for the property but only by modifying myself the xml for the column myself.
What is really cool with this is the fact that I don’t have to change anything on my code. // except Delete methods.
Now, I will allow deletion.
For this, I create a new edmx NorthwindDeletion from DB with my four tables.
I want this entity model:
For my entities, it isn’t my problem if there are some DB constraints. I want a very easy way to delete or restore an entity and having a boolean property is the best way I found.
To have this model, after creating my edmx, I delete DeletedOrderDetail entity type without deleting the table in the SSDL.
Thus, after renaming entity types and mapping OrderDetail entity type on the two OrderDetail tables, I have three entities: ProductDeletion mapped on Products, OrderDeletion mapped on Orders and OrderDetailDeletion mapped on [Order Details] AND DeletedOrderDetails.
Then I add a new not nullable bool property IsDeleted on OrderDetailDeletion entity type.
Now I have to use OrderDetailDeletion.IsDeleted property to identify the table. It is what we name Horizontal Entity Splitting. Sadly, it isn’t supported by the VS EDM designer yet. So I have to modify the xml myself:
And here we are. If I set OrderDetailDeletion.IsDeleted to true, it will imply a Delete From [Order Details] and an insert on DeletedOrderDetails (and reversely if OrderDetailDeletion.IsDeleted is set to false) when I will call the context SaveChanges method.
Pretty cool, isn’t it? For my part, I think that EF rocks!