«
»

LINQ to SQL and tables with no Primary Key

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:

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:

[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.

Tags: ,

This entry was posted on Friday, July 24th, 2009 at 6:24 am and is filed under LINQ. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

4 Responses to “LINQ to SQL and tables with no Primary Key”

  1. Brijesh says:
    August 25, 2009 at 7:27 pm

    hi,

    i am having the same scenario where in a table there is no primary key.
    In above suggested blog add a primary key in table but is there any othere wayout where without adding a new entity as primary key.
    its urgent.
    Thanks
    brijesh

    Reply
    • Rhys says:
      August 26, 2009 at 7:16 am

      If it is urgent, define a primary key. The alternative is to temporarily embed your own SQL logic into your code. This is not a good alternative (and if you don’t do it right you can expose yourself to SQL injection vulnerabilities). The following workaround is not in any way recommended:

      Using the partial class for your object, add a SubmitChanges(DataContext) method. The DataContext class provides a useful ExecuteCommand(string, params object[]) method. You can use this to execute a SQL command directly against your connection. Do not build a SQL string by concatenating the required bits together. Place all your parameters in the params portion of the method call. Here’s an example for the class above:

      public partial class SimpleRecord
{
   public void SubmitChanges(DataContext db)
   {
      db.ExecuteCommand(@"UPDATE [dbo].[SimpleTable]
         SET [value] = {0}
         WHERE [identifier] = {1}",
         this.Value, this.Identifier);
   }
}

      You will likely want to wrap some exception handling around that. By using the String.Format style in your command you are not simply substituting the values into your query. Instead you are building a parameterized query (e.g. {0} would be replaced with @p0). This way you are still separating the the data from the command.

      But finally, define a primary key. Your database table should have one anyway.

      Reply
  2. Brijesh says:
    August 27, 2009 at 5:11 pm

    Hi,

    Thanks for your response. one another query left/right outer join is possible in linq to entities ?. if not so is there any another way out for this. plz.

    Regards
    Brijesh

    Reply
    • Rhys says:
      August 29, 2009 at 6:55 am

      LINQ to entities is a different beast. While I’m pretty sure they have a lot in common this isn’t something I know much about. My guess would be that if you can express your query in LINQ it will work out what SQL is necessary to execute the query.

      Of course if you have a very specific query you need to run, consider creating a stored procedure on your database. (LINQ to SQL can handle these well, and I’m sure the story is similar with LINQ to Entities).

      Reply

Leave a Reply