How to write Update logic using LEFT JOIN in LINQ Method Syntax?

Question

In my .Net application, EntityFramework 6 is used and I am writing a logic in C# to update one of the column values of the ORDERTABLE to Yes or No.

In ORDERTABLE, for a Single TranID there will be one or more OrderNumber. I need to update the last OrderNumber (i.e., MAX(OrderNumber)) value of a TranID to 'NO' and for remaining OrderNumber value will be 'YES'.

Below SQL query is giving me the expected result but I am not sure of converting this into LINQ to Entities code logic.

UPDATE SC
SET ORDERVALUE = CASE WHEN SC1.ORDERNUMBER IS NULL THEN 'YES' ELSE 'NO' END
FROM ORDERTABLE SC
LEFT JOIN (SELECT MAX(ORDERNUMBER) ORDERNUMBER, SID FROM ORDERTABLE WHERE STATUS ='A' GROUP BY SID) SC1
ON SC.ORDERNUMBER = SC1.ORDERNUMBER AND SC.SID = SC1.SID 
WHERE SC.STATUS ='A' AND SC.SID IN (SELECT ID FROM  ORDERMASTER(NOLOCK) WHERE MID = variablename)

Select Query:

SELECT 
    ORDERVALUE = CASE WHEN SC1.ORDERNUMBER IS NULL THEN 'YES' ELSE 'NO' END,
SC.*
    FROM ORDERTABLE SC
    LEFT JOIN (SELECT MAX(ORDERNUMBER) ORDERNUMBER, SID FROM ORDERTABLE WHERE STATUS ='A' GROUP BY SID) SC1
    ON SC.ORDERNUMBER = SC1.ORDERNUMBER AND SC.SID = SC1.SID 
    WHERE SC.STATUS ='A' AND SC.SID IN (SELECT ID FROM  ORDERMASTER(NOLOCK) WHERE MID = variablename)

In C#, LINQ to Entities code should looks like somewhat similar to below first format (LINQ Method) but not like the second one(LINQ query).

//1. LINQ Method
using (var context = new ProductDBEntities())
{

     dbContextTransaction = context.Database.BeginTransaction();
     ORDERDETAILS od = context.ORDERDETAILS.Single(G => G.ID == 1);
     od.OrderNumber = OrdNumber;
     od.LastModifiedBy = createdBy;
     od.LastModifiedTS = DateTime.UtcNow;
     context.SaveChanges();
}

//2. LINQ Query
using (var context = new ProductDBEntities())
{
    var query = from st in context.ORDERTABLE 
                where ...
                select st;
   
    var ORDERTABLE = query.FirstOrDefault<ORDERTABLE >();
}

It is possible only with EF Core 7. Are you using this version? — Svyatoslav Danyliv, Oct 19 '22 at 10:57
Then you have to execute Raw SQL by yourself. LINQ update is not supported prior EF Core 7 — Svyatoslav Danyliv, Oct 19 '22 at 11:00
@SvyatoslavDanyliv - In my application itself I have a code to update single table. I've Updated my question. — Tech Learner, Oct 19 '22 at 11:06
@SvyatoslavDanyliv - Can you please help me to create a Select query for this logic. — Tech Learner, Oct 19 '22 at 11:13
I can, but reason for this? SQL query will be faster and safe. — Svyatoslav Danyliv, Oct 19 '22 at 11:15
@SvyatoslavDanyliv - I have update my question with select statement. Please convert that into LINQ method. And the reason is I am not allowed to write StoredProcedure or SQL query in my application. — Tech Learner, Oct 19 '22 at 11:17
For writing LINQ query, it is needed model classes for tables which are used in SQL (with navigation properties) — Svyatoslav Danyliv, Oct 19 '22 at 11:18

How to write Update logic using LEFT JOIN in LINQ Method Syntax?

0 Answers0