Adventures in LINQ to SQL

by Cameron Albert 25. January 2008 22:14

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. :)

Tags: , , ,

Game Development | General | Perenthia PBBG

Comments

Powered by BlogEngine.NET 1.5.0.7
Modified Theme by Mads Kristensen

About the Author

CameronAlbert.com I am Senior Software Development Consultant specializing in Silverlight, WPF and the Microsoft .NET Framework. 

I have released an iPhone game called the Adventures of Puppyman that was built using ExEn and am currently working on a WP7 and iPhone version of Perenthia soon to be released.

View Cameron Albert's profile on LinkedIn
See how we're connected

Follow cameronalbert on Twitter

 

Recommended Books

Silverlight 4 Business Application Development - Beginner's Guide:

http://www.packtpub.com/microsoft-silverlight-4-business-application-development-beginners-guide/book

Microsoft Silverlight 4 Business Application Development: Beginner’s Guide