Partager via

“Table Splitting”: Mapping multiple entity types to the same table.

  • Article

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]
END

I 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:

blog1

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:

blog2

Step 4: Map the Model

The mappings for the ProductImages type should look like this:

blog3

The mappings for the association should look like this:

blog4

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

Comments

  • Anonymous
    December 06, 2008
    The solution I always choose with LINQ to SQL is to create a value object in code and map the properties of that object back to the entity and hide the properties by making them internal. In the given example I create an class called ProductImages in code, create a property returning a 'ProductImages' in the Product class and set the FrontImage, SideImage and TopImage as 'internal' in the designer. I suppose this could also work with Entity Framework. Here's a simple implementation: public partial class Product { private ProductImages images; public ProductImages Images { get { return this.images ?? this.images = new ProductImages(this); } } } public class ProductImages { private readonly Product product; internal ProductImages(Product product) { this.product = product; } public Binary FrontImage { get { return this.product.FrontImage; } } public Binary SideImage { get { return this.product.SideImage; } } public Binary TopImage { get { return this.product.SideImage; } } }

  • Anonymous
    December 06, 2008
    In step 5 and maybe before it. What if I made a refresh or update schema using EF designer, does all my changes disappear?

  • Anonymous
    December 08, 2008
    Is it possible to have the same field in both entities? In your example, I would like to have Name both in Product and in ProductImages.

  • Anonymous
    December 09, 2008
    In step 3.4 you mention "Product" again, where this should be "ProductImages"

  • Anonymous
    December 09, 2008

  1. Running update model from database against this model should work fine.
  2. You cannot define Name on both entities, bit it is a trivial exercise to provide Name on ProductImages' partial class (although yes, you will need to load the Product reference for this to work.)
  3. Making the properties internal does not help the situation since EF does not support delay loading of properties. The intent with this approach is to "simulate" delay loading so that expensive properties are not loaded when it is not necessary.

  • Anonymous
    December 09, 2008
    Thanks for the correction Dennis, I've asked the blog owner to make the fix.

  • Anonymous
    January 05, 2009
    You mention that the designer will support referential constraints "in the next release". Is this Visual Studio 2010?

  • Anonymous
    January 20, 2009
    I have a problem. "error 3033: Problem in Mapping Fragment. EntitySets 'EntitySet1' and 'EntitySet2' are both mapped to table 'Table1'. Their Primary Keys may collide" In my case "Large Data" field (like TopImage) is optional. EntitySet2 was joined to EntitySet2 by 0..1 multiplicity association. Please, help.

  • Anonymous
    January 31, 2009
    @eLVik: You should look at what I did here (http://msmvps.com/blogs/matthieu/archive/2008/11/14/how-to-simulate-a-1-to-0-1-relationship-when-you-must-have-a-1-to-1.aspx).

  • Anonymous
    February 01, 2009
    The comment has been removed

  • Anonymous
    April 07, 2009
    ナースの卒業試験は開脚台で股を開き、パイパンマンコにドクターのごっつい注射を注入され空っぽになるまで受けれたら合格です。

  • Anonymous
    April 07, 2009
    ナースの卒業試験は開脚台で股を開き、パイパンマンコにドクターのごっつい注射を注入され空っぽになるまで受けれたら合格です。http://www.kore114.net/

  • Anonymous
    April 10, 2009
    文字化け対策のコード

ページの文字エンコーディングを揃えても文字化けするのなら使うべし

  • Anonymous
    May 16, 2009
    文字化け対策のコード

ページの文字エンコーディングを揃えても文字化けするのなら使うべし

  • Anonymous
    May 17, 2009
    Sunday, May 17, 2009 4:34 AM by nameee  

  • Anonymous
    May 17, 2009
    寂しくて　寂しくて　言葉で表せないぐらい寂しいです。彼氏が欲しいイです。誰が手伝ってくれるが。よろしく　おねがいします。連絡してくださいね、待っていますよ

  • Anonymous
    May 17, 2009
    寂しくて　寂しくて　言葉で表せないぐらい寂しいです。彼氏が欲しいイです。誰が手伝ってくれるが。よろしく　おねがいします。連絡してくださいね、待っていますよ。

  • Anonymous
    May 17, 2009
    寂しくて　寂しくて　言葉で表せないぐらい寂しいです。彼氏が欲しいイです。誰が手伝ってくれるが。よろしく　おねがいします。連絡してくださいね、待っていますよ。

  • Anonymous
    May 21, 2009

  1. Running update model from database against this model should work fine.
  2. You cannot define Name on both entities, bit it is a trivial exercise to provide Name on ProductImages' partial class (although yes, you will need to load the Product reference for this to work.)
  3. Making the properties internal does not help the situation since EF does not support delay loading of properties. The intent with this approach is to "simulate" delay loading so that expensive properties are not loaded when it is not necessary.

  • Anonymous
    June 10, 2009
    寂しくて　寂しくて　言葉で表せないぐらい寂しいです。彼氏が欲しいイです。誰が手伝ってくれるが。よろしく　おねがいします。連絡してくださいね、待っていますよ。

  • Anonymous
    July 19, 2009
    Is it possible to have the same field in both entities? In your example, I would like to have Name both in Product and in ProductImages.

  • Anonymous
    August 05, 2009
    Has anyone run across this error?  I get an instance of the error for each relationship between WorkOrder and another table in the database via foreign keys. Error 3019: Problem in Mapping Fragments starting at lines 5423, 6488: Incorrect mapping of composite key columns. Foreign key constraint 'fk_Address_WorkOrderKey' from table Address (WorkOrderKey) to table WorkOrder (Key): Columns (WorkOrderKey) in table Address are mapped to properties (Key) in fk_Address_WorkOrderKey and columns (Key) in table WorkOrder are mapped to properties (Key) in WorkOrderWorkOrderDetail. The order of the columns through the mappings is not preserved.

  • Anonymous
    August 19, 2009
    Error 3019: Problem in Mapping Fragments starting at lines 5423, 6488: Incorrect mapping of composite key columns. Foreign key constraint

  • Anonymous
    August 26, 2009
    @Brian: I'm seeing exactly the same thing - I think it's caused by another relationship with the table we've split. Details and (hopefully soon) a response from the wonderous StackOverflow: http://stackoverflow.com/questions/1337730/delay-loading-expensive-fields-in-entity-framework-v-1

  • Anonymous
    November 16, 2009
    @EF Team Thanks! Exactly what I've been searching for. :-)

  • Anonymous
    January 25, 2010
    Wanted to update this to reflect that the EF4 beta has added the "Referential Constraint" field in the Properties for the 1:1 association.  So you can specify it there and not have to delve into the XML. Loving EF4 -- way to go ADO.NET team.

  • Anonymous
    February 15, 2010
    Thank you for the solution. It seems that for some reason the EF is not creating such Referential Constraints. What we noticed is that if we use the same database on SQL 2005 it will create the references properly, but it does not work if you use SQL 2008. Thanks, Felix

  • Anonymous
    April 21, 2010
    Hi, Does EF4 fix this association issue? Thanks,

  • Anonymous
    July 01, 2010
    The comment has been removed