Delay loading expensive fields

Question

 

I asked this question to Noam in earlier thread but i guess he never responnded so i will try again.

 

I have few expsenive fields on my table that i want to delay loading. To accomplis that I want to map the additional fields to an association. is it possible to split a table to multple entities where the second entiy i can put the expensive column. Will i have to take over the insert, update and delete.

 

Can anyone share an example.

 

 

Zeeshan Hirani

Answer 1

Hi,

I blogged about it here.

Answer 2

 

thanks matt, I will look into that right now.

 

Zeeshan Hirani

Answer 3

 

Matt,

 

I actually did got that far and got the model to validate when i try to insert, it gives me an error that ssdl section does not have any referential constraint. Did u had similar problems. 

According to noam on one of the thread i have to do something like this

 

 You can, in fact, map two types to the same table - you need a 1:1 association between the two types, and you will need to manually add a referential constraint to your model (edit XML) because the current designer does not support this scenario. (The next release will.)

Answer 4

Oh ye of little patience! .

 

I just wrote up a detailed blog post containing instructions for how to do this for the entity framework blog and submitted it for approval, it should be up soon, hopefully.

 

I looked at Matthieu's blog post - his walkthrough and scenario are very similar to mine.

Answer 5

Noam Ben-Ami // MSFT wrote:

I looked at Matthieu's blog post - his walkthrough and scenario are very similar to mine.

 

Cool

 

But zeeshan is right, I have a problem with the Add. I will look at this.

Answer 6

 

Thanks Noam,

 

I really did not meant to rush you! This is happened to me third time when i replied to an existing thread and never heard from you so decided to open up another thread.

 

Apologies if it had caused you any inconvience.

 

Zeeshan Hirani

Answer 7

 

Just to give u a detail error message when i try to insert category based on teh above scenario.

 

var db = new Test2Entities();

var category = new Category

{

CategoryName = "cat1",

CategoryDescription = "description",

CategoryPicture = new CategoryPicture { LargeContent = "content goes here" }

};

db.AddToCategory(category);

db.SaveChanges();

 

The error that i receive is this

 

A dependent property in a ReferentialConstraint is mapped to a storage-generated column. Column: 'CategoryId'.

Answer 8

Ok, here is the XML for the model in the blog entry:

 

<!-- SSDL content -->

 

<Schema Namespace="TableSplittingModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2005" xmlnstore="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl">

<EntityContainer Name="TableSplittingModelStoreContainer">

<EntitySet Name="Products" EntityType="TableSplittingModel.Store.Products" store:Type="Tables" Schema="dbo" />

</EntityContainer>

<EntityType Name="Products">

<Key>

<PropertyRef Name="id" />

</Key>

<Property Name="id" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />

<Property Name="Name" Type="nvarchar" Nullable="false" MaxLength="150" />

<Property Name="MSRP" Type="money" Nullable="false" />

<Property Name="FrontImage" Type="image" Nullable="false" />

<Property Name="TopImage" Type="image" Nullable="false" />

<Property Name="SideImage" Type="image" Nullable="false" />

</EntityType>

</Schema>

Answer 9

 

<!-- CSDL content -->

 

<Schema Namespace="TableSplittingModel" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2006/04/edm">

<EntityContainer Name="TableSplittingEntities">

<EntitySet Name="ProductSet" EntityType="TableSplittingModel.Product" />

<EntitySet Name="ProductImagesSet" EntityType="TableSplittingModel.ProductImages" />

<AssociationSet Name="ProductProductImages" Association="TableSplittingModel.ProductProductImages">

<End Role="Product" EntitySet="ProductSet" />

<End Role="ProductImages" EntitySet="ProductImagesSet" />

</AssociationSet>

</EntityContainer>

<EntityType Name="Product">

<Key>

<PropertyRef Name="id" />

</Key>

<Property Name="id" Type="Int32" Nullable="false" />

<Property Name="Name" Type="String" Nullable="false" MaxLength="150" Unicode="true" FixedLength="false" />

<Property Name="MSRP" Type="Decimal" Nullable="false" Precision="19" Scale="4" />

<NavigationProperty Name="ProductImages" Relationship="TableSplittingModel.ProductProductImages" FromRole="Product" ToRole="ProductImages" />

</EntityType>

<EntityType Name="ProductImages">

<Key>

<PropertyRef Name="id" />

</Key>

<Property Name="id" Type="Int32" Nullable="false" />

<Property Name="FrontImage" Type="Binary" Nullable="false" />

<Property Name="TopImage" Type="Binary" Nullable="false" />

<Property Name="SideImage" Type="Binary" Nullable="false" />

<NavigationProperty Name="Product" Relationship="TableSplittingModel.ProductProductImages" FromRole="ProductImages" ToRole="Product" />

</EntityType>

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

</Schema>

 

Answer 10

<!-- C-S mapping content -->

 

<Mapping Space="C-S" xmlns="urnchemas-microsoft-com:windowstorage:mapping:CS">

<EntityContainerMapping StorageEntityContainer="TableSplittingModelStoreContainer" CdmEntityContainer="TableSplittingEntities">

<EntitySetMapping Name="ProductSet">

<EntityTypeMapping TypeName="IsTypeOf(TableSplittingModel.Product)">

<MappingFragment StoreEntitySet="Products">

<ScalarProperty Name="id" ColumnName="id" />

<ScalarProperty Name="Name" ColumnName="Name" />

<ScalarProperty Name="MSRP" ColumnName="MSRP" />

</MappingFragment>

</EntityTypeMapping>

</EntitySetMapping>

<EntitySetMapping Name="ProductImagesSet">

<EntityTypeMapping TypeName="IsTypeOf(TableSplittingModel.ProductImages)">

<MappingFragment StoreEntitySet="Products">

<ScalarProperty Name="SideImage" ColumnName="SideImage" />

<ScalarProperty Name="TopImage" ColumnName="TopImage" />

<ScalarProperty Name="FrontImage" ColumnName="FrontImage" />

<ScalarProperty Name="id" ColumnName="id" />

</MappingFragment>

</EntityTypeMapping>

</EntitySetMapping>

<AssociationSetMapping Name="ProductProductImages" TypeName="TableSplittingModel.ProductProductImages" StoreEntitySet="Products">

<EndProperty Name="ProductImages">

<ScalarProperty Name="id" ColumnName="id" />

</EndProperty>

<EndProperty Name="Product">

<ScalarProperty Name="id" ColumnName="id" />

</EndProperty>

</AssociationSetMapping>

</EntityContainerMapping>

</Mapping>

Answer 11

Thanks Noam for posting the csdl, msl and ssdl. I have pretty muc similar mapping as you have. However when it comes to inserting, i get an exception which i mentioned aboove. Can you insert?

 

Zeeshan Hirani

 

Answer 12

Yes, I can insert - and my primary key is an identity field...

 

Answer 13

I still cannot insert, wonder if anyone had the same problem. My mappings are as follows.

 <!-- SSDL content -->

<edmxtorageModels>

<Schema Namespace="Test2Model.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008" xmlnstore="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl">

<EntityContainer Name="Test2ModelStoreContainer">

<EntitySet Name="Category" EntityType="Test2Model.Store.Category" store:Type="Tables" Schema="dbo" />

</EntityContainer>

<EntityType Name="Category">

<Key>

<PropertyRef Name="CategoryId" />

</Key>

<Property Name="CategoryId" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />

<Property Name="CategoryName" Type="varchar" Nullable="false" MaxLength="50" />

<Property Name="CategoryDescription" Type="varchar" Nullable="false" MaxLength="50" />

<Property Name="LargeContent" Type="varchar(max)" Nullable="false" />

</EntityType>

</Schema></edmxtorageModels>

Answer 14

 

<!-- CSDL content -->

<edmx:ConceptualModels>

<Schema Namespace="Test2Model" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2006/04/edm">

<EntityContainer Name="Test2Entities">

<EntitySet Name="Category" EntityType="Test2Model.Category" />

<EntitySet Name="CategoryPictureSet" EntityType="Test2Model.CategoryPicture" />

<AssociationSet Name="CategoryCategoryPicture" Association="Test2Model.CategoryCategoryPicture">

<End Role="Category" EntitySet="Category" />

<End Role="CategoryPicture" EntitySet="CategoryPictureSet" /></AssociationSet></EntityContainer>

<EntityType Name="Category">

<Key>

<PropertyRef Name="CategoryId" />

</Key>

<Property Name="CategoryId" Type="Int32" Nullable="false" />

<Property Name="CategoryName" Type="String" Nullable="false" MaxLength="50" Unicode="false" FixedLength="false" />

<Property Name="CategoryDescription" Type="String" MaxLength="50" Unicode="false" FixedLength="false" Nullable="false" />

<NavigationProperty Name="CategoryPicture" Relationship="Test2Model.CategoryCategoryPicture" FromRole="Category" ToRole="CategoryPicture" />

</EntityType>

<EntityType Name="CategoryPicture" >

<Key>

<PropertyRef Name="CategoryId" /></Key>

<Property Name="CategoryId" Type="Int32" Nullable="false" xmlns:a="http://schemas.microsoft.com/ado/2006/04/codegeneration" />

<Property Name="LargeContent" Type="String" Nullable="false" />

<NavigationProperty Name="Category" Relationship="Test2Model.CategoryCategoryPicture" FromRole="CategoryPicture" ToRole="Category" />

</EntityType>

<Association Name="CategoryCategoryPicture">

<End Type="Test2Model.Category" Role="Category" Multiplicity="1" />

<End Type="Test2Model.CategoryPicture" Role="CategoryPicture" Multiplicity="1" />

<ReferentialConstraint>

<Principal Role="Category">

<PropertyRef Name="CategoryId" /></Principal>

<Dependent Role="CategoryPicture">

<PropertyRef Name="CategoryId" /></Dependent></ReferentialConstraint></Association></Schema>

Answer 15

 

<!-- C-S mapping content -->

<edmx:Mappings>

<Mapping Space="C-S" xmlns="urnchemas-microsoft-com:windowstorage:mapping:CS">

<EntityContainerMapping StorageEntityContainer="Test2ModelStoreContainer" CdmEntityContainer="Test2Entities">

<EntitySetMapping Name="Category">

<EntityTypeMapping TypeName="IsTypeOf(Test2Model.Category)">

<MappingFragment StoreEntitySet="Category">

<ScalarProperty Name="CategoryId" ColumnName="CategoryId" />

<ScalarProperty Name="CategoryName" ColumnName="CategoryName" />

<ScalarProperty Name="CategoryDescription" ColumnName="CategoryDescription" />

</MappingFragment>

</EntityTypeMapping>

</EntitySetMapping>

<EntitySetMapping Name="CategoryPictureSet">

<EntityTypeMapping TypeName="IsTypeOf(Test2Model.CategoryPicture)">

<MappingFragment StoreEntitySet="Category">

<ScalarProperty Name="LargeContent" ColumnName="LargeContent" />

<ScalarProperty Name="CategoryId" ColumnName="CategoryId" /></MappingFragment></EntityTypeMapping></EntitySetMapping>

<AssociationSetMapping Name="CategoryCategoryPicture" TypeName="Test2Model.CategoryCategoryPicture" StoreEntitySet="Category">

<EndProperty Name="CategoryPicture">

<ScalarProperty Name="CategoryId" ColumnName="CategoryId" /></EndProperty>

<EndProperty Name="Category">

<ScalarProperty Name="CategoryId" ColumnName="CategoryId" /></EndProperty></AssociationSetMapping></EntityContainerMapping>

</Mapping>

</edmx:Mappings>

 

var db = new Test2Entities();

var category = new Category

{

CategoryName = "cat1",

CategoryDescription = "description",

CategoryPicture = new CategoryPicture { LargeContent = "content goes here" }

};

db.AddToCategory(category);

db.SaveChanges();

 

When I insert a category, I get the following exception.

 

{"A dependent property in a ReferentialConstraint is mapped to a storage-generated column. Column: 'CategoryId'."}

 

I have no clue how to get around it.

 

Zeeshan Hirani

 

 

Answer 16

zeeshan, you should do a mistake becaus I can with Noam edm.

 

Answer 17

 

actually after 3 hours I have finally managed to figure out why my example did not work and why noam example worked although i had exactly the samething. Ok here is the reason.

on the related entity ProductImageSet noam, had three properties in addition to ProductId. Try moving one of the properties back to product entity and it will give the same error that i was getting.

 

The moral of the story is.. there is a bug in entity framework, its forcing me to have ateast 3 properties on the related entity where as all i need is one. If you do not do so you will get a runtime exception.

 

Noam, can you confirm that because i ran your example and it worked fine. When I moved SideImage to Productentity, i was getting the same erorr.

 

Zeeshan Hirani

Answer 18

zeeshan hirani wrote:

The moral of the story is.. there is a bug in entity framework, its forcing me to have ateast 3 properties on the related entity where as all i need is one. If you do not do so you will get a runtime exception.

 

In fact you can have as many properties as you want. But there is effectively a very strange bug and to don't have it, you must have the same properties count in the two entity types.

Answer 19

 

Thanks Mattt,

 

You are right. I have confirmed that fact as well.

 

Hopefully EF will fix the issue in next release.

Zeeshan Hirani

Answer 20

Hey guys,

 

 Sorry for the delay in responding. Just wanted to let you know that the mapping team has reproduced this issue, is investigating this, and will report back when we find the source of these problems. Thanks for your involvement!

 

 Cheers,

   Noam

 

Answer 21

We've tracked down the source of the problem in the product. First a workaround: if you swap the dependent and principal roles in your ReferentialIntegrityConstraint (which makes no difference for this particular association), you can hopefully avoid the conflict.

 

Second, for the curious, an explanation for what's happening: those of you familiar with the EF internals might know we use "update mapping views" to describe the translation of entity modifications to store modifications. In this example, the PK column could be a projection of the either the principal or dependent entity key. If it's the principal entity key, no problem... If it's the dependent entity key, the EF believes that the dependent entity is contributing the (generated) key value to the principal, which is not supported. In this case, the same column is responsible for both the dependent and the principal entity, so the exception really makes no sense.

 

Thanks,

-Colin

Answer 22

Hi guys,
any news regarding this issue? Is this still the valid workaround?
Edited: If its indeed a bug, why there is no related issue in Microsoft Connect?

---

Dimitris Papadimitriou, Software Development Professional

Answer 23

Hi Papadi -

The workaround is still suggested.  There was no connect bug since this was reported on the forums here and fixed by the product team.  The fix will show up in the .net framework 4.0. 

Thanks, 

Mike Kaufman
Microsoft

---

This posting is provided "AS IS" with no warranties, and confers no rights

Answer 24

With EF4 Beta 2 I believe I'm experiencing the same issue. I've setup a FK Association between two entities that is quite basic. The Principal.ID is an identity field and the dependancy is nullable, but otherwise nothing special. When I try to call an update proc I'm getting:

To clarify, pRef2 is a calculated value in the DB and is returned by a view. My Update and Insert procs handle it though and I've setup my models so that it isn't calculated (so the DB contains all the logic) because I encountered other issues with my original design (before using FK Associations) where the model did know it was calculated.

AFTERTHOUGHT: This exception is being thrown by .AddResultColumn according to the stacktrace. I've gone through (for testing purposes) and removed ALL possible hints that pRef2 is a store-generated colum, to include the ResultColumn mapping, THEN I removed the StoreGenneratedPattern property from the ID field (the principle field in the constraint), did a 'Clean' on the Solution, did a 'Rebuild' and I'm STILL getting this exception. Now I can't figure out where in the world this is coming from.

A dependent property in a ReferentialConstraint is mapped to a store-generated column. Column: 'pRef2'.

Store Model:

        <EntityType Name="SubjectTreeNode">
          <Key>
            <PropertyRef Name="ID" />
          </Key>
          <Property Name="ID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
          <Property Name="SubjectTree" Type="int" Nullable="true" />
          <Property Name="pRef" Type="int" Nullable="false" />
          <Property Name="Title" Type="varchar" MaxLength="100" Nullable="false" />
          <Property Name="Description" Type="varchar" MaxLength="250" Nullable="true" />
          <Property Name="OrderWeight" Type="int" Nullable="false" />
          <Property Name="ExpandState" Type="bit" />
          <Property Name="IsTopLevel" Type="int" StoreGeneratedPattern="Computed" />
          <Property Name="pRef2" Type="int" Nullable="true" />
        </EntityType>

          <EntitySet Name="SubjectTreeNode_appSec" EntityType="edmTESTSCORING2.Store.SubjectTreeNode" store:Type="Views" store:Schema="appSec" store:Name="SubjectTreeNode_appSec">
            <DefiningQuery>
              SELECT TOP 100 PERCENT
              STN.[ID], STN.[SubjectTree], STN.[pRef], STN.[Title], STN.[Description], STN.[OrderWeight], STN.[ExpandState], STN.[IsTopLevel], STN.[pRef2]
              FROM
              appSec.SubjectTreeNode as STN
            </DefiningQuery>
          </EntitySet>

ConceptualModel:

<AssociationSet Name="SubjectTreeNode_appSecSubjectTreeNode_appSec" Association="edmTESTSCORING2.SubjectTreeNode_appSecSubjectTreeNode_appSec">
            <End Role="SubjectTreeNode_appSec" EntitySet="SubjectTreeNode_appSecs" />
            <End Role="SubjectTreeNode_appSec1" EntitySet="SubjectTreeNode_appSecs" />
</AssociationSet>

        <EntityType Name="SubjectTreeNode_appSec">
          <Key>
            <PropertyRef Name="ID" />
          </Key>
          <Property Type="Int32" Name="ID" Nullable="false" />
          <Property Type="Int32" Name="pRef" Nullable="false" />
          <Property Type="String" Name="Title" Nullable="false" MaxLength="100" />
          <Property Type="String" Name="Description" Nullable="true" MaxLength="250" />
          <Property Type="Int32" Name="OrderWeight" Nullable="false" />
          <Property Type="Boolean" Name="ExpandState" Nullable="true" />
          <Property Type="Int16" Name="IsTopLevel" Nullable="true" a:StoreGeneratedPattern="Computed" xmlns:a="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" />
          <NavigationProperty Name="SubjectTree_appSec" Relationship="edmTESTSCORING2.SubjectTree_appSecSubjectTreeNode_appSec" FromRole="SubjectTreeNode_appSec" ToRole="SubjectTree_appSec" />
          <NavigationProperty Name="SubjectTreeNodeAssoc_appSec" Relationship="edmTESTSCORING2.SubjectTreeNode_appSecSubjectTreeNodeAssoc_appSec" FromRole="SubjectTreeNode_appSec" ToRole="SubjectTreeNodeAssoc_appSec" />
          <Property Type="Int32" Name="pRef2" Nullable="true" a:StoreGeneratedPattern="None" xmlns:a="http://schemas.microsoft.com/ado/2009/02/edm/annotation" />
          <NavigationProperty Name="SubjectTreeNode_appSec_children" Relationship="edmTESTSCORING2.SubjectTreeNode_appSecSubjectTreeNode_appSec" FromRole="SubjectTreeNode_appSec" ToRole="SubjectTreeNode_appSec1" />
          <NavigationProperty Name="SubjectTreeNode_appSec_parent" Relationship="edmTESTSCORING2.SubjectTreeNode_appSecSubjectTreeNode_appSec" FromRole="SubjectTreeNode_appSec1" ToRole="SubjectTreeNode_appSec" />
          <Property Type="Int16" Name="SubjectTree" Nullable="true" />
          </EntityType>

        <Association Name="SubjectTreeNode_appSecSubjectTreeNode_appSec">
          <End Type="edmTESTSCORING2.SubjectTreeNode_appSec" Role="SubjectTreeNode_appSec" Multiplicity="0..1" />
          <End Type="edmTESTSCORING2.SubjectTreeNode_appSec" Role="SubjectTreeNode_appSec1" Multiplicity="*" />
          <ReferentialConstraint>
            <Principal Role="SubjectTreeNode_appSec">
              <PropertyRef Name="ID" /></Principal>
            <Dependent Role="SubjectTreeNode_appSec1">
              <PropertyRef Name="pRef2" /></Dependent></ReferentialConstraint>
          </Association>

Mappings:

          <EntitySetMapping Name="SubjectTreeNode_appSecs">
            <EntityTypeMapping TypeName="IsTypeOf(edmTESTSCORING2.SubjectTreeNode_appSec)">
              <MappingFragment StoreEntitySet="SubjectTreeNode_appSec">
                <ScalarProperty Name="SubjectTree" ColumnName="SubjectTree" />
                <ScalarProperty Name="pRef2" ColumnName="pRef2" />
                <ScalarProperty Name="IsTopLevel" ColumnName="IsTopLevel" />
                <ScalarProperty Name="ExpandState" ColumnName="ExpandState" />
                <ScalarProperty Name="OrderWeight" ColumnName="OrderWeight" />
                <ScalarProperty Name="Description" ColumnName="Description" />
                <ScalarProperty Name="Title" ColumnName="Title" />
                <ScalarProperty Name="pRef" ColumnName="pRef" />
                <ScalarProperty Name="ID" ColumnName="ID" />
              </MappingFragment>
            </EntityTypeMapping>
            <EntityTypeMapping TypeName="edmTESTSCORING2.SubjectTreeNode_appSec">
              <ModificationFunctionMapping>
                <InsertFunction FunctionName="edmTESTSCORING2.Store.SubjectTreeNode_insert" RowsAffectedParameter="InsertCount">
                  <ScalarProperty Name="pRef2" ParameterName="pRef2" />
                  <ScalarProperty Name="SubjectTree" ParameterName="SubjectTree" />
                  <ScalarProperty Name="ID" ParameterName="ID" />
                  <ScalarProperty Name="ExpandState" ParameterName="ExpandState" />
                  <ScalarProperty Name="OrderWeight" ParameterName="OrderWeight" />
                  <ScalarProperty Name="Description" ParameterName="Description" />
                  <ScalarProperty Name="Title" ParameterName="Title" />
                  <ScalarProperty Name="pRef" ParameterName="pRef" />
                  <ResultBinding Name="ID" ColumnName="ID" />
                  <ResultBinding Name="pRef2" ColumnName="pRef2" /></InsertFunction>
                <UpdateFunction FunctionName="edmTESTSCORING2.Store.SubjectTreeNode_update" RowsAffectedParameter="UpdateCount">
                  <ScalarProperty Name="pRef2" ParameterName="pRef2" Version="Current" />
                  <ScalarProperty Name="ExpandState" ParameterName="ExpandState" Version="Current" />
                  <ScalarProperty Name="OrderWeight" ParameterName="OrderWeight" Version="Current" />
                  <ScalarProperty Name="Description" ParameterName="Description" Version="Current" />
                  <ScalarProperty Name="Title" ParameterName="Title" Version="Current" />
                  <ScalarProperty Name="pRef" ParameterName="pRef" Version="Current" />
                  <ScalarProperty Name="SubjectTree" ParameterName="SubjectTree" Version="Current" />
                  <ScalarProperty Name="ID" ParameterName="ID" Version="Current" />
                  <ResultBinding Name="pRef2" ColumnName="pRef2" /></UpdateFunction>
                <DeleteFunction FunctionName="edmTESTSCORING2.Store.SubjectTreeNode_delete" RowsAffectedParameter="DeleteCount">
                  <ScalarProperty Name="ID" ParameterName="ID" /></DeleteFunction></ModificationFunctionMapping></EntityTypeMapping>
          </EntitySetMapping>