Adventures in LINQ to SQL

I've been playing around a little with LINQ to SQL for Perenthia and wanted to share a kind of nasty query I just wrote. In Perenthia the typical RPG classes are called Professions. To track these professions I have the following database tables:

The players table links over to the rad_ProfessionLevels table via the ProfessionLevelId foreign key. The rad_ProfessionLevels table also links over the rad_Levels table on the LeveId foreign key. This layout allows me to have professions, define custom names for the levels and give certain levels titles.

When loading up a player record I need to go and get the level number, level name, profession name and title prefix and suffix values from the database. Since I am using LINQ to SQL for my entity classes I wrote the following query to retrieve this information:

 

var titleQuery = from pl in db.ProfessionLevelsjoin p in db.Professions on pl.ProfessionId equals p.ProfessionIdjoin plt in db.ProfessionLevelTitles on pl.ProfessionLevelId equals plt.ProfessionLevelId into profLevelsfrom x in profLevels.DefaultIfEmpty()join t in db.Titles on x.TitleId equals t.TitleId into titlesfrom y in titles.DefaultIfEmpty()join l in db.Levels on pl.LevelId equals l.LevelId into levelsfrom z in levels.DefaultIfEmpty()where pl.ProfessionLevelId == avatar.ProfessionLevelIdselect new { p.ProfessionName, z.LevelNumber, pl.LevelName, y.Prefix, y.Suffix };
 

It's kind of a nasty beast but produces the following SQL query:

 

SELECT [t1].[ProfessionName],[t4].[LevelNumber] AS [LevelNumber],[t0].[LevelName], [t3].[Prefix] AS [Prefix],[t3].[Suffix] AS [Suffix]FROM [dbo].[rad_ProfessionLevels] AS [t0]INNER JOIN [dbo].[rad_Professions] AS [t1]ON [t0].[ProfessionId] = [t1].[ProfessionId]LEFT OUTER JOIN [dbo].[rad_ProfessionLevelTitles] AS [t2]ON [t0].[ProfessionLevelId] = [t2].[ProfessionLevelId]LEFT OUTER JOIN [dbo].[rad_Titles] AS [t3]ON [t2].[TitleId] = [t3].[TitleId]LEFT OUTER JOIN [dbo].[rad_Levels] AS [t4]ON [t0].[LevelId] = [t4].[LevelId]WHERE [t0].[ProfessionLevelId] = @p0
 

The @p0 value is the ProfessionLevelId value stored with the player record.

The LINQ query is nasty because of the three outer joins I have to perform because not all profession levels have titles. It doesn't look pretty but it does work; as I get into more LINQ writing I will probably find a better way to write this but at least I got my data. :)

1/25/2008