Taras Kovalenko Taras Kovalenko - 17 days ago 6
C# Question

How to convert sql multimple LEFT JOIN to linq

I have next SQL query:

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)


And I need to convert this query to LINQ in C#.

Or how to execute this with Context.Database.SqlQuery, and get response.

Help me please do it.

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