Inserting and Updating using Linq-2-SQL

Question

L2SQL seems great as long as I stick to read-only operations. As soon as I need to start changing fields, things get a bit hairy.

In particular, I'm running in to two distinct problems.

First, I'm attempting to populate a table with an arbitrary number of rows. The schema is mostly irrelevant, but it does have a 'BIGINT' primary key which is the identity column.

Since the primary key is an identity, I'm not setting it before calling Table.InsertOnSubmit() or Table.InsertAllOnSubmit(), and I'm yielding a DuplicateKeyException: "Cannot add an entity with a key that is already in use."

My second concern stems from the need for a LINQ equivalent to the following SQL statement:

UPDATE dbo.someTable SET someCol = 'someValue' WHERE pkValue = 20

If I query the needed row first, then change someCol's value before a DataContext.SubmitChanges(), all is fine. However, I'm seeking a solution that doesn't require me to query the row first. In other words, if the primary key is a known quantity, how can this be executed?

Answer 1

Looking at the question that Jose pointed out, what I am getting from it is that people get the error you are getting when they try to insert the same object twice (which makes sense - the first insert would tack on the ID and the second update would then be inserting the just created ID); Is it possible that you are tagging the same object for insert on submit two times (or more)? You said you are inserting an "arbitrary number of rows" - why don't you try dropping it down to only one.

Essentially, Linq to SQL should handle the situation as you are talking about it - an Identity column should not have the value populated and you call InsertOnSubmit() and SubmitChanges to get it in.

As to your second question, I don't think Linq to SQL has any explicit way of doing what you are wanting. Probably the easiest/best solution for it is to have a sproc that updates the data you want, and call it using Linq to SQL (passing the record ID you want updated).

Answer 2

For your first Q: Is your PK auto-incremented by DB?

For your second Q, if understood correctly, following should help (taken from MSDN FAQ on L2S). You should test following as I have bad experiences with MSDN ;-) :

Q. Can I update table data without first querying the database?

A. Although LINQ to SQL does not have set-based update commands, you can use either of the following techniques to update without first querying:

Use ExecuteCommand to send SQL code.

Create a new instance of the object and initialize all the current values (fields) that affect the update. Then attach the object to the DataContext by using Attach and modify the field you want to change.

Note from me: For update without querying, you will have to make sure DataContext does not have same object loaded already.

Answer 3

I've located the answers to my questions.

First, my issues with duplicate keys was the result of my table getting poorly imported in by the Designer. I deleted the connection from the Server Explorer and started from scratch and that resolved that.

Second, I've determined that the following code sample resolves my second issue:

using( var dc = new MyDataContext() )
{
   TableValue row = new TableValue();
   row.pkValue = 20;
   dc.TableValues.Attach(row); // DataContext thinks 'row' is the original.

   row.someCol = "Changed Value";  // DataContext formats an UPDATE statement
   dc.SubmitChanges();
}

The caveat with this is that I must set the 'Update Check' property of all my table columns to 'Never' or it throws exceptions. This makes me feel a bit uncomfortable and leads me to believe this isn't the best solution, but that could be in part to my lack of understanding of the 'Update Check' property's actual purpose.

Answer 4

In response to your second issue:

DataContext.someTable.Where(a => a.pkValue == 20).SingleOrDefault().someCol = 'someValue';
DataContext.SubmitChanges();