none
How to delete efficiently? RRS feed

  • Question

  • Hello,

    Can the following be made more efficient, just using LINQ?

    I have a one-to-many relationship between two tables (say Order and Line) which are joined by OrderId. I want to delete all Orders and Lines where Order.Something == a value. The code I have just now is:

    IQueryable<Order> orders = context.Orders.Where(ord => ord.Something == 'A1234567');
    
    foreach (Order order in orders)
    {
      context.Lines.DeleteAllOnSubmit(order.Lines);
    }
    context.Orders.DeleteAllOnSubmit(orders);
    context.SubmitChanges();

    However when selecting lots of Orders the process took a very long time & I had to resort to hacking together some SQL which was much quicker, though not what I really wanted to do. Each of these was submitted using context.ExecuteCommand()

    DELETE FROM Lines
    WHERE OrderId IN (SELECT OrderId FROM Orders WHERE Something = 'A1234567')
    
    DELETE FROM Orders WHERE Something = 'A1234567'

    p.s. I don't allow DELETE ON CASCADE on the foreign key constraint, so that wouldn't help with the answer.

    Thanks

    John.

    Wednesday, July 7, 2010 8:16 AM

Answers

  • Unfortunately not - LINQ to SQL only offers the following options:

    1. Cascade on delete
    2. Delete objects that have been materialized
    3. Write your own query and use LINQ to SQL to execute it
    There is no LINQ-based DELETE operations that does not use materialized objects.

    [)amien

    • Marked as answer by John T. Angle Monday, July 12, 2010 4:45 PM
    Wednesday, July 7, 2010 6:33 PM
    Moderator

All replies

  • Unfortunately not - LINQ to SQL only offers the following options:

    1. Cascade on delete
    2. Delete objects that have been materialized
    3. Write your own query and use LINQ to SQL to execute it
    There is no LINQ-based DELETE operations that does not use materialized objects.

    [)amien

    • Marked as answer by John T. Angle Monday, July 12, 2010 4:45 PM
    Wednesday, July 7, 2010 6:33 PM
    Moderator
  • Thanks for your answer [)amien.

    Monday, July 12, 2010 4:45 PM