CODEWITHSUNDEEP
.netlinqlinq-to-sql
stimms
stimms

Reputation: 44064

Cleanest syntax to delete a record in linqtosql

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

Upvotes: 2

Views: 1206

Answers (6)

Amy B
Amy B

Reputation: 110111

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();                       
}

Upvotes: 2

Mark Cidade
Mark Cidade

Reputation: 99957

  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);

Upvotes: 1

stimms
stimms

Reputation: 44064

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();

Upvotes: 1

CVertex
CVertex

Reputation: 18237

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.

Upvotes: 2

Ty.
Ty.

Reputation: 2220

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.

Upvotes: 0

Mark Cidade
Mark Cidade

Reputation: 99957

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

Upvotes: 0

Related Questions