Rewriting a SQL with LINQ

Question

I have this SQL:

select * from ADDRESSES where ZIPCODE ='66210'and ADDRESS_K in 
       (select ADDRESS_K from GROUPADDRESS where GROUP_K in 
       (select GROUP_K from GROUPS where NPI = 'groupnpi' and TAXID = 'grouptin') 
        and ADDRESSTYPE_RTK = '_REI0PVM65')

I want to rewrite it in LINQ

Can someone help with converting it to LINQ? That's as far as I could get.

Answer 1

This is not tested but should give you a good start (this helped How can you handle an IN sub-query with LINQ to SQL?)

var innerInnerQuery = from g in GROUPS
                      where NPI == "roupnpi" 
                          && TAXID == "grouptin"
                          && ADDRESSTYPE_RTK == "_REI0PVM65"
                      select g.GROUP_K;
var innerQuery = from ga in GROUPADDRESS
                 where innerInnerQuery.Contains(ga.GROUP_K)
                 select ga.ADDRESS_K;
var query = from a in ADDRESSES
            where ZIPCODE == "66210"
                && innerQuery.Contains(a.ADDRESS_K)
            select a;