Complex SQL Server query to LINQ to SQL query C#

Question

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 ;

You Write already a SQL Query. Do You want to translate this to LINQ? — Osman
– Osman, Commented Aug 23, 2016 at 10:28
Yes , this code snippet what i used in my old program. Right now i have to replace every sqlcommand/reader etc to linq to sql. I almost replaced everything i stucked with this one. — bencemik95
– bencemik95, Commented Aug 23, 2016 at 10:38

Ivan Stoev · Accepted Answer · 2016-08-23 11:06:07Z

1

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
}

edited Aug 23, 2016 at 11:06

Ivan Stoev

208k17 gold badges356 silver badges376 bronze badges

answered Aug 23, 2016 at 10:27

Gilad Green

37.3k7 gold badges67 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Ivan Stoev Over a year ago

Gilad, sorry, tried to make your query compilable and introduced another silly mistake.

Ivan Stoev Over a year ago

I meant I broke your answer with my first edit, so I apologize for that.

Gilad Green Over a year ago

@IvanStoev - ahh ok ya got it. The equals was just a silly mistake :) haha. About the join compared to the where - I know the join is the correct one in terms of how the original sql but what will happen if that join yields more than one record?.. that is why I changed it to the Any

Ivan Stoev Over a year ago

Well, specifically in this case I was not thinking for performance or cardinality, but only that you need itemB inside the select :-)

Gilad Green Over a year ago

@IvanStoev - och of course haha stupid me. I did it with the join initially and then changed without checking haha :) thanks for the edit

|

Collectives™ on Stack Overflow

Complex SQL Server query to LINQ to SQL query C#

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related