user1828605 user1828605 - 4 months ago 18
SQL Question

Convert SQL to lambda in C#

How can I re-write the following query from SQL to lambda in C#? Is it recommended to write in lambda?

SELECT *
FROM Claims
WHERE id IN (SELECT claimId
FROM MissingItems
GROUP BY claimId);

Answer

Equivalent using LINQ lambdas - assuming you have a collection of MissingItem objects in your code (and a representation of Claim in your code)

List<String> distinctClaimIds = missingItems.Select(mi => mi.claimId).Distinct();

List<Claim> claimsWithThoseIds = claims.Where(c => distinctClaimIds.Contains(c.id)).ToList();

Edit for your "one statement" interest:

Closest to "one statement" (even though I think 2 is more readable) I can think of:

List<Claim> claimsWithThoseIds = claims.Where(c => missingItems.Select(mi => mi.claimId).Distinct().Contains(c.id)).ToList()