Convert SQL to lambda in C#

Question

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);

I didn't downvote, but maybe it is because you don't show your efforts (aka "[what have you tried?](http://meta.stackexchange.com/questions/172758/what-have-you-tried-epidemic)"). — Uwe Keim, Aug 09 '16 at 18:13
probably it's downvoted cause you are asking for conversion without showing any effort from your end. — Rahul, Aug 09 '16 at 18:17

Don Cheadle · Accepted Answer · 2016-08-09T18:06:57.770

3

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()

edited Aug 09 '16 at 18:06

answered Aug 09 '16 at 17:52

Don Cheadle

5,224
5
39
54

So I still have to do it separately. Is there a benefit of using lamda? – user1828605 Aug 09 '16 at 17:58
not sure what you mean separately. There's other LINQ/lambda approaches to solve your problem. The main benefit of lambda is that you don't have to write a bunch of SQL strings/call stored procedures in your C#. Instead, you can just use regular collection methods. Generally more readable/convenient when you're already in a C# codebase. – Don Cheadle Aug 09 '16 at 18:01
Understood. Thank you. When I said separately, I meant writing two separate statements: first to get the list of ids, and the use the list to retrieve the list of claims. I thought there's a way to do it in one statement. But I understand it now. Thank you. – user1828605 Aug 09 '16 at 18:04
@user1828605 edited it with pretty much the exact lambda equivalent of the SQL you wrote, if that's what you mean when you say "one statement". – Don Cheadle Aug 09 '16 at 18:07
Yeah. I see it now, and I agree with you. the first answer is much more readable. I don't do lambda that much, so it's still confusing. I usually write SQL statements in my C# application. I wanted to move away from that and start using lambda. Thanks for showing me this. This is very helpful. – user1828605 Aug 09 '16 at 18:10
@user1828605 I personally really like using EntityFramework to map POCO's to SQL tables, and then using C# lambdas to query/analyze information. I have another Answer related to that: http://stackoverflow.com/questions/15422664/is-there-an-alternative-to-code-first-migrations-with-ef-when-all-code-changes-a/34273946#34273946 which I think shows how you can start with minimal impact to your code base. In that answer, I reference an article that I think is great too about that. – Don Cheadle Aug 09 '16 at 18:17
@user1828605 Check my answer http://stackoverflow.com/a/38857754/509746 to see how to do this with just a single query. You just need to use .Join – Jetti Aug 09 '16 at 18:18

score 0 · Answer 2 · answered Aug 09 '16 at 18:14

You can use Join to do this in one "line":

class MissingItems
{
    public string Id {get;set;}
}
class Claims
{
    public string ClaimId {get;set;}
}

void Main()
{
    List<MissingItems> mi = new List<MissingItems>() {
        new MissingItems() {Id = "a"},
        new MissingItems() {Id = "b"},
        new MissingItems() {Id = "c"},
        new MissingItems() {Id = "d"},
    };
    List<Claims> cl = new List<Claims>() {
        new Claims() {ClaimId = "a"},
        new Claims() {ClaimId = "f"},
        new Claims() {ClaimId = "c"},
        new Claims() {ClaimId = "d"},
    };

    var a = mi.Join(cl, m => m.Id, c => c.ClaimId, (m,c) => {       
        return new { Claim = c.ClaimId, Missing = m.Id};
    });
    foreach(var b in a) 
    {
        Console.WriteLine("Claim: " + b.Claim + " Missing: " + b.Missing);
    }
}

This will join the ClaimId from Claims on the MissingItems Id property. The output looks like this:

Claim: a Missing: a
Claim: c Missing: c
Claim: d Missing: d

Convert SQL to lambda in C#

2 Answers2