Here are two example queries:
var queryA =
from t1 in context.T1
join t2 in context.T2
where
tl.colA == desiredColAValue
select new ObjectA(valuesFromQueryA)
;
var queryB =
from t1 in context.T1
join t2 in context.T2
where
tl.colA != null
&& t2.id == someId
select new ObjectB(quiteOtherValuesNowFromQueryB)
;
Note that their joins are exactly the same, but the where conditions and objects returned are different.
In my application there are more joins and the queries are executed in the same procedure. Is there a way to “factor out” the joins in an elegant and profitable way (so that the joins are only executed once)?
I’ve searched online and it seems that CTE’s are not supported in LINQ.
4
Usually you should create intermediate query and reuse in other queries:
var commonQuery =
from t1 in context.T1
join t2 in context.T2 on ... equals ...
select new { t1, t2 }
;
var queryA =
from c in commonQuery
where
c.t1.colA == desiredColAValue
select new ObjectA(valuesFromQueryA)
;
var queryB =
from c in commonQuery
where
c.t1.colA != null
&& c.t2.id == someId
select new ObjectB(quiteOtherValuesNowFromQueryB)
;