Why isn't my SubmitChanges() working in LINQ-to-SQL?

Question

I created a .MDF database in my WPF application.

I then generated LINQ-to-SQL classes and used LINQ get all the customers.

Then I run through them and change each of their last names.

However, when I call SubmitChanges, the database remains unchanged.

I thought that was the purpose of SubmitChanges(), to submit changes to the database?

What am I missing, how do I "submit changes" back to my database?

public Window1()
{
    InitializeComponent();

    Main2DataContext _db = new Main2DataContext();
    var customers = from c in _db.Customers
                    select c;

    foreach (var customer in customers)
    {
        customer.LastName = "CHANGED lastname"; //ListBox shows changes 
    }

    _db.SubmitChanges(); //does NOT save to database (???)

}

This is a really dumb question but you have a Primary Key on the customer table right? — Restore the Data Dumps, Feb 25 '09 at 15:38
Yes: ID is primary key, does not allow NULLs, and Identity Specification is set to IsIdentity=Yes. I created the .MDF file with Visual Studio. — Edward Tanguay, Feb 25 '09 at 15:42

Micah · Accepted Answer · 2009-02-25T15:16:40.360

15

I think I know what the problem is. Are you using a local .mdf file? If that's the case, check your bin/debug folder. I bet you have a database in there with the changes. I think you have an .mdf file in your project that is getting copied to the bin/debug folder everytime you build. Therefore the changes are getting saved to the copy, and you're not seeing it reflected in the copy that resides directly in your project.

edited Feb 25 '09 at 15:16

answered Feb 25 '09 at 15:01

Micah

111,873
86
233
325

I've just run through this and it seems correct. Attach to the bin/debug copy of the MDF and you'll see the changes. – Restore the Data Dumps Feb 25 '09 at 15:56
bingo, that was it! appreciate the tenacity everyone – Edward Tanguay Feb 25 '09 at 16:25
I only know because I've spend hours chasing this same thing before. Gald I could help. – Micah Feb 25 '09 at 17:35
1

@Micah, your answer helped me too. But I am a bit sad because I might forget this since I didn't spend hours as you did. – anar khalilov Jan 14 '14 at 14:17
What is the solution if we still want to use the local db .mdf approach ? Any reliable solution ? – Hemant Bavle Jun 21 '14 at 05:14

score 2 · Answer 2 · answered Feb 25 '09 at 15:14

2

Make sure that your _db isn't getting reset to a new context at any point between the retrieval and change, if so well that is the problem. Also you can simplify your assignment of your datasource by doing TheListBox.ItemsSource = _db.Customers;. I have many places in current code where I am doing exactly what you describe and the changes are propogating fine. An additional suggestion, setup logging on your context (set the _db.Log to some writer, I generally use the Console.Out so I can watch changes in the output window while debugging) so you can see the changes that actually do occur when you call SubmitChanges().

answered Feb 25 '09 at 15:14

Quintin Robinson

81,193
14
123
132

I put in _db.Log = Console.Out; before the submitchanges line, it doesn't show in my Output window, or where is it supposed to output? – Edward Tanguay Feb 25 '09 at 15:28
ok, I maximally simplified the code now, it just gets the objects, changes them and saves them back with SubmitChanges, but the database still doesn't change them. Any idea why this is the case? – Edward Tanguay Feb 25 '09 at 15:30
Are you running in debug mode? Also do you have the option "Redirect all Output Window text to the Immediate Window" checked in Tools->Options->Debugging->General ? If so your output is going to the immediate window, otherwise Try setting up your own TextWriter around a StringBuilder and set Log. – Quintin Robinson Feb 25 '09 at 15:33
Also make sure your output window has "Show output from: " selected on Debug – Quintin Robinson Feb 25 '09 at 15:34
tried this: db.Log = new System.IO.StreamWriter(@"App_Data\linq.log") { AutoFlush = true } but it doesn't create a file. Hmm. – Edward Tanguay Feb 25 '09 at 16:06
ok turned on "Redirect all Output Window text to the Immediate Window" but it just tells me that it is loading each dll hmm. – Edward Tanguay Feb 25 '09 at 16:07
Well it seems like that is a separate issue, since we can't narrow this down I would take a look at Micah's suggestion. – Quintin Robinson Feb 25 '09 at 16:14

Why isn't my SubmitChanges() working in LINQ-to-SQL?

2 Answers2

Linked