How to delete in linq to sql?

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

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

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

Method 2

this is a simple way to delete row from table by linq query.may be it helps .

        var summary_delete = database.summeries.Find(id);

        var delete = database.summeries.Remove(summary_delete);

        database.SaveChanges();

reference : http://mvc4asp.blogspot.in/2013/09/how-to-delete-table-row-in-sql-database.html

Method 3

Inserted_LINQDataContext db = new Inserted_LINQDataContext();
                Item itm = new Item();
                int ID = Convert.ToInt32(TextBox1.Text);
                var DeleteID = from d in db.Items
                               where d.id == ID
                               select d;
                db.Items.DeleteAllOnSubmit(DeleteID);
                db.SubmitChanges();
                Label2.Text = "Record deleted Successfully.";
                TextBox1.Text = "";

where Item is Table name, Linserted_LINQDataContext is your Linq DB name, id is the Column name in Item table. Items is the alias name of Item table in linq.

Method 4

        SupportDataDataContext Retrive = new SupportDataDataContext();
        //  SupportDataDataContext delete = new SupportDataDataContext();

        Topic res = Retrive.GetTable<Topic>().Single(t => t.ID == topicID);


        if (res != null)
        {
            Retrive.Topics.DeleteOnSubmit(res);
            Retrive.SubmitChanges(ConflictMode.ContinueOnConflict);
        }

Method 5

I know the question is old but this may be useful to someone:

"YourDataContext" dc = new "yourDataContext";
"YourTable" element = dc."YourTable".First(a => a.Id == 12345);
dc."YourTable".DeleteOnSubmit(element);
dc.SubmitChanges();

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0