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