none
How to exclude columns from UPDATE in Entity Framework 4? RRS feed

  • Question

  • I have CreatedDate and CreatedBy columns in my table. When I tried to do an update, I will have to supply these values, otherwise EF throws exception. EF includes those two columns in the generated UPDATE query.

    These two columns should never be changed once created. So the best way to handle this is to exclude these columns from update. How can I tell EF to do that?

    Thank you.

    Thursday, May 27, 2010 11:43 PM

Answers

  • Hi,

     

    Welcome to EF forum!

     

    Are these two columns included in primary key?   Or do you set the Concurrency Mode on the CreateDate and CreateBy properties?  If so, EF will make the concurrency check on these two columns every time we try to update the data.   If not, I don’t think these two columns are necessary when we try to update the data, even they are not nullable.   Here are some sample codes to update the entity:

    ==================================================================================

                    var entity = new MyEntity { ID = 1, Name = "New Name" };

                    context.AttachTo("TestDBEntities.MyEntity", entity);

                    var entry = context.ObjectStateManager.GetObjectStateEntry(entity);

                    entry.SetModifiedProperty("Name");

                    context.SaveChanges();

    ==================================================================================

    Here I used the SetModifiedProperty to which properties need to be updated.   The generated SQL won’t contain other columns except the Name and ID (PK).  

    ==================================================================================

    exec sp_executesql N'update [dbo].[MyEntity]

    set [Name] = @0

    where ([ID] = @1)

    ',N'@0 nvarchar(8),@1 int',@0=N'New Name',@1=1

    ==================================================================================

     

    Besides, to exclude them in the updated statements, we can also map the CUD stored procedures on the entity.   For detail, please see

    http://blogs.msdn.com/b/adonet/archive/2007/09/14/how-to-map-stored-procedures-using-the-ado-net-entity-framework.aspx

    http://blogs.msdn.com/b/adonet/archive/2008/03/26/stored-procedure-mapping.aspx.

     

    Also note that, we need to map all the CUD stored procedures in EFv1 in VS2008.  Fortunately, in EF4 of VS2010, we can map the necessary stored procedures as our request.   But for example, we don’t map the insert stored procedure, we cannot perform insert operation on that entity, otherwise we get some exceptions.  

     

    Have a nice weekend!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, May 28, 2010 3:28 AM
    Moderator
  • Hi,

     

    From my first post, if the two columns are not modified, they won’t appear in the UPDATE statement.   I am afraid we don’t have such “update=false” in EF4.   Have you checked the Concurrency Mode?  

     

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, May 31, 2010 7:59 AM
    Moderator

All replies

  • Hi,

     

    Welcome to EF forum!

     

    Are these two columns included in primary key?   Or do you set the Concurrency Mode on the CreateDate and CreateBy properties?  If so, EF will make the concurrency check on these two columns every time we try to update the data.   If not, I don’t think these two columns are necessary when we try to update the data, even they are not nullable.   Here are some sample codes to update the entity:

    ==================================================================================

                    var entity = new MyEntity { ID = 1, Name = "New Name" };

                    context.AttachTo("TestDBEntities.MyEntity", entity);

                    var entry = context.ObjectStateManager.GetObjectStateEntry(entity);

                    entry.SetModifiedProperty("Name");

                    context.SaveChanges();

    ==================================================================================

    Here I used the SetModifiedProperty to which properties need to be updated.   The generated SQL won’t contain other columns except the Name and ID (PK).  

    ==================================================================================

    exec sp_executesql N'update [dbo].[MyEntity]

    set [Name] = @0

    where ([ID] = @1)

    ',N'@0 nvarchar(8),@1 int',@0=N'New Name',@1=1

    ==================================================================================

     

    Besides, to exclude them in the updated statements, we can also map the CUD stored procedures on the entity.   For detail, please see

    http://blogs.msdn.com/b/adonet/archive/2007/09/14/how-to-map-stored-procedures-using-the-ado-net-entity-framework.aspx

    http://blogs.msdn.com/b/adonet/archive/2008/03/26/stored-procedure-mapping.aspx.

     

    Also note that, we need to map all the CUD stored procedures in EFv1 in VS2008.  Fortunately, in EF4 of VS2010, we can map the necessary stored procedures as our request.   But for example, we don’t map the insert stored procedure, we cannot perform insert operation on that entity, otherwise we get some exceptions.  

     

    Have a nice weekend!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, May 28, 2010 3:28 AM
    Moderator
  • CreatedDate and CreatedBy columns are not part of PK. I'll check about Concurrency Mode.

    We just migrated from NHibernate to EF4. So I'm very new to EF.

    To exclude a field from update in NHibernate, we just have to add an attribute update="false" in the mapping file, which is very easy.

    From what I've gathered, in EF4, it seems more involved. Or is there an simpler way?

    Thank you.

    Friday, May 28, 2010 4:14 AM
  • Hi,

     

    From my first post, if the two columns are not modified, they won’t appear in the UPDATE statement.   I am afraid we don’t have such “update=false” in EF4.   Have you checked the Concurrency Mode?  

     

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, May 31, 2010 7:59 AM
    Moderator
  • Hi,

     

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, June 3, 2010 1:26 AM
    Moderator
  • The only way I've been able to make the scenario you describe work properly is to manually edit the storage schema as I describe in this post: http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/e5249861-2b81-4046-91d1-14cb0bc504d8

    Unfortunately, whenever the model is updated from the database, any such manual changes are lost. 

    Tuesday, June 8, 2010 10:43 PM
  • Do you know how this property is set when you're using EF in the ASP.NET MVC?

    In my view I only update a single column, yet the generated UPDATE statement mentions all the columns. My model and views are quite complex, but I am wondering if there is some built in way in EF/MVC to only identify changed properties?

    Thanks a lot in advance.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, April 9, 2013 8:08 PM