CODEWITHSUNDEEP
c#.netentity-frameworklinq-to-entitiesentity-framework-4
Greg
Greg

Reputation: 34798

entity framework - how can I implement this SQL in the abbreviated EF linq

Can anyone help out with what the C# code would be to implement this SQL as Entity Framework Linq in the abbreviated form? (e.g. where you have the "." notation such as xxx.where(... etc)

SELECT PN.Name, Sum(U.Amount)
FROM Usages as U, ProcessNames as PN
WHERE PN.Id == U.ProcessNameId 
   AND U.Datetime BETWEEN '2010-01-08' AND '2010-10-11'
Group By PN.Name

Upvotes: 3

Views: 2566

Answers (2)

Steve Michelotti
Steve Michelotti

Reputation: 5213

Try this (I don't have your code so I'm getting no compiler help):

from u in context.Usages
join pn in context.ProcessNames on pn.Id equals u.ProcessNameId
where u.Datetime >= new DateTime(2010, 1, 8) && u.Datetime <= new DateTime(2010, 10, 11)
group pn by pn.Name into g
select new { Name = pn.Name , sum = g.Sum(u => u.Amount) };

That is the query expression version of it. To get the lambda based syntax (as I believe you are asking for this question), put the query into LinqPad and run it. Then click on the lambda tab in LinqPad and it will show you that syntax of the above query as if you had written it with lambda expressions (i.e., where you have the "." notation) and not a query expression.

Upvotes: 1

Morteza Manavi
Morteza Manavi

Reputation: 33206

Method-Based Query:
To implement this in lambda we need to leverage Queryable.GroupJoin:

var query = context.ProcessNames
    .GroupJoin(context.Usages
                      .Where(u => u.Datetime >= new DateTime(2010, 1, 8) ) 
                                  && u.Datetime <= new DateTime(2010, 10, 11),
               pn  => pn.Id,
               u => u.ProcessNameId, 
               (pn, usages) => new { Name = pn.Name, 
                                     Sum = usages.Sum(u => u.Amount) });


Query Expression:
And the very same query in query expression syntax:

var query = 
    from pn in context.ProcessNames
    join u in context.Usages
                     .Where(u => u.Datetime >= new DateTime(2010, 1, 8) ) 
                                 && u.Datetime <= new DateTime(2010, 10, 11),
    on pn.Id 
    equals u.ProcessNameId 
    into g                      
    select new { Name = pn.Name, 
                 Sum = g.Sum(u => u.Amount) };


Check the Generated SQL:
To verify that these queries give you your desired Sql command at runtime you can do this:

string sqlCommand = ((ObjectQuery)query).ToTraceString();


More Examples:
For some good examples on GroupJoin, please take a look at these:

Method-Based Query Syntax Examples: Join Operators
Query Expression Syntax Examples: Join Operators

Upvotes: 5

Related Questions