有人能帮我把SQL查询转换成LINQ表达式吗

Question

有人能帮我把下面的SQL查询转换成LINQ表达式吗？

select student.StudentID,
       student.FirstName + ' ' + student.MiddleInitial + ' ' + student.LastName, 
       student.OpenDate,     
       acct.Balance    
from Student student
inner join 
(Select StudentID, Sum(Amount) as Balance 
from Account 
Group by StudentID) as acct on student.StudentID = acct.StudentID

---

以下LINQ表达式有效！非常感谢你，菲尔！！！

var results = (from s in Students     
          join a in Accounts on s.StudentID equals a.StudentID     
          group a by new                  
             {s.StudentID, s.FirstName, s.MiddleInitial, s.LastName, s.OpenDate}             
             into z     
             select  new     
             {   StudentID = z.Key.StudentID,         
                 FirstName = z.Key.FirstName, 
                 MiddleInitial = z.Key.MiddleInitial, 
                 LastName = z.Key.LastName,         
                 OpenDate = z.Key.OpenDate,         
                 Balance = z.Sum(a=> a.Amount)       
             }
             ); 

谢谢！

Answer 1

试试看？

var stu = 
    from s in db.Students
    join a in db.Account on s.StudentID equals a.StudentID
    group a by new 
                {s.StudentID, s.FirstName, s.MiddleInitial, s.LastName, s.OpenDate} 
            into z
    select new 
    {   StudentID = z.Key.StudentID,
        FullName = string.Format("{0} {1} {2}",z.Key.FirstName, z.Key.MiddleInitial, z.Key.LastName),
        OpenDate = z.Key.OpenDate,
        Balance = z.Sum(a=>a.Amount)
    };