How to delete in linq to sql?

Question

Hi

I am very new to linq to sql and I am not sure how to actually delete a record.

So I been looking at this tutorial

http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx

So for Update they have

NorthwindDataContext db = new NorthwindDataContext();

Product product = db.Products.Single(p => p.ProductName == "Toy 1");

product.UnitPrice == 99;
product.UnitsInStock = 5;

db.SubmitChanges();

For delete they have

NorthwindDataContext db = new NorthwindDataContext();

var toyProducts = from p in db.Producsts
              where p.ProductName.Contains("Toy")
                  select p;

db.Products.RemoveAll(toyProducts);

db.SubmitChanges();

So do I have to query every time, to get the record in order to delete that record? Like I can sort of see doing this with update since you need to give it a record which to update first and then make the changes so I understand the querying part but not with delete.

Like can't you just send in what you want to delete and it goes and deletes it? why do you have to first get it and then tell it to be deleted?

Is that not 2 hits on the database?

Also I have foreign key relationship that I am trying to get to work. So I have this

public ViewResult(string param1, string param2)
{
   Table A = new Table A
   A.Field1 = param1;
   A.Field2 = param2;

   Delete(A);
}

private void Delete(Table A)
{
   DbContext.A.DeleteAllOnsubmit(A.TableB);
   DbContext.A.DeleteAllOnSubmit(A.TableC);
   DbContext.A.DeleteOnSubmit(A);

}

So this fails it comes up with this message "Cannot remove an entity that has not been attached."

So I can see why the first 2 lines would fail in the delete method, since I made a new object and their is nothing in the object that has any information about TableB and TableC.

I however can't see why the last line still fails even if the 2 other lines where not there.

Like how I thought it would work it would take my Table A class object that I passed in and look through the table for that information contained in it. It does not seem to be the case though.

So do I first have to take the information out and then do a query to get it and then delete it, like in the example?

Also what is the difference between removeAll() and say DeleteAllOnSubmit().

Like I said I am new to linq to sql and have not been able to sit down and read a book on it due to time constraints. Once I have more time I will read through a book probably.

Thanks

Answer 1

You have several questions in your one question, but I will start with the simplest, about attaching, if you already have the primary key. If you don't have the primary key then I have always just done a fetch then a delete, but, whenever I do a fetch I tend to store the primary key for updates and deletes.

It will delete off of the primary key, but if you have that then just attach as I do below and call delete. I don't pass around the object needed by DLINQ as I want to be able to change it if I want, so I pass in a different User object and just pull the PK from the business class and put it into the DAO class.

var db = new MeatRequestDataContext();            
if (input.UserID > 0)
{
     entity = new User()
     {
         UserID = input.UserID
     };
     db.Users.Attach(entity);
     db.Users.DeleteOnSubmit(entity);
 }