LINQ join with filter criteria

Question

How is something like this done in linq? It has filter criteria on the JOIN.

This is taken from this question: http://stackoverflow.com/questions/1401889/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

Answer 1

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

Answer 2

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)
              };