Updating specific column using linq to SQL

Question

I have a local list of thousands of GUID's (over 100k) of records that I need to update.

Now i'm using simple:

foreach(var id in GUIDs)
{
    var objToUpdate = dataContext.table.Single(o=>o.id==id);

    objToUpdate.val1=...
    objToUpdate.val2=...
    ...


}
dataContext.SubmitChanges();

This solution is very slow. Every time when i call Single method, the whole record is retrieved from DB, (i don't need it, because i'm overwriting all this data except the Primary Key).

Is there any way I could retrieve only two columns that i really need? (Primary key, and another one column)?

When I do something like this:

dataContext.table.Select(o =>

              new sqlRecord
              {
                  PrimaryKey = o.PrimaryKey,
                  AnotherCol = o.AnotherCol
              }
           );

i get error:

Explicit construction of entity type '{0}' in query is not allowed.

Using a stored procedure is not an option. Setting correct data values is very complex, depending on external resources.

var query = dataContext.table.Where(o => GUIDs.Contains(o.id));
foreach(var objToUpdate in query)
{
   objToUpdate.val1 =...;
   objToUpdate.val2 = ...;
}
dataContext.SubmitChanges();

this will produce an error, that i'm using over 2100 parameters, while i have tons of GUIDs.

for this, i'm using an alternative:

(from ids in dataContext.fn_getGuidsFromString(String.Join("", GUIDs)) join o in dataContext.table on ids.id equals o.PrimaryKey select o)

where fn_getGuidsFromString is table function in SQL. This is better then using where and Contains.

My problem is that this works too slow. You must know, that in this table there is over 200 columns, some of them are ntext with a lot of data.

dataContext.table.Single(o=>o.id==id);

is about 20 times slower then this (depending on data):

dataContext.table.Single(o=>o.id==id).select(o=>new {o.id, o.anotherCol});

but with this I cannot update a record.

Do you have any suggestions? Regards

try to use bulk update, in your case you need to use store procedure. http://stackoverflow.com/questions/17274430/efficient-way-to-do-bulk-insert-update-with-entity-framework — Chandrasekar Kesavan, Sep 17 '16 at 14:49
i suggest using stopwatch to determine which line of your code is the time consuming one, then you can move towards improvement from there. — Niklas, Sep 17 '16 at 18:35
Take a look here: http://stackoverflow.com/questions/8223942/linq2sql-select-only-some-columns-but-still-able-to-submit-changes — astidham2003, Sep 18 '16 at 00:59
I think, that this is a good way, I need to use attach.. Following this link: https://devio.wordpress.com/2011/01/16/updating-a-single-column-in-linq-to-sql-summary/ But i cannot make it work. When I do Attach i get an error: Cannot add an entity with a key that is already in use. — mateuszwdowiak, Sep 18 '16 at 11:32
@mateuszwdowiak The devio article you cite shows only that you have to bring in the old values first. The documentation here https://msdn.microsoft.com/en-us/library/bb548978(v=vs.110).aspx shows that is not necessary if, for instance, you can calculate all of the new values without the old ones and know the primary key. — astidham2003, Sep 18 '16 at 20:37
@mateuszwdowiak I deleted my old answer and created a new one that takes your updates / comments into account. — astidham2003, Sep 18 '16 at 20:50
@mateuszwdowiak After your last update about the query working on SQL 2008, but not 2014, I would suggest you consider asking a new question with the appropriate tags.applied describing your current issue. — astidham2003, Sep 19 '16 at 22:49

score 0 · Answer 1 · answered Sep 18 '16 at 20:26

To get the values from specific columns use this query:

var objToUpdate = (from o in dataContext.Table
                   where o.id == guid
                   select new {o.Column1, o.Column2, ...}).Single();

The select new {...} syntax is important, because it projects into an anonymous type that is not part of the context tracking. If the object obtained was part of the context tracking, then you would receive an error when using the Attach method next, because you can't track two objects with the same primary key and Attach is going to add an object track with a primary key.

Now, to update the database create a stub of the entity with just the updated values and using the correct primary key:

TableName stub = new TableName();
// One of these should be a primary key.
stub.val1 = ...;
stub.val2 = ...;
TableName.Attach(stub); // Will throw an exception if the row with this primary key is already being tracked in memory.

After all of the changes have been made, call

 dataContext.SubmitChanges();

As an important aside, if the old values aren't needed and the primary keys are all already known, then a query to bring the old rows in can be skipped. The stubs can be added without querying the database first, then when SubmitChanges is called the correct rows will be updated.

Thank you, that solves the problem. There was another issue in my case. I was retrieving data using this statement: (from ids in dataContext.fn_getGuidsFromString(String.Join("", GUIDs)) join o in dataContext.table on ids.id equals o.PrimaryKey select o) using the same dataContext, retrieved items were already attached. That's why I got errors: "Cannot add an entity with a key that is already in use." Whena i changed it to anonymous objects, it solves the case. Thank you for suggestions! — mateuszwdowiak, Sep 19 '16 at 08:40
I've just discovered, that now i don't have exception on Attaching. I get an exception: Row not found or changed, when I'm trying to submit changes.. So stil this is not resolved. — mateuszwdowiak, Sep 19 '16 at 12:44
@mateuszwdowiak Attach does assume that the record with that primary key exists in the database. Could the record have existed and then have been deleted before the call to SubmitChanges? You could try moving the SubmitChanges() inside the foreach loop and stepping through a few iterations to see if this error is thrown for every row, or just some. Is there additional information in an inner exception? Do you have access to any SQL logs that would show you an operation that failed? — astidham2003, Sep 19 '16 at 19:44

Updating specific column using linq to SQL

1 Answers1