I have spent the last three days writing LINQ qeueries over object collections and have to come to rely on LINQ pretty heavily for most query operations. I have played around some with LINQ to SQL and LINQ to Entities but I mostly use it to just query over some good old fashion collections like so:

var groups = proposal.Lines.Where(kvp => kvp.Key.Equals(key.Quarter))

                                           .Select(kvp => kvp.Value.Where(l => l.AvailType.Spot == "N" && l.AvailType.Type == "RE"))

                                           .FirstOrDefault()

                                           .GroupBy(l => new { l.NetworkId, l.UnitLength, l.BrandId });

            List<TimePeriod> weeks = proposal.GetWeeksFromOrderFlightDates(key.Quarter);

            foreach (var group in groups)

            {

                BudgetRow row = AddBudgetRow(budgetDefinition,

                    new List<string>(new string[] { group.Key.NetworkId.ToString(), group.Key.UnitLength.ToString(), group.Key.BrandId.ToString() }),

                    budgetRows, BudgetDefinitionType.WeeklyNetworkAvailTypeImps);

                foreach (var week in weeks)

                {

                    var units = group.SelectMany(l => l.Units.Where(u => week.contains(u.AirDate)));

                    row.setRawValue(week, units.Sum(u => (decimal?)u.Demo1Imps));

                    row.setLockedValue(week, units.Where(u => u.IsLockedOutOfUnitAllocation).Sum(u => (decimal?)u.Demo1Imps));

                }

            }

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