Wednesday, February 10, 2016

LINQ to SQL In Operator with Subquery

Given the following sql statement below using In operator with subquery as it's argument, I need to convert this to LINQ statement.
SELECT [CountryRegionCode]
      ,[Name]
      ,[ModifiedDate]
  FROM [AdventureWorks2012].[Person].[CountryRegion]
  where CountryRegionCode in (select distinct [CountryRegionCode] 
  from [AdventureWorks2012].[Person].StateProvince)

After doing some readings on LINQ documentation, I found it's equivalent LINQ code using let.
model.CountryRegionsList 
     = (from country in _context.CountryRegions
       let subQuery = (from stateprovince in _context.StateProvinces
                      select stateprovince.CountryRegionCode).Distinct()
       where subQuery.Contains(country.CountryRegionCode)
       select country).ToList();

0 comments:

Post a Comment