Tag: linq

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.

Unit Testing LINQ To LLBL

In many of the projects I’ve been working on we use an ORM tool called LLBL Gen Pro.  This tool provides a LINQ implementation that allows us to to select objects from our data source using LINQ query syntax.  An interesting obstacle we encountered was how to unit test code that used LINQ queries passed to LLBL.

For unit testing we did not want to actually hit our database, we wanted to isolate the code under test and ensure that it did what we intended.  Our first thought was to mock the IQueryable interface.  However, in the context of a LINQ query this didn’t seem feasible.   When mocking an object usually we provide a  mock implementation as a stand in and have it provide return values when needed and otherwise track the the object being’s mock usage.  It wasn’t immediately obvious how we would program a mocked IQueryable interface or what calls would be made against it during a particular LINQ query.

Instead of providing a mocked IQueryable interface we realized we could simply create an “in memory” test database of sorts for our tests.  Generic lists provide an ‘AsQueryable’ method that return their contents as an IQueryable.  In order to make our select logic testable we have our methods take the IQueryable to select against as a parameter.  When running for real the IQueryable parameter passed to the method is the data object that really hits the database, when under test  a generic list is passed in.  A very simplified example method might look like the below:

public List<SchoolEntity> GetList(SchoolFilter filter, IQueryable<SchoolEntity> schoolQ)
        {

            var result = from school in schoolQ
                         where school.Name == filter.Name
                                 && school.Colors == filter.Colors
                         select school;

            return result.ToList();

        }

In order to test we can create a list to pass in to our method:

[Test]
 public void TestSchoolColorFilter()
 {
 //--Arrange
 SchoolFilter filter = new SchoolFilter();
 filter.Colors = "Blue";

 List<SchoolEntity> schools = new List<SchoolEntity>();

 SchoolEntity school1 = new SchoolEntity();
 school1.Colors = "Blue";

 schools.Add(school1);

 SchoolEntity school2 = new SchoolEntity();
 school2.Colors = "Purple";

 //--Act
 List<SchoolEntity> retVal = GetList(filter, schools.AsQueryable());

 //--Assert
 Assert.AreEqual(1, retVal.Count());

 }

When actually running the method is called after a reference to an IQueryable object from the database is created and passed in like the below:

public List<SchoolEntity> GetList(SchoolFilter filter)
 {
         using (DataAdapter data = new DataAdapter())
         {
            LinqMetaData meta = new LinqMetaData(data);
            return this.GetList(filter, meta.School);

         }

 }

This allows us to take advantage of LLBL’s LINQ to LLBL functionality but still create unit tests that don’t have to cross the boundary to the database in order to run. So far it’s helped us to keep our code less coupled and more cohesive. The next hurde, and another post, is how we handle unit testing when LLBL specific LINQ extension methods are involved, aka Prefetch Paths!