All IQueryables Are Not The Same

On my current project we use an Object Relational Mapping (ORM) tool named LLBL Gen Pro.  LLBL has a really nice LINQ implementation over it which allows for writing queries much more naturally and in a strongly typed environment.  nHibernate has long had a query language that looked similar to SQL named HQL (Hibernate Query Language).  Unlike LINQ, HQL was weakly typed so changes to types wouldn’t result in compile time errors.  Since LINQ is strongly typed this is not an issue.  When we decided on LLBL Gen Pro it was before nHibernate Linq existed.  We liked being able to use LINQ for querying since could leverage the learning from using LINQ to Objects from a syntactic perspective.

The above is why we choose LLBL and we have been please with the LINQ implementation.  When using LINQ, however, it is important to keep in mind who is the daddy of an IQueryable or IEnumerable you are working with. 

I recently came across a scenario like the below where I retrieved a result set  and then further refine the result set.


//--query against IQueryable that is LLBL Entity
var codes = from c in companyQ
                 where c.CompanyID == companyID
                 select c.PromoCodes;


if (codes.Count() > 0)
            {
               //--get the freetrial from result set
                var freeTrial = codes.Where(c => c.CodeType == (int)CodeTypeEnum.FreeTrial).SingleOrDefault();

              //--get list prepaid from result set
                var prePaids = codes.Where(c => c.CodeType == (int)CodeTypeEnum.PrePaid);


      }

What I didn’t expect was that the second refining, which I meant to happen to the result set in memory, actually spawned another query in the database.  Once I realized this it made sense, since the DataProvider for the IQueryable I was working with was the LLBL Gen Pro Implementation, and it is supposed to query against the database.   To do what I intended I converted the result set to a List before doing my refining which allowed the DataProvider to be the DataProvider underpinning LinqToObjects.  This then did what I wanted and queried against the data in memory.


//--query against IQueryable that is LLBL Entity
var codesFromDB = from c in companyQ
                 where c.CompanyID == companyID
                 select c.PromoCodes;

//--convert to list based Queryable so 
//   further refining done in memory

var codes = codesFromDB.ToList().AsQueryable();

if (codes.Count() > 0)
            {
               //--get the freetrial from result set
                var freeTrial = codes.Where(c => c.CodeType == (int)CodeTypeEnum.FreeTrial).SingleOrDefault();

              //--get list prepaid from result set
                var prePaids = codes.Where(c => c.CodeType == (int)CodeTypeEnum.PrePaid);


      }

The lesson I learned here was keep in mind who owns an IQueryable or IEnumerable when using it, or you might end up with some unexpected side effects.