What could be causing db.SubmitChanges() to not work in linq-to-sql?

Question

I've set up a very simple example with LINQ-TO-SQL in WPF.

I can get an object (pageItem) out like this and I can change the property and when I call SubmitChanges() it gives me no error but it doesn't save the change.

MainDataContext db = new MainDataContext();
var pageItem = (from p in db.PageItems
              where p.Id == 1
              select p).SingleOrDefault();

pageItem.Title = "changed";
db.SubmitChanges();

What could be causing SubmitChanges not to submit the changes?

MORE INFO:

This doesn't work either, even the db.ExecuteCommand doesn't work, and strangely when debugging F11 doesn't step into SubmitChanges() or ExecuteCommand(), why can't I step in those?

using (var db = new MainDataContext())
{
    var pageItem = (from p in db.PageItems
                    where p.Id == 1
                    select p).SingleOrDefault();

    pageItem.Title = "changed";
    db.SubmitChanges();

    db.ExecuteCommand("INSERT INTO PageItems (Title) VALUES ('this is the title')");


    if (pageItem != null)
        MainContent.Children.Add(new QuickForm(pageItem));

}

more info:

The db.Log = Console.Out gives me this:

SELECT [t0].[Id], [t0].[IdCode], [t0].[Title], [t0].[Description], [t0].[DisplayOrder]
FROM [dbo].[PageItems] AS [t0]
WHERE [t0].[Id] = @p0
'TestPageManager23434.vshost.exe' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\PresentationFramework.resources\3.0.0.0_de_31bf3856ad364e35\PresentationFramework.resources.dll'
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

INSERT INTO PageItems (Title) VALUES ('this is the title')
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

The thread 0x1190 has exited with code 0 (0x0).

ANSWER

The solution was three-fold:

• I was changing a different database than I was looking at in visual studio, solution:

  var db = new MainDataContext(@"C:\Users\TestUser\Documents\Visual Studio 2008\Projects\TestPageManager23434\TestPageManager23434\Data\Main.mdf"))

• that made Update work but not SubmitChanges(), solution was to set the primary key.

• it still wasn't showing all the chagnes, problem was I had a number of "show data" windows open which weren't being updated

Answer 1

This can happen if you don't have a primary key defined on the tables in SQL Server

Answer 2

Are you using SQL Expression mdf file?

There's an article about how this might cause you to get a copy of the file and not the original, causing the symptoms you're describing.

FTA:

I think the project system or server explorer wizard offers to 'copy' your mdf into your project directory. Maybe you are operating on a copy of the database and viewing the other in server explorer.

Answer 3

For some reason the context may not be tracking changes. Try wiring up your db.Log to a writer and inspect what LINQ->SQL is doing when you call SubmitChanges()..

db.Log = Console.Out;

Then you can watch your output window running in debug and see what is going on.

Answer 4

I had the same problem in which the record in the Database I see in my project was not modified by the SubmitChanges method.

After so many trial and researches I found out that the system put another version of the Database Northwnd.mdf in the project's root directory \Bin\Debug\Northwnd.mdf. That is where the changes, perfectly, occurred.