none
Update Datatable using LINQ

    Question

  • Hi,

    I have two Datatable say Order and OrderDetail. In Order Datatable there is a OrderTotal Column which is updated on the basis of order detail column
    e.g

    "Order Datatable"

    OrderID     Description          Total
     
    1                    ABC
     2                    MNO


    "OrderDetail DataTable"

    OrderDetailID               OrderID        Description           Amount
       1                                    1                  DDD                       20
       2                                    1                  MMM                       15
       3                                    2                  NNN                        40
       4                                    2                  XXX                        80
             

    In Sql my query would be "Update Order set Total = sum(Amount) from OrderDetail Inner join Order on Order.OrderID = OrderDeatail.OrderID group by OrderDetail.OrderDetailID."

    Please note I have just written the query for understanding, it might not work in SQL.

    I want the above sql query in LINQ. the Sum of Amount column of orderdetail table should be updated against the corresponding Order table in the Total column.
    How do i do it in LINQ ??

    Tuesday, July 14, 2009 12:44 PM

Answers

  • LINQ To SQL cannot perform such complex UPDATE statements itself.  You can calculate this value on client side and then assign it to OrderDatatabe.Total property.

    Or create stored procedure for updating record in Order Datatable. Put the mentioned update SQL to this procedure
    Import this procedure to DataContext
    Use this procedure in the partial method "partial void UpdateOrderDatatabe(OrderDatatable instance);"
    Mark the OrderDatatabe.Total property with IsDbGenerated=true and AutoSync=AutoSync.OnUpdate attributes

    Devart Team
    http://www.devart.com/dotconnect
    ADO.NET data providers for Oracle, MySQL, PostgreSQL, SQLite with Entity Framework and LINQ to SQL support
    Tuesday, July 14, 2009 4:47 PM