CODEWITHSUNDEEP
linqc#-4.0entity-framework-4linq-to-entities
Mathew
Mathew

Reputation: 132

Linq Query for Corresponding SQL

Below is my SQL Statement, I am Unable to Convert to LINQ 

select o.CustId,c.CustName,Count(o.CustId) as Total_Orders from Customer c 
inner join Orders o on c.CustId = o.CustId group by o.CustId,c.CustName

Upvotes: 1

Views: 316

Answers (4)

Tilak
Tilak

Reputation: 30698

Following is the LINQ query (db is EntityFramework DBContext).

var query =  from c in db.Customers
             join o in db.Orders on c.CustId equals o.CustId into g
             groupby new { CustID = c.CustId, CustName=c.CustName } into gs
             select new { CustID = gs.Key.CustId, CustName=gs.Key.CustName, Count= gs.Count() } into gs

Upvotes: 0

Vlad Bezden
Vlad Bezden

Reputation: 89527

Please try:

    var query = from c in Customer
            join o in Orders 
            on c.CustId 
            equals o.CustId 
            group x by new {o.CustId, c.CustName} 
            into g
                select new 
                {
                  g.Key.CustId,
                  g.Key.CustName,
                  Total_Orders = g.Count()
                };

Upvotes: 1

lante
lante

Reputation: 7336

var query = from c in context.Customers
            select new
            {
                c.CustId,
                c.CustName,
                Orders = c.Orders.Count(),
            };

Upvotes: 0

Ray Saltrelli
Ray Saltrelli

Reputation: 4218

What about something like this?

var query = db.Customers.Select(c => new 
                {
                    CustId = c.CustId,
                    CustName = c.CustName,
                    Total_Orders = db.Orders.Where(o => o.CustId == c.CustId).Count()
                }
            );

Upvotes: 2

Related Questions