locked
Multiple entities for one table? RRS feed

  • Question

  • I am working with both ado.net data services and the entity framework.  In order to streamline the communication for ado.net data services I need to create multiple entities that point to the same table in the database.  Some entities share the same property. In essence each entity represents a different view into the same table.

    I have created one to one relationships between all the entities and added referential constraints to the associations.  When I compile I get this error:

    Non-primary key column(s) are being mapped in both fragments to different conceptual side properties - data inconsistency is possible because the corresponding conceptual side properties can be independently modified.

    I can see what the error is saying but in my case I don't care.  My objects do not persist in the context for very long and two different entity types will almost never co-exist in the same context.

    How do I get around this?

    The reason I am doing this is so that I can foce an update of only some properties in the table indepent of the other properties.  For example:

    I have a Widget table that has the following properties:

    Id, Name, Description and Sort

    I want to create one entity that will get all properties.  I then want to create a second entity that just has the Id and Sort.  I can then use the second entity to update the sort without affecting the name and description.

    On the client I make a call to get all the widgets.  The user then changes the order of the widgets.  I then want to use the WidgetSort entity to update just the sort and leave the name and description untouched.

    If I have to create two entities that don't share the Sort it messes up how I handle things on the client.  Ado.net data services does not allow you to query using joins.  Id have to make two calls just to get all the attributes of the Widget.

    What is the best way to handle this?

    Thanks!

    Stewart

    Friday, February 27, 2009 3:52 AM

Answers

  • Thanks George,

    I was trying to avoid having to create service operations in the ado.net data services which is the only real way I think I could expose the stored procedure method.  The more I think about it that is probably the best thing to do it.

    However, since I posted this, I have bumped into several other limitations with ado.net data service querying capabilities that have pushed me out of it into straight WCF.  With this approach all my linq queries have moved to the server instead of existing on the client.  Linq to entities gives me projections that I didn't have with Linq to REST.  Now I only need the one Widget entity definition.

    I found a nice work-around for forcing the update of specific fields that is a twist to what is in this article:

    http://msdn.microsoft.com/en-us/magazine/cc700340.aspx

    Basically I just created a couple versions of the SetAllModified extension method mentioned by John Papa in the article.

    His looked like this:
    public static void SetAllModified<T>(this T entity, ObjectContext  context)   
    where T : IEntityWithKey  
    {  
        var stateEntry = context.ObjectStateManager.      GetObjectStateEntry(entity.EntityKey);  
        var propertyNameList = stateEntry.CurrentValues.DataRecordInfo.      FieldMetadata.Select  
          (pn => pn.FieldType.Name);  
        foreach (var propName in propertyNameList)  
            stateEntry.SetModifiedProperty(propName);  
    }  
     

    For mine i switched from an extension method and did this:

            private void SetPositionDataModified(IEntityWithKey entity)  
            {  
                var stateEntry = this.context.ObjectStateManager.GetObjectStateEntry(entity.EntityKey);  
                stateEntry.SetModifiedProperty("ParentId");  
                stateEntry.SetModifiedProperty("ParentLastModified");  
                stateEntry.SetModifiedProperty("Sort");  
            }  
            public void SavePositionData(WidgetPositionData data)  
            {  
                Widget specificData = new Widget  
                {  
                    Id = data.Id,  
                    ParentId = data.ParentId,  
                    ParentLastModified = data.ParentLastModified,  
                    Sort = data.Sort  
                };  
                this.context.AttachTo("WidgetSet", specificData);  
                this.SetPositionDataModified(specificData);  
                this.context.SaveChanges();  
                this.context.Detach(specificData);  
            }  
     

    The widget listed above has far more properties than the 4 listed above.  When you use this approach the entity framework sends an update to the database that only updates the 3 non key values (ParentId, ParentLastModified, and Sort).  It also bypasses the framework from tryng to get the widget out of the database first.

    Works pretty nice for what I need.

    Thanks for the reply!

    Stewart

    Saturday, February 28, 2009 12:55 AM

All replies

  • I suggest you use one Entity 'Widget' that has the properties Id, name, Description and Sort.

    Create a Stored Propcedure 'UpdateWidget' that accepts the Id, and Sort as parameters, and updates the sort given the Id.

    In the Function mapping Tab of the Mapping window, you could map the Entity to this Stored Procedure.

    Now all you have to do is query the Entity 'Widget' the usual way to get all the properties.

    However, for updating the Entity, you could just invoke the 'Update Stored Procedure' on the Entity 'Widget' with the right parameters.


    George Varghese
    Friday, February 27, 2009 9:34 PM
  • Thanks George,

    I was trying to avoid having to create service operations in the ado.net data services which is the only real way I think I could expose the stored procedure method.  The more I think about it that is probably the best thing to do it.

    However, since I posted this, I have bumped into several other limitations with ado.net data service querying capabilities that have pushed me out of it into straight WCF.  With this approach all my linq queries have moved to the server instead of existing on the client.  Linq to entities gives me projections that I didn't have with Linq to REST.  Now I only need the one Widget entity definition.

    I found a nice work-around for forcing the update of specific fields that is a twist to what is in this article:

    http://msdn.microsoft.com/en-us/magazine/cc700340.aspx

    Basically I just created a couple versions of the SetAllModified extension method mentioned by John Papa in the article.

    His looked like this:
    public static void SetAllModified<T>(this T entity, ObjectContext  context)   
    where T : IEntityWithKey  
    {  
        var stateEntry = context.ObjectStateManager.      GetObjectStateEntry(entity.EntityKey);  
        var propertyNameList = stateEntry.CurrentValues.DataRecordInfo.      FieldMetadata.Select  
          (pn => pn.FieldType.Name);  
        foreach (var propName in propertyNameList)  
            stateEntry.SetModifiedProperty(propName);  
    }  
     

    For mine i switched from an extension method and did this:

            private void SetPositionDataModified(IEntityWithKey entity)  
            {  
                var stateEntry = this.context.ObjectStateManager.GetObjectStateEntry(entity.EntityKey);  
                stateEntry.SetModifiedProperty("ParentId");  
                stateEntry.SetModifiedProperty("ParentLastModified");  
                stateEntry.SetModifiedProperty("Sort");  
            }  
            public void SavePositionData(WidgetPositionData data)  
            {  
                Widget specificData = new Widget  
                {  
                    Id = data.Id,  
                    ParentId = data.ParentId,  
                    ParentLastModified = data.ParentLastModified,  
                    Sort = data.Sort  
                };  
                this.context.AttachTo("WidgetSet", specificData);  
                this.SetPositionDataModified(specificData);  
                this.context.SaveChanges();  
                this.context.Detach(specificData);  
            }  
     

    The widget listed above has far more properties than the 4 listed above.  When you use this approach the entity framework sends an update to the database that only updates the 3 non key values (ParentId, ParentLastModified, and Sort).  It also bypasses the framework from tryng to get the widget out of the database first.

    Works pretty nice for what I need.

    Thanks for the reply!

    Stewart

    Saturday, February 28, 2009 12:55 AM