Using LINQ to SQL to update database?

Question

When I execute the following code I don't get any error, but it doesn't update the changes on the database either. I have 5 entries in the table user, and after executing the following code there is no user with "Active" state in the database.

Am I supposed to write the update statement myself or does it do it for me? What can be the problem here?

var dbContext = new DataClasses1DataContext();

List<user> users = (from u in dbContext.users
                          where u.age < 30
                          select u).ToList();

users[0].state = "Active";
dbContext.SubmitChanges();

EDIT:

So I know what the problem is. I change the State on my object, and the update statement contains the state as a where clause. So when executing the query it can't find an item that matches - it fails on [state] = @p4.

Why is it using all parameters in my update statement when I have a primary key?

UPDATE [dbo].[user]
SET [state] = @p5
WHERE ([id] = @p0) AND ([firstName] = @p1) AND ([lastName] = @p2) 
    AND ([age] = @p3) AND ([state] = @p4)
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [1]
-- @p1: Input NChar (Size = 10; Prec = 0; Scale = 0) [firstName     ]
-- @p2: Input NChar (Size = 10; Prec = 0; Scale = 0) [lastName  ]
-- @p3: Input Int (Size = -1; Prec = 0; Scale = 0) [25]
-- @p4: Input NChar (Size = 10; Prec = 0; Scale = 0) [Active    ]
-- @p5: Input NChar (Size = 10; Prec = 0; Scale = 0) [Active]

That looks Ok to me, are you sure you have items that is age < 30? — Raymund
– Raymund, Commented Sep 26, 2011 at 2:43
I tested your code snippet and it works for me. There is something outside of the code you have shown us that is causing the problem. Could you post a full DDL SQL? — Branko Dimitrijevic
– Branko Dimitrijevic, Commented Sep 26, 2011 at 3:19
That is not a problem. That's called "optimistic concurrency", and what it's doing is checking to make sure the values of the table have not changed since you retrieved the object. @p4 contains the old value, @p5 contains the new value. This looks correct. Are you certain that you are looking at the right database? — Erik Funkenbusch
– Erik Funkenbusch, Commented Sep 26, 2011 at 4:40

Kirk Broadhurst · Accepted Answer · 2011-09-26 04:03:20Z

2

You need to change the concurrency pattern on your DBML. This website about Updating LINQ-to-SQL entities explains a bit about it.

Anything you don't want to include in the WHERE clause should have their Update Check set to Never on the DBML.

Open DBML
Select the state property on your user object
Press F4 (to see properties)
Change Update Check (the last setting in the properties) to Never
Save and try again

Normally I'd turn off Update Check for all columns but you should do more reading to understand exactly how it works in case you need concurrency.

answered Sep 26, 2011 at 4:03

Kirk Broadhurst

28.9k20 gold badges111 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kirk Broadhurst Over a year ago

@aryaxt I know. Update Check doesn't stop the columns from being updated, it stops them from being included (i.e. checked) in the WHERE clause. This answer will fix your problem.

aryaxt Over a year ago

Thanks, this fixed the problem with the update statement, but it still doesn't update the database.

evpo · Accepted Answer · 2011-09-26 02:29:05Z

1

Apparently, you don't have a primary key on the user table or you didn't specify it in your dbml file. LINQ to SQL only throws exceptions when you insert or delete a record with no primary key. It doesn't throw an exception when updating for some reason.

answered Sep 26, 2011 at 2:29

evpo

2,53118 silver badges24 bronze badges

5 Comments

evpo Over a year ago

have you checked Primary Key property of your primary key column in your dbml designer? Is it "True"?

aryaxt Over a year ago

yes it's set to true, and the primary key icon shows up on my "id" field

aryaxt Over a year ago

Is it possible to see the sql statement that is being executed by LINQ on the database?

jason Over a year ago

@aryaxt: Yes. Use dbContext.Log = Console.Out or set it to some other TextWriter of your choosing.

jason Over a year ago

@evpo: This was my first thought too. I've seen this behavior before and the problem was exactly as you describe.

Collectives™ on Stack Overflow

Using LINQ to SQL to update database?

2 Answers 2

2 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related