locked
Delete records LINQ to SQL

    Question

  • Given an expression (where clause) , how can I call SqlFormat or someting so that I can delete records based on the condition w/o having to retrieve the records first ?

     

     

    Thanks

    Tuesday, December 18, 2007 12:35 AM

Answers

  • You can always pass straight SQL through to the database using DataContext.ExecuteCommand.  I find I've been using that more and more when I need to trade strong typing for speed.  Unfortunately it's a move away from ORM and back to opaque SQL strings in your code, but at least the DataContext handles the database connection for you. 

     

    -Larry

     

     

    Friday, December 21, 2007 11:35 AM

All replies

  • Hi,

     

    As I understand, if you would like to delete a record through LinQ, you can try to refer to the following sample codes(Assumpting you drag a Customer table from the database to the LinQ designer view and the DataContext named CustomerDataContext):

    Code Block

    CustomerDataContext ctx = new CustomerDataContext("connection string");

    Customer cust = ctx.Customers.Where(c =c.CustomerID == "ALFKI").Single();
    ctx.Customers.DeleteOnSubmit(cust);
    ctx.SubmitChanges();

     

    Hope this helps,

    Regards,

     

    Wednesday, December 19, 2007 8:04 AM
  • abcpp:

     

    DeleteOnSubmit is for single records in memory, not abstract sets of records.  You can either retrieve the record from the database, or you can attach a record containing just the primary key and concurrency field(s), then call DeleteOnSubmit(), and SubmitChanges().

     

     

    Thursday, December 20, 2007 3:56 AM
  • I don’t want to call DeleteOnSubmit.

     

    Given an expression, can I

     

    1)      Somehow use the Where method and SqlProvider to map the columns

    2)      Somehow use SqlFormat to format delete statement

     

    I understand LINQ is ORM, isn’t my situation quite common where I don’t want to large retrieve records just to delete records ???

     

    I have been looking into DataContext but couldn't see anything that I can call to do that :-((

     

    Thanks

    Friday, December 21, 2007 6:15 AM
  • You can always pass straight SQL through to the database using DataContext.ExecuteCommand.  I find I've been using that more and more when I need to trade strong typing for speed.  Unfortunately it's a move away from ORM and back to opaque SQL strings in your code, but at least the DataContext handles the database connection for you. 

     

    -Larry

     

     

    Friday, December 21, 2007 11:35 AM
  • Hi Larry,

     

    That's what I am trying to avoid those sql statement. I thought by passing query expression and that the datacontext has info about data provider (in this case SQL), it can 'somehow' generate/format sql statement and I will call ExecuteCommand instead of me generating the sql statement myself ???

     

    I am not sure if anyone has encountered similar situation where you want speed and yet don't want to hard-coded the sql statement ???

     

     

    Thanks

     

    Tuesday, January 01, 2008 7:08 AM
  • How about something like:

     

      

    Code Snippet

       LqDsMyDataset lqds = new LqDsMyDataset();

     

       // get the rows to be deleted...

       var rows =

    from rec in lqds.MyTable

    where rec.something == "should be deleted"

    select rec;

     

        // and delete them!  (the ID Column is available automatically)

        DsMyDataset ds;

        string idColumnName = ds.MyTable.IDColumn.ColumnName;

        foreach (var row in rows)

        {

               lqds.MyTable.Delete( lqds.MyTable.ID.Name, row.ID );

        }

     

     

    Wednesday, August 06, 2008 10:26 AM
  • I may be wrong, but there is no .Delete(...) method. I think you need to be using the DeleteOnSubmit method.

     

     lqds.MyTable.DeleteOnSubmit(row);

    Thursday, November 06, 2008 11:42 PM
  •  

    Thursday, November 06, 2008 11:42 PM
  • There is another quirk here - My understanding is that due to late execution although your query _would_ return all the data you wanted to delete if you used it, it shouldn't as long as you don't actually read the data -

    i'm not near intellisense but it should eb something like:

    Dim ToDelete = From <Blah>

    DataContext.Table.DeleteAllOnSubmit(ToDelete)

    DataContext.submit()

    As I said I'm not a LINQ guru by any stretch of the imagination but I hope this helps a bit :)
    Wednesday, March 25, 2009 12:31 PM
  • You may try smth like this:

     

    OfficeRequestDatabaseDataContext c
    ... 
     
    IQueryable <OR_Limit> result = c.OR_Limits.Where(item => item.SubdivisionID == ID); 
     
    c.OR_Limits.DeleteAllOnSubmit(result); 
     
    c.SubmitChanges();
    

     

     

     


    Wednesday, December 02, 2009 10:03 AM
  • Someone should be fired at MS for the LINQ implementation.  Clearly someone sold them on this garbage.  It does about 60% of what a real world programmer would need.  The typical answer is "roll your own query decision engine, you can't delete but you could write your own delete function....bla bla bla.

    I am really disappointed in this. 

    How about table value parameters...

    LINQ 4.0 may actually be worth using some day.

    so as for now I will roll a delete view to handle this simple task!

    me
    Wednesday, February 17, 2010 6:04 PM
  • Being new to this too, I was surprised how 'odd' the proper ways seem

    But I've just used:

    dc.table.DeleteAllOnSubmit(dc.table.Where(f => f.id==id));
    dc.SubmitChanges();

    Probly does the same (ie retrieve before deleting), but at leasts its one reasonably clear line!

    Monday, October 11, 2010 12:54 PM
  • Stop thinking in SQL

    Try investigating the available methods a it more before passing judgement.

     

    Thursday, October 21, 2010 8:03 AM
  • That's the correct method.

    No this doesn't retrieve before deleting.

    Rememer this uses DEFERRED execution, to the actual SQL that will be fired on the db isn't really constructed until you call the submit changes.

    You could always take a look @ the generated sql, I would bet you get something along the lines of 'Delete from TABLE where TABLE.ID == yourvalue'

    Thursday, October 21, 2010 8:07 AM