SQL TO LINQ Where In Clause with Max

Question

I want to convert the following query to LINQ equivalent. I've gone through this but unable to acheive my results which has Max. can anyone help me in this regard.

Below is my SQL Query:

select max(a.amount_limit) as AmountLimit from tbl_AmountEmpRole a where a.Role_Name in(select b.Role_name from tbl_UserRoles b where Emp_id=4)

Thanks in Advance

Can you show your current linq code, even if it does not use `max` yet? — Andrei, Sep 09 '19 at 16:30

score 0 · Answer 1 · answered Sep 09 '19 at 16:42

0

You can try somthing like this :

 var role_names=tbl_UserRoles.Where(x=> x.Emp_id==4).Select(x=>x.Role_name);
 var MaxacountLimit=tbl_AmountEmpRole.Where(y=>role_names.contains(y))
 .Max(z=>z.amount_limit);

answered Sep 09 '19 at 16:42

Mehrdad Dowlatabadi

1,335
2
9
11

score 0 · Accepted Answer · answered Sep 09 '19 at 16:43

Assuming a variable called context, this should work:

context.tbl_AmountEmpRole
.Where(er => context.tbl_UserRoles.Where(ur => ur.Emp_id == 4).Select(ur => ur.Role_Name).Contains(er.Role_Name))
.Max(er => er.amount_limit);

However, I believe you might be able to rewrite your query using a JOIN:

SELECT MAX(a.amount_limit) as AmountLimit FROM tbl_AmountEmpRole a 
INNER JOIN tbl_UserRoles b 
ON a.Role_Name = b.Role_Name
WHERE b.Emp_id = 4

In which case you could use the Join method:

context.tbl_AmountEmpRole.Join(context.tbl_UserRoles, er => er.Role_Name, ur => ur.Role_Name, new { er, ur })
.Where(j => j.ur.Emp_id = 4)
.Max(j => j.er.amount_limit);

Make sure to double-check syntax and signatures. I wrote this from memory.

SQL TO LINQ Where In Clause with Max

2 Answers2