locked
LINQ Updates using all columns in the WHERE clause instead of Primary Key RRS feed

  • Question

  • As the title states, my table updates are not using the primary key, but rather using all of the columns that were not changed including the primary key "AND"ed together in a where clause. This will not work very well as my indicies are over the primary key, not what LINQ arbitrarily decides to use in the where clause.

    Does anyone know why this happens?

    Here is an example:

    C# Code
    ==========
    BlogEntry entry = db.BlogEntries.SingleOrDefault( be => be.BlogEntryId == blogEntryId );
    entry.Post = "Updated Post";

    db.SubmitChanges();


    SQL that SHOULD be generated
    ==========================
    exec sp_executesql N'UPDATE [dbo].[BlogEntries]
    SET [Post] = @p1
    WHERE ([BlogEntryId] = @p0),N'@p0 int,@p1 text',@p0=4,@p1='Updated Post'


    SQL actually generated
    ==========================
    exec sp_executesql N'UPDATE [dbo].[BlogEntries]
    SET [Post] = @p6
    WHERE ([BlogEntryId] = @p0) AND ([BlogId] = @p1) AND ([UserId] = @p2) AND ([Title] = @p3) AND ([Tags] = @p4) AND ([UserTimestamp] = @p5) AND (NOT ([IsActive] = 1))',N'@p0 int,@p1 int,@p2 int,@p3 varchar(41),@p4 varchar(11),@p5 datetime,@p6 text',@p0=4,@p1=3,@p2=1,@p3='Test Title',@p4='lipsum,test',@p5='2008-01-21 11:31:00:000',@p6='Updated Post'






    Tuesday, January 22, 2008 5:51 PM

Answers

  • Each column of an entity has a property in designer called Update Check which is set to Always by default. Change its value to Never for all non-PK properties of your entity and you'll get your desired results.
    Tuesday, January 22, 2008 6:05 PM

All replies

  • Each column of an entity has a property in designer called Update Check which is set to Always by default. Change its value to Never for all non-PK properties of your entity and you'll get your desired results.
    Tuesday, January 22, 2008 6:05 PM
  • Thank you for the quick reply. This is what I was looking for.

    Do you have a decent source of documentation for LINQ? This was actually quite simple to fix, and I'm assuming it's documented somewhere.  Most of the online sources I've discovered now use out of date syntax.
    Tuesday, January 22, 2008 6:35 PM
  • Thank you very much. You saved me :) .
    Friday, December 30, 2011 11:49 AM