LINQ join with filter criteria
How is something like this done in linq? It has filter criteria on the JOIN.
This is taken from this question: SQL 开发者_运维百科Filter criteria in join criteria or where clause which is more efficient
select salesman.salesmanid, max(sales.quantity)
from salesman
inner join sales on salesman.salesmanid =sales.salesmanid
and sales.salesdate < salesman.promotiondate
group by salesman.salesmanid
Thanks
You can't join
on anything other than equals
, but that's probably not what you want here anyway. I would contend that the SQL query is awkwardly written and that the date comparison should be in a WHERE
clause, but I suppose that's subjective. Anyway, that's the only way to do it in Linq:
var results =
from sm in salesman
join s in sales on sm.salesmanid equals s.salesmanid
where s.salesdate < sm.promotiondate
group s by s.salesmanid into g
select new { salesmanid = g.Key, maxsales = g.Max(s => s.quantity) };
Note - corrected typo on group line
Assuming you have navigation properties between your tables, you can leave the join to entity framework.
var results = from s in salesmen
group s by s.salesmanid
select new
{
s.salesmanid,
maxsales = s.sales
.where(x => s.salesdate < x.promotiondate)
.max(x => x.quantity)
};
精彩评论