SQLiteException and SQLite error near "(": syntax error with Subsonic ActiveRecord

Question

I ran into an interesting error with the following LiNQ query using LiNQPad and when using Subsonic 3.0.x w/ActiveRecord within my project and wanted to share the error and resolution for anyone else who runs into it.

The linq statement below is meant to group entries in the tblSystemsValues collection into their appropriate system and then extract the system with the highest ID.

from ksf in KeySafetyFunction where ksf.Unit == 2 && ksf.Condition_ID == 1 
   join sys in tblSystems on ksf.ID equals sys.KeySafetyFunction
   join xval in (from t in tblSystemsValues 
group t by t.tblSystems_ID into groupedT 
  select new 
  { 
    sysId = groupedT.Key, 
    MaxID = groupedT.Max(g=>g.ID), 
    MaxText = groupedT.First(gt2 => gt2.ID ==  
                groupedT.Max(g=>g.ID)).TextValue,
    MaxChecked = groupedT.First(gt2 => gt2.ID ==  
                groupedT.Max(g=>g.ID)).Checked
   }) on sys.ID equals xval.sysId
select new {KSFDesc=ksf.Description, sys.Description, xval.MaxText, xval.MaxChecked}

On its own, the subquery for grouping into groupedT works perfectly and the query to match up KeySafetyFunctions with their System in tblSystems also works perfectly on its own.

However, when trying to run the completed query in linqpad or within my project I kept running into a SQLiteException SQLite Error Near "("

First I tried splitting the queries up within my project because I knew that I could just run a foreach loop over the results if necessary. However, I continued to receive the same exception!

I eventually separated the query into three separate parts before I realized that it was the lazy execution of the queries that was killing me. It then became clear that adding the .ToList() specifier after the myProtectedSystem query below was the key to avoiding the lazy execution after combining and optimizing the query and being able to get my results despite the problems I encountered with the SQLite driver.

// determine the max Text/Checked values for each system in tblSystemsValue
var myProtectedValue = from t in tblSystemsValue.All()
        group t by t.tblSystems_ID into groupedT
           select new {
             sysId = groupedT.Key,
             MaxID = groupedT.Max(g => g.ID),
             MaxText = groupedT.First(gt2 => gt2.ID ==groupedT.Max(g => g.ID)).TextValue,
             MaxChecked = groupedT.First(gt2 => gt2.ID ==groupedT.Max(g => g.ID)).Checked};
// get the system description information and filter by Unit/Condition ID
var myProtectedSystem = (from ksf in KeySafetyFunction.All()
        where ksf.Unit == 2 && ksf.Condition_ID == 1
          join sys in tblSystem.All() on ksf.ID equals sys.KeySafetyFunction
             select new {KSFDesc = ksf.Description, sys.Description, sys.ID}).ToList();
// finally join everything together AFTER forcing execution with .ToList()
 var joined = from protectedSys in myProtectedSystem
         join protectedVal in myProtectedValue on protectedSys.ID equals protectedVal.sysId
           select new {protectedSys.KSFDesc, protectedSys.Description, protectedVal.MaxChecked, protectedVal.MaxText};
  // print the gratifying debug results
  foreach(var protectedItem in joined)
        {
            System.Diagnostics.Debug.WriteLine(protectedItem.Description + ", " + protectedItem.KSFDesc + ", " + protectedItem.MaxText + ", " + protectedItem.MaxChecked);
        }

Answer 1

Avoid lazy evaluation by forcing an early execution with .ToList() on one of the components of the final query. The results will go into memory so try to make sure you are choosing a small set of data and don't force an unbounded query or gigantic query into a list.