Partager via

Linq supports multi-query

  • Article

Turns out Linq supports getting sub-queries along with the main query.  It does though through what they call a SelectMany action.  You can also use the let clause to do the same thing. 

I've got a part of my code for converting the database to Wix that currently performs a 2nd query based on results from the first query.  I thought I would put a little effort into optimizing this part of the code since most of my time is spent waiting on the 2 queries.

I did a time trial using 3 options:

  • To a SQL server
  • To a SQL server with 1 query only
  • To a DataSet

I started out as a bunch of small queries for each part I needed to convert.  This was the easiest way to incrementally build the full functionality.  This took about 17 minutes to convert the 8012 items.  I then rolled up all of the small queries into the top most query.  This produced the most ugly looking query anyone has probably ever seen.  (It is tacked on to the bottom of this post)  Anyhow, this only took 5 minutes.  A huge improvement over the small queries.

I assumed this meant that the round trip time for each query was eating up all of the time.  So thinking this I created a hybrid of the 2 in that I would use a DataSet to cache all of the tables I needed for all of the original small queries.  I guessed that this would run almost as fast as the single query version since all of the data was now cached and the round trip cost would be much lower.  This method took 9 minutes.  Although I think this method is a bit more readable.

I wonder if there is a way to make this giant query smaller or more efficient.

             var components = from c in _db.Components
                             join cg in _db.ComponentGroups on c.ComponentGroupId equals cg.Id
                             join d in _db.Directories on c.DirectoryId equals d.Id
                             let ShortcutsPerComponent = from s in _db.Shortcuts
                                                         join sp in _db.Shortcut_Platform_XRefs on s.Id equals sp.ShortcutId
                                                         join plat in _db.Platforms on sp.PlatformId equals plat.Id
                                                         where ((c.Id == s.ComponentId) && (s.Enabled))
                                                         select new ShortcutTuple
                                                         {
                                                             Platform = plat.Name,
                                                             Shortcut = s
                                                         }
                             let sortedFiles = from fi in _db.Files
                                               join fileXplat in _db.FilePlatformXrefs on fi.Id equals fileXplat.FileId
                                               join plat in _db.Platforms on fileXplat.PlatformId equals plat.Id
                                               where fi.ComponentId == c.Id
                                               let namespaces = from hn in _db.HelpNamespaces
                                                                where (hn.File_Collection == fi.Id)
                                                                select hn
                                               let HelpFiles = from hf in _db.HelpFiles
                                                               where (hf.File_HxS == fi.Id) && (hf.Enabled)
                                                               let index = (from f in _db.Files
                                                                            where (f.Id == hf.File_HxI) && (f.Enabled)
                                                                            select new HelpFileTuple
                                                                            {
                                                                                Enabled = f.Enabled,
                                                                                FileName = f.SourceFileName,
                                                                                Guid = f.Component.ComponentGUID
                                                                            }).SingleOrDefault()
                                                               select new HelpFileData
                                                               {
                                                                   Id = hf.HelpFileKey,
                                                                   FileName = hf.HelpFileName,
                                                                   Lang = hf.LangID,
                                                                   Index = index,
                                                                   HxR = new HelpFileTuple
                                                                   {
                                                                       Enabled = hf.File_HxRFile.Enabled,
                                                                       FileName = hf.File_HxRFile.SourceFileName,
                                                                       Guid = hf.File_HxRFile.Component.ComponentGUID
                                                                   },
                                                                   HxQ = new HelpFileTuple
                                                                   {
                                                                       Enabled = hf.File_HxQFile.Enabled,
                                                                       FileName = hf.File_HxQFile.SourceFileName,
                                                                       Guid = hf.File_HxQFile.Component.ComponentGUID
                                                                   },
                                                                   Samples = new HelpFileTuple
                                                                   {
                                                                       Enabled = hf.File_SamplesFile.Enabled,
                                                                       FileName = hf.File_SamplesFile.SourceFileName,
                                                                       Guid = hf.File_SamplesFile.Component.ComponentGUID
                                                                   }
                                                               }
                                               select new FileTuple
                                               {
                                                   SourceFileName = fi.SourceFileName,
                                                   Guid = fi.Component.ComponentGUID,
                                                   Source = String.Concat(fi.Source.Path, fi.SourcePath),
                                                   Platform = plat.Name,
                                                   Key = fi.IsKeyFile,
                                                   Id = fi.Id,
                                                   Compressed = fi.UnCab,
                                                   ComponentId = c.Id,
                                                   NameSpaces = namespaces,
                                                   HelpFiles = HelpFiles
                                               }
                             let conditions = from cxos in _db.ComponentOsXrefs
                                              join os in _db.OsDefinitions on cxos.OsId equals os.Id
                                              where cxos.ComponentId == c.Id
                                              select os.Condition
                             let directoryIds = from f in _db.Features
                                                join di in _db.Directories on f.KeyComponentDirectoryId equals di.Id
                                                where f.KeyComponentGuid == c.ComponentGUID
                                                select di.DirectoryName
                             select new
                             {
                                 Id = c.Id,
                                 WixFileBaseName = cg.WixFileBaseName,
                                 Guid = c.ComponentGUID,
                                 Attributes = c.Attributes,
                                 Directory = d.DirectoryName,
                                 Condition = c.Condition,
                                 Shortcuts = ShortcutsPerComponent,
                                 Files = sortedFiles,
                                 OsConditions = conditions,
                                 CreateFolders = directoryIds
                             };

Comments