LINQ-to-SQL and SQL Compact - database file sharing problem

Question

Hello.

I'm learing LINQ-to-SQL right now and i have wrote a simple application that define SQL data:

  [Table( Name = "items" )]
  public class Item
  {
    [ Column( IsPrimaryKey = true, IsDbGenerated = true ) ]
    public int Id;
    [ Column ]
    public string Name;
  }

I have launched 2 copy of application connected to the same .sdf file and tested if all database modifications in one application affects another application. But strange thing arise. If i use InsertOnSubmit() and DeleteOnSubmit() in one application, added/removed items are instantly visible in other application via 'select' LINQ queue. But if i try to modify 'Name' field in one application, it is NOT visible in other applicaton until it reconnects the database :(. The test code i use:

  var Items = from c in db.Items
              where Id == c.Id
              select c;
  foreach( var Item in Items )
  {
    Item.Name = "new name";
    break;
  }
  db.SubmitChanges();

Can anyone suggest what i'm doing wrong and why InsertOnSubmit()/DeleteOnSubmit works and SubmitChanges() don't?

Answer 1

This is related to how Linq-to-Sql manages Object Identity. If you query for the same entity more than once, you will always receive the same object representing the row in the database.

When you insert or delete entities, you are able to see the changes from another client because Linq-to-Sql will need to create/delete the objects associated with those entities and, if there are no conflicts, this presents no problem.

Updating has a different behavior that's explained in the Object Identity article.

LINQ to SQL uses this approach to manage the integrity of local objects in order to support optimistic updates. Because the only changes that occur after the object is at first created are those made by the application, the intent of the application is clear.

If you need the most updated data from the database, use the DataContext.Refresh with RefreshMode.OverwriteCurrentValues.