Cleanest syntax to delete a record in linqtosql

Question

What is the best way to delete a database record using LINQ when I have the primary key?

Answer 1

You can map a stored procedure to a method on the DataContext that deletes a record with a given value for the primary key.

Answer 2

I've always used the select-delete approach as it's very straight-forward. As marxidad said, you can map a stored proc if you really want to. I believe there are plans for ESQL to support insert, update and delete operations in the future.

Answer 3

That approach is acceptable and typical.

It is a little wasteful because you're requesting the whole record. This cannot be avoided given the DeleteOnSubmit method signature.

Why does LINQ to SQL work this way? The answers obvious if you think about tables that do not have primary keys, or use composite keys. LINQ to SQL needs the whole record for the worst case where it needs to match every object property against the table fields.

But as should be asked for all concerns of performance - are you absolutely sure this is a performance issue for you?? Where's your profiling proof?

I haven't tried this yet but there might be a way to achieve the same thing with DataContext.ExecuteCommand.

Answer 4

I ended up doing a select, but improved my syntax which I think was really my concern:

Old:

var products = from p in db.Products select p;
db.Products.DeleteOnSubmit(products.Take(1).Single()); <--seemed nasty
db.SubmitChanges();

New:

Service service = db.Services.Where(s => s.id == serviceID).FirstOrDefault(); <--nicer
db.Services.DeleteOnSubmit(service);
db.SubmitChanges();

Answer 5

  public static Delete(this ITable table, object key)
   { var t = table.GetType();
     var tableName  = t.GetCustomAttributes
                                    (typeof(TableAttribute),false)[0].Name;
     var primaryKey = (from p in t.GetProperties()
                       from c in p.GetCustomAttributes
                                            (typeof(ColumnAttribute),false)
                       where c.IsPrimaryKey
                       select c.Name).Single();
     using (var cmd = table.Context.Connection.CreateCommand())
      { cmd.CommandText = string.Format( "DELETE FROM {0} WHERE {1} = @key"
                                        ,tableName, primaryKey);
        cmd.Parameters.Add(new SqlParameter("@key", key));
        cmd.ExecuteNonQuery();        
      }            
   }

/* ... */
dataContext.Table1.Delete(id);

Answer 6

If you set update checks on all your columns to never or when changed, you can simply attach an instance with the primary key and delete the row without a load.

public void DeleteCustomer(int CustomerID)
{
    NorthwindContext c = new NorthwindContext c;
    Customer target = new Customer(){CustomerID = CustomerID};
    c.Customers.Attach(target);
    c.Customers.DeleteOnSubmit(target);
    c.SubmitChanges();                       
}