bencemik95 bencemik95 - 3 months ago 27
SQL Question

Complex SQL Server query to LINQ to SQL query C#

I'm new to linq to sql and I have some complex SQL query.

My SQL Server query does this shortly: I have 2 tables, product (a) and productorder (b). Product has amount and a second amount which warns the customer how many items still in progress.

In "a" we have 10 papers if we order 5. Then table a second amount value is 5. If we order more : 5 = 5 + more

So I write my T-SQL here. I want a linq to sql

UPDATE a
SET a.secondamount = a.secondamount + b.orderedamount
INNER JOIN b ON a.productid == b.productid
WHERE b.status = false ;

Answer

Linq is not used for updating but for querying. You can use linq to find all the records you want to update and prepare the data but then the update itself should happen separately.

var result = from itemA in a
             join itemB in b on itemA.productId equals itemB.productId
             where itemB.Status == false
             select new { itemA, itemB.orderedamount };

foreach(var item in result)
{
    //do update using item.itemA.secondAmount + item.orderedamount
}