“Table Splitting”: Mapping multiple entity types to the same table.
Imagine that you have a table called “Products” which contains a number of columns holding a large amount of data. For example, images of the product from above, front, and side. For most operations against instances of Products, you do not wish to pull down these large columns.
Or, imagine that you would like to split a table such that some clients get a constrained view of the table, while more sophisticated clients get additional information, such as auditing and other internal fields.
There are several ways with which to accomplish this in the Entity Framework, but one of the more flexible and powerful approaches is not much discussed. In this blog post, I will provide a soup-to-nuts example, from DDL to client code, of how to implement this pattern.
Step 1: Create the Sample Database
We’ll start with an example table, although any table will do:
CREATE TABLE [dbo].[Products](
[id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](150) NOT NULL,
[MSRP] [money] NOT NULL,
[FrontImage] [image] NOT NULL,
[TopImage] [image] NOT NULL,
[SideImage] [image] NOT NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ENDI created this table in a database schema called “TableSplitting”
Step 2: Create the Entity Data Model
I created a new command-line project and added an Entity Data Model file to it called “TableSplitting.edmx”. I used the wizard to reverse engineer the database containing the Products table defined above, which resulted in this model:
Step 3: Modify the Model
1. Rename “Products” to “Product”.
2. Copy and paste the “Product” type and rename the copied type “ProductImages”.
3. Delete “FrontImage”, “TopImage” and “SideImage” from “Product”.
4. Delete “Name” and “MSRP” from “ProductImages”.
5. Add a 1:1 association between “Product” and “ProductImages”.
The resulting model should look like this:
Step 4: Map the Model
The mappings for the ProductImages type should look like this:
The mappings for the association should look like this:
Step 5: Introduce a Referential Constraint
At this point we have one remaining problem: If you build the project, you will get the following error:
Error 3021: Problem in Mapping Fragment starting at line 72: Each of the following columns in table Products is mapped to multiple conceptual side properties: Products.id is mapped to <ProductsProductImages.ProductImages.id, ProductsProductImages.Products.id>
Fixing this duplicate mapping issue requires a referential constraint, which the designer will only support in the next release, so save the edmx file, close it, then right-click it in Solution Explorer, select “Open With…” and double click on “XML Editor”.
In the CSDL section, you will see the ProductProductImages association:
<!-- CSDL content -->
…
<Association Name="ProductProductImages">
<End Type="TableSplittingModel.Product" Role="Product" Multiplicity="1" />
<End Type="TableSplittingModel.ProductImages" Role="ProductImages" Multiplicity="1" />
</Association>
We add the referential constraint to it to inform the model that the ids of these two types are tied to each other:
<Association Name="ProductProductImages">
<End Type="TableSplittingModel.Product" Role="Product" Multiplicity="1" />
<End Type="TableSplittingModel.ProductImages" Role="ProductImages" Multiplicity="1" />
<ReferentialConstraint>
<Principal Role="Product"><PropertyRef Name="id"/></Principal>
<Dependent Role="ProductImages"><PropertyRef Name="id"/></Dependent>
</ReferentialConstraint>
</Association>At this point, the model should validate when you build your project.
Step 6: Test the Model
Finally, we’ll write some code that will create a product and its images, then pull the product back and lazily load its images. The acquisition of test images for the front, side, and top is left as an exercise for the user.
static void Main(string[] args)
{
Product product = new Product() {
Name = "Split Entity Soup",
MSRP = 1337.42M,
};
ProductImages productImages = new ProductImages();
product.ProductImages = productImages;
productImages.FrontImage = File.ReadAllBytes(@"C:\front.jpg");
productImages.SideImage = File.ReadAllBytes(@"C:\side.jpg");
productImages.TopImage = File.ReadAllBytes(@"C:\top.jpg");
//Save a product with its images
using (TableSplittingEntities context = new TableSplittingEntities()) {
//Adding the product also implicitly adds the product's images object.
context.AddToProductSet(product);
context.SaveChanges();
Console.Out.WriteLine("Saved product {0}.", product.id);
//Note that productImages.id is the same as product.id. This is why we love the Entity Framework.
Console.Out.WriteLine("Saved product images {0}.\n", productImages.id);
}
//Next, the product, update it, then load its images
using (TableSplittingEntities context = new TableSplittingEntities()) {
//Query the product back from the database
product = (from p in context.ProductSet
where p.id == product.id
select p).FirstOrDefault();
//Note that product.ProductImages is null, since we did not include it in the query
Console.Out.WriteLine("Retrieved product {0} with product images '{1}'.", product.id, product.ProductImages);
//We can now lazily load the product's images
product.ProductImagesReference.Load();
Console.Out.WriteLine("Retrieved product images for product {0}.", product.ProductImages.id);
Console.Out.WriteLine("Retrieved product front product image contains {0} bytes.", product.ProductImages.FrontImage.Length);
Console.Out.WriteLine("Retrieved product side product image contains {0} bytes.", product.ProductImages.SideImage.Length);
Console.Out.WriteLine("Retrieved product top product image contains {0} bytes.", product.ProductImages.TopImage.Length);
Console.ReadLine();
}
}Running this code will result in output that looks something like this:
Saved product 8.
Saved product images 8.
Retrieved product 8 with product images ''.
Retrieved product images for product 8.
Retrieved product front product image contains 31345 bytes.
Retrieved product front product image contains 45332 bytes.
Retrieved product front product image contains 98761 bytes.
A Note About Optimistic Concurrency
In a timestamp-based optimistic concurrency model, the timestamp column can only be mapped to one of the types. Placing the timestamp on the Product type (and setting its ConcurrencyMode to “fixed”) means that if you alter an instance of ProductImage and call SaveChanges() and then alter Product and call SaveChanges() again, you will get an optimistic concurrency exception. This is not a “strong” guarantee that the two types are written to the database as a unit, but can be helpful. If you desire to have optimistic concurrency guarantees against both types, a more careful, manual process is probably necessary, for example, the creation of integer “ProductVersion” and “ProductImagesVersion” columns that are updated manually.
A Note About Possible Issues
There is a bug in the current mapping system that may cause validation errors when using this technique in some situations. We are fixing this issue for the next release. In the meantime, validation issues are most often resolved by reversing the principal and dependent roles in the referential constraint.
We hope you’ve found this post useful and look forward to your feedback.
Thank you,
The Entity Framework Team
