views:

898

answers:

1

This is the SQL I want (ClearinghouseKey is a bigint):

select *
from ConsOutput O
where O.ClearinghouseKey IN (
  select distinct P.clearinghouseKey
  from Project P
  Inner join LandUseInProject L on L.ClearinghouseKey = P.ClearinghouseKey
  where P.ProjectLocationKey IN ('L101', 'L102', 'L103')
  and L.LandUseKey IN ('U000', 'U001', 'U002', 'U003')
)

The inner query is straight forward and gives correct results in LINQPad:

var innerQuery = (from p in Projects
                  join l in LandUseInProjects on p.ClearinghouseKey equals l.ClearinghouseKey
                  where locations.Contains(p.ProjectLocationKey) 
                  &&  (landuses.Contains(l.LandUseKey)) 
                  select new { p.ClearinghouseKey  }).Distinct();

But the outer query gives the error: Type arguments from ...Contains..cannot be inferred from usage:

var returnQuery = from o in OperOutput
                  where (innerQuery).Contains(o.ClearinghouseKey)
                  select o;

Is it because ClearinghouseKey is a bigint? Any other ways to write this query?

Thanks, Jeanne

+4  A: 

Don't use an anonymous type:

select new { p.ClearinghouseKey })

Should be

select p.ClearinghouseKey)

Also consider using Any instead of Contains (I don't have reasons for choosing one over the other, yet).

where innerQuery.Any(i => i == o.ClearinghouseKey)
David B