There’s an Entity Framework mapping that I have paid very little attention to because I didn’t realize it was possible. It was possible, just not obvious or easy in VS2008 SP1.  Thanks to Adam Cogan, who asked a question about this feature, I’ve discovered that it gets a lot more usable in EF4.

The mapping is Table Splitting.

You may be familiar with Entity Splitting, where one Entity maps to two different tables.

My classic example of Entity Splitting begins with these two tables in my database:

There is a one to one relationship, a matching primary key (PersonID) and PersonalDetails merely extends the Person table.

In the model I can combine the fields into a single entity by mapping both tables to the entity.

Table Splitting is just the opposite. I want to create two entities from a single table.

Take a look at Noam Ben-Ami’s December 2008 blog post about table splitting in EFv1. The only missing piece was that this requires a constraint that you had to add manually in the XML. That’s no longer the case with EF4 (in teh Beta 1 that I’m using). I’ll walk you through it.

Here’s the AdventureWorksLT SalesOrderHeader table along with the default entity created from it. It has a LOT of fields/properties.

I will create a separate entity just to house all of the monetary data (SubTotal, TaxAmt, Frieght and TotalDue).

Following Noam’s walkthrough, I copy & paste teh entity, then remove the unwanted fields from each entity.

The new entity needs to be mapped. It maps to SalesOrderHeader just like the original entity.

The next step is to create a 1:1 association between the two.

And then map the association. (See how much work the wizard saves you when you are bringing pre-existing relationships in from a database?)

This is the big difference with EF4, the constraint is automatically created for me whereas in v1 you had to build it yourself. Even if you had to build it yourself, it’s a lot easier now with the constraint being in the designer.

Here’s the detail window behind the constraint.

Now the model validates. What about some code?

I wrote a bit of code that grabs just the header, modifies and saves that, then loads a related MoneyDetail and modifies and saves that entity.

                List<SalesOrderHeader> orders = context.SalesOrderHeaders.ToList();
                SalesOrderHeader order = orders[0];
                order.SalesOrderNumber = "JS321";
                context.SaveChanges();
                order.OrderMoneyDetailsReference.Load();
                order.OrderMoneyDetails.TaxAmt=46;
                context.SaveChanges();
  

SQL Profiler 

SQL Profiler shows me the individual database requests.

On the first query, SQL Server queries for just the fields needed for the SalesOrderHeader entities. None of the money fields were requested.

SELECT
1 AS [C1],
[Extent1].[SalesOrderID] AS [SalesOrderID],
[Extent1].[RevisionNumber] AS [RevisionNumber],
[Extent1].[OrderDate] AS [OrderDate],
[Extent1].[DueDate] AS [DueDate],
[Extent1].[ShipDate] AS [ShipDate],
[Extent1].[Status] AS [Status],
[Extent1].[OnlineOrderFlag] AS [OnlineOrderFlag],
[Extent1].[SalesOrderNumber] AS [SalesOrderNumber],
[Extent1].[PurchaseOrderNumber] AS [PurchaseOrderNumber],
[Extent1].[AccountNumber] AS [AccountNumber],
[Extent1].[ShipMethod] AS [ShipMethod],
[Extent1].[CreditCardApprovalCode] AS [CreditCardApprovalCode],
[Extent1].[Comment] AS [Comment],
[Extent1].[rowguid] AS [rowguid],
[Extent1].[ModifiedDate] AS [ModifiedDate],
[Extent1].[BillToAddressID] AS [BillToAddressID],
[Extent1].[ShipToAddressID] AS [ShipToAddressID],
[Extent1].[CustomerID] AS [CustomerID]
FROM [SalesLT].[SalesOrderHeader] AS [Extent1]

Then I get the update

exec sp_executesql N'declare @p int
update [SalesLT].[SalesOrderHeader]
set @p = 0
where ([SalesOrderID] = @0)
select [SalesOrderNumber], [TotalDue]
from [SalesLT].[SalesOrderHeader]
where @@ROWCOUNT > 0 and [SalesOrderID] = @0',N'@0 int',@0=71774

Next I get the query for the other fields, as a result of the Load.

exec sp_executesql N'SELECT 
1 AS [C1], 
[Extent1].[SalesOrderID] AS [SalesOrderID], 
[Extent1].[SubTotal] AS [SubTotal], 
[Extent1].[TaxAmt] AS [TaxAmt], 
[Extent1].[Freight] AS [Freight], 
[Extent1].[TotalDue] AS [TotalDue]
FROM [SalesLT].[SalesOrderHeader] AS [Extent1]
WHERE [Extent1].[SalesOrderID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=71774

and finally, the second update that pushes in the new Tax Amount value.

exec sp_executesql N'update [SalesLT].[SalesOrderHeader]
set [TaxAmt] = @0
where ([SalesOrderID] = @1)
select [SalesOrderNumber], [TotalDue]
from [SalesLT].[SalesOrderHeader]
where @@ROWCOUNT > 0 and [SalesOrderID] = @1',N'@0 decimal(19,4),@1 int',@0=46.0000,@1=71774

So..Table Splitting is a totally viable (and useful) type of mapping that has been around since EF was released with VS2008 SP1, but most people probably thought it wasn’t possible because of the error you would get if you didn’t know to set the constraint.

You’ll still get the error, but setting the constraint is a heck of a lot easier now.

I will find out why the constraint