c# - 如何将SQL多个LEFT JOIN转换为Linq

我有下一个SQL查询：

SELECT Troox.*, Transfers.TranferDate, Users.UserName, Users_1.UserName
FROM((Troox LEFT JOIN Transfers ON Troox.TrooxID = Transfers.TrooxID)
LEFT JOIN Users ON Transfers.SenderID = Users.UserId)
LEFT JOIN Users AS Users_1 ON Transfers.ReceiverID = Users_1.UserId
where(Troox.UserId = 51)

而且我需要将此查询转换为C＃中的LINQ。

或如何使用Context.Database.SqlQuery执行此操作并获取响应。

帮帮我吧。

最佳答案

  internal class MappingData
 {
  public int TrooxID {get; set} // Here i declare only TrooxID  of Troox for sample,you can declare all of the fields  in Troox as  one by one
  public DateTime TranferDate{ get; set; }
  public string UserName{ get; set; }
  public string UserName1{ get; set; }

 }

using (var context = new MyDBContext())
 {
 var TrooxUserId= new SqlParameter("@TrooxUserId","51");
 string sqlQuery = @"SELECT Troox.TrooxID, Transfers.TranferDate, Users.UserName, Users_1.UserName as UserName1
                   FROM((Troox LEFT JOIN Transfers ON Troox.TrooxID = Transfers.TrooxID)
                   LEFT JOIN Users ON Transfers.SenderID = Users.UserId)
                   LEFT JOIN Users AS Users_1 ON Transfers.ReceiverID = Users_1.UserId where Troox.UserId =@TrooxUserId";
 var Results = db.Database.SqlQuery<MappingData>(sqlQuery,TrooxUserId).ToList();
 }