Entity Framework – IQueryable LEFT JOIN

Entity Framework – IQueryable LEFT JOIN

 
   public IQueryable<PlayerDetail> getPlayerDetail()
   {
      IQueryable<PlayerDetail> query = null;
 
      query = from p in context.PLAYERs
         join n in context.NATIONs on p.PLAYER_NATION equals n.NATION_ID into q
         from n in q.DefaultIfEmpty()
         select new PlayerDetail {
            PLAYER_ID = p.PLAYER_ID,
            PLAYER_NAME = p.PLAYER_NAME,
            PLAYER_NATION = n.NATION_NAME,
            PLAYER_TEAM = "",
            TEAM_ID = p.PLAYER_TEAM,
            NATION_ID = p.PLAYER_NATION,
         };
 
      query = from p in query
         join t in context.TEAMs on p.TEAM_ID equals t.TEAM_ID into q
         from t in q.DefaultIfEmpty()
         select new PlayerDetail
         {
            PLAYER_ID = p.PLAYER_ID,
            PLAYER_NAME = p.PLAYER_NAME,
            PLAYER_NATION = p.PLAYER_NATION,
            PLAYER_TEAM = t.TEAM_NAME,
            TEAM_ID = p.TEAM_ID,
            NATION_ID = p.NATION_ID
         };
 
      return query;
 
      /*
         SELECT [dbo].[PLAYER].[PLAYER_ID] AS PLAYER_ID
         ,[dbo].[PLAYER].[PLAYER_NAME] AS PLAYER_NAME
         ,[dbo].[NATION].[NATION_NAME] AS PLAYER_NATION
         ,[dbo].[TEAM].[TEAM_NAME] AS PLAYER_TEAM
         FROM [dbo].[PLAYER]
         LEFT JOIN [dbo].[NATION]
         ON [dbo].[PLAYER].[PLAYER_NATION] = [dbo].[NATION].[NATION_ID]
         LEFT JOIN [dbo].[TEAM]
         ON [dbo].[PLAYER].[PLAYER_TEAM] = [dbo].[TEAM].[TEAM_ID]
         ORDER BY [dbo].[PLAYER].[PLAYER_ID]
      */
   }
 
   public class PlayerDetail
   {
      public Int32 PLAYER_ID { get; set; }
      public string PLAYER_NAME { get; set; }
      public string PLAYER_NATION { get; set; }
      public string PLAYER_TEAM { get; set; }
      public Int32 NATION_ID { get; set; }
      public Int32 TEAM_ID { get; set; }
   }