none
delete for linq to sql RRS feed

  • Question

  • I need to write some linq to sql statments for sql server 2008 r2 database that accomplishes the following tasks in a C# 2010 .net application:

    1. I need to delete one row from a table where the record I would be deleting is the key to the table called main.

    2. I also need to delete records from an associated table (called sub) that has the key to the main table as one of its columns. This table called sub has no foreign key constraints setup as a requirement to the table called main. In the table called sub there can be from 1 to 500 occurences of the reference to the table called main.

    3. I am not certain how to setup these two deletes? Would this be separate linqs statements? would I need two separate  DeleteOnSubmit statements? Would I wrap the two delete statements into one transaction so that if there is a problem, I can rollback the changes?

    Can you tell me and/or point me to a reference on how to complete this task?


    Tuesday, June 19, 2012 4:55 PM

Answers

  • This task could be done quite straight forward. e.g.

    var toBeDeleted_EntityC = from c in dbConx.Categories where c.CategoryID == 1 select c;
    dbConx.Categories.DeleteOnSubmit(toBeDeleted_EntityC);
    
    var toBeDeleted_EntityP = from c in dbConx.Products where c.CategoryID == 1 select c;
    dbConx.Products.DeleteOnSubmit(toBeDeleted_EntityP);
    
    try
    {
        dbConx.SubmitChanges();
    }
    catch (Exception es)
    {
    
    }

    But if they have key relationship, always try to drop children first.

    In EF, Cascade Delete is also available for relationship-ed entities you may refer this MSDN reference for detail http://msdn.microsoft.com/en-us/library/bb738695.aspx

    Hope this help.

    • Proposed as answer by TinMgAye Tuesday, June 19, 2012 5:25 PM
    • Marked as answer by wendy elizabeth Tuesday, June 19, 2012 6:14 PM
    Tuesday, June 19, 2012 5:11 PM