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.ProfessionLevels
join p in db.Professions on pl.ProfessionId equals p.ProfessionId
join plt in db.ProfessionLevelTitles on pl.ProfessionLevelId 
equals plt.ProfessionLevelId into profLevels
from x in profLevels.DefaultIfEmpty()
join t in db.Titles on x.TitleId equals t.TitleId into titles
from y in titles.DefaultIfEmpty()
join l in db.Levels on pl.LevelId equals l.LevelId into levels
from z in levels.DefaultIfEmpty()
where pl.ProfessionLevelId == avatar.ProfessionLevelId
select 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. :)