i-think Twenty-Two

Now with more coherency.

LINQ to SQL and tables with no Primary Key

| Comments

I ran into an interesting issue with LINQ to SQL yesterday. I had to update a table with no Primary Key. As I expected, LINQ to SQL wasn’t too happy with this scenario. Unfortunately LINQ to SQL will only throw an exception when you try to Insert or Delete a record with no primary key. Updates fail silently.

It’s actually quite obvious when you look into what is happening. To do an update you would usually do something like this:

1
2
3
4
5

var update = (from v in db.SimpleRecords
              where v.identifier == 12
              select v).First();
update.value = "new value";
db.SubmitChanges();

Of course, nothing happens. Here’s the code (slightly edited for readability) that is generated by the LINQ to SQL classes:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

[Table(Name="dbo.SimpleRecord")]
public partial class SimpleRecord
{
   private int _identifier;
      
   private string _value;
      
      public SimpleTable()
      {
      }
     
      [Column(Storage="_identifier", AutoSync=AutoSync.Always,
         DbType="Int NOT NULL IDENTITY", IsDbGenerated=true)]
      public int identifier
      {
         get
         {
            return this._identifier;
         }
         set
         {
            if (this._identifier != value)
            {
               this._identifier = value;
            }
         }
      }
     
      [Column(Storage="_value", DbType="VarChar(50)")]
      public string value
      {
         get
         {
            return this._value;
         }
         set
         {
            if (this._value != value)
            {
               this._value = value;
            }
         }
      }
}

Without a primary key the two following interfaces aren’t emitted: INotifyPropertyChanging and INotifyPropertyChanged

Therefore LINQ to SQL doesn’t know that your record has changed (so can’t warn you that it can’t update).

Now that you understand the problem the solution is simple: Define a primary key in your table.

Comments