Nesting a List in a custom class List via a LINQ SQL Query

Question

This is my code that retrieves information from the database about a list of Printer Drivers. The table has a list of Printer Drivers, as well as what servers they were found on.

 public List<PrinterDrivers> GetPrinterDriversFromCache()
        {
            using (dbPrintSimpleDataContext db = new dbPrintSimpleDataContext())
            {
                var q = from p in db.GetTable<tblPrinterDriverCache>()
                        where p.CacheGUID == mostRecentCacheID()
                        group p by p.PrinterDriver into g
                        select new PrinterDrivers
                        {
                            DriverName = g.Key,
                            InstalledOn = g.Where(x => x.PrinterDriver == g.Key).Select(x => x.PrinterServer).ToList(),
                            Usable = (g.Count() == Properties.Settings.Default.PrintServers.Count)
                        };
                return q.ToList();
            }
        }

What I am trying to return is a List that contains a property that has a List in it that contains what servers that printer driver exists on. I think that I'm up against the limit of my current LINQ SQL knowledge :(

The resultant List should contain:

DriverName = Printer driver name, in this case the group key (string)

InstalledOn = List (containing the list of servers that this printer driver was found on)

Usable = A simple bool check if the servers that it was found on is the same amount as the servers we have in the preferences file.

Thanks for the help :)

InstalledOn contains spurious results, that is, not results that I was expecting. I was expecting each grouped DriverName to contain a list of the print servers that it was installed on. The table in this query contains a list of print drivers and print servers, so, the query should only return the specified print driver along with a list of what servers have been found. Does that make sense? I'm a bit foggy because I've been coding all day — Lewis Cianci, Jul 21 '16 at 04:06

score 0 · Answer 1 · edited May 23 '17 at 12:14

Try this:

LINQ Lambda, Group by with list

The problem is that Linq does not know about ToList. Only part of the entire query is executed on the server as there is an extra ToList call before the final ToList call (Untested code below)

public List<PrinterDrivers> GetPrinterDriversFromCache()
    {
        using (dbPrintSimpleDataContext db = new dbPrintSimpleDataContext())
        {
            var q = (from p in db.GetTable<tblPrinterDriverCache>()
                    where p.CacheGUID == mostRecentCacheID()
                    group p by p.PrinterDriver.DriverName into g
                    select g
                    ).ToList().Select(g => new PrinterDrivers
                    {
                        DriverName = g.Key,
                        InstalledOn = g.Where(x => x.PrinterDriver == g.Key).Select(x => x.PrinterServer).ToList(),
                        Usable = (g.Count() == Properties.Settings.Default.PrintServers.Count)
                    });
            return q.ToList();
        }
    }

Translating the same pattern from the answer I linked, yours would be:

var q = db.GetTable<tblPrinterDriverCache>()
            .Where(p => p.CacheGUID == mostRecentCacheID())
            .Select(o => new { DriverName = o.DriverName, PrintServer = o.PrintServer })
            .GroupBy(g => g.DriverName)
            .ToList()
            .Select(g => new PrinterDrivers
                {
                    DriverName = g.Key,
                    InstalledOn = g.Select(p => p.PrinterServer).ToList(),
                    Usable = (g.Count() == Properties.Settings.Default.PrintServers.Count)
                }
            )
            .ToList();

This makes me feel like maybe I'm going about this the wrong way. Maybe there's a better way to get this information then trying to make it all happen in the one query. — Lewis Cianci, Jul 21 '16 at 04:16
Actually, If you are filtering first (you are), then returning the data, it isn't that bad. I can't think of a better way to do it and this should be fine. Any other way (short of something I am not thinking of) is going to have even more (N+1) queries. — Josh K, Jul 21 '16 at 04:18
Yeah okay. This code resulted in the same thing happening (I had to add "select g" after the " group p by p.PrinterDriver into g" line so the query would work). I might check the DB for duplicate data or something like that. — Lewis Cianci, Jul 21 '16 at 04:25

Nesting a List in a custom class List via a LINQ SQL Query

1 Answers1