c# 4.0 -对应SQL的Linq查询

Question

下面是我的SQL语句，我无法转换为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

Answer 1

这样怎么样?

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

Answer 2

请尝试:

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

Answer 3

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

Answer 4

下面是LINQ查询(db是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