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