1

I have the following expression

        var q = from c in D1 
                join dp in
                    (from e in E1
                     group e by e.ID into g
                     select new { ID = g.Key, Cnt = g.Count() })
                on c.ID
                equals dp.ID 
                into dpp from v in dpp.DefaultIfEmpty() 
                select new { c.ID, Cnt= v.Cnt ?? 0 };

How can i convert this to Lambda expression?

Improve this question
3
  • What do you mean by "convert this to Lambda expression"? Do you mean "convert it to a the form using the extension methods explicitly rather than a query expression"? Commented Feb 25, 2009 at 15:30
  • yes like using => and then writing the same query Commented Feb 25, 2009 at 16:01
  • You could use LINQPad to show the translation: add .AsQueryable() after the "D1", click on the lambda button and hit F5. Commented May 3, 2011 at 20:28

2 Answers 2

Reset to default
2

Here's one way to go. This kind-of matches the above.

var subquery = E1
  .GroupBy(e => e.Id)
  .Select(g => new { ID = g.Key, Cnt = g.Count()});
  //.ToList();

var q = D1
  .GroupJoin(
    subquery,
    c => c.ID,
    dp => dp.ID,
    (c, g) => new {ID = c.ID, Cnt=g.Any() ? g.First().Cnt : 0 }
  )

After refactoring, I came up with this:

var q = D1
  .GroupJoin(
    E1,
    d => d.ID,
    e => e.ID,
    (d, g) => new {ID = d.ID, Cnt = g.Count()}
  );

For comparision, the query comprehension form is:

var q = from d in D1
  join e in E1 on d.ID equals e.ID into g
  select new {ID = d.ID, Cnt = g.Count()};
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Is Groupjoin an equivalent to subquery? Please explain this point..thanks
Your subquery had a group by, then the "join .. into" (which is a group join) was regrouping. One does not need to group twice in order to produce the correct answer : Ids from D1 and a count of matches from E1.
msdn.microsoft.com/en-us/library/bb534297.aspx "GroupJoin produces hierarchical results, which means that elements from outer are paired with collections of matching elements from inner. GroupJoin enables you to base your results on a whole set of matches for each element of outer."
hanks for link..Ok so you mean a subquery was not needed there? Can you tell me in what example would a subquery be needed then assuming two tables are order and order details
In SQL, one might use subqueries instead of joins to avoid cartesian duplication of results. ORDER JOIN DETAILS yields Order1 several times. This can lead to double counting, etc. Since Linq can work with heirarchies instead of flat results, this need is much reduced.
|
1

Why would you want to convert it?

For complex queries like this one the query syntax you have used here is invariably clearer.

Improve this answer

1 Comment

I don't agree. GroupJoin is clearer as an extension method call, than in a query comprehension blob.