Tag: llbl

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!

Using LINQ Expressions For Dynamic Predicates

I ‘ve recently been building a set of reports that all output data in a common format, and also group their data in a common format.   Each report needs to be able to have a date predicate based on day, week or month.    Using LINQ to LLBL Gen Pro (the ORM tool I’m currently using) I set up a base report class that has abstract methods defining the date predicates and query bodies, but share the grouping and output mechanisms.  What allows this is being able to return a LINQ Expression in the methods that define the date predicates.

The abstract date predicates end up looking like the below:

protected abstract Expression<Func<T, bool>> GetDayDatePredicate(int reportBuckets);

Once implemented the date predicate ends up being something like:

protected overrides Expression<Func<dataEntity>,bool>> GetDayDatePredicate(int reportBuckets)

{

return d => d.CreatedDate.Date > DateTime.Now.AddDays(-1 * reportBuckets).Date;

}

Having the predicates in method form serves two purposes: first the method name clearly states what the predicate is doing, (fairly straightforward above but bucketing by weeks is not nearly as clear), and second it allows the base class to create the query by doing something like the below:

public void RunQuery(int numberOfBuckets, IQueryable<T> q)
{

Expression<Func<T, bool>> datePred = GetDayDatePredicate(numberOfBuckets);
q = q.where(datePred);
RunQuery(q);

}

This allows a base class to manage running several reports if they all have similar structure.  When adding a report I just add a new implementation of the base class and fill in the abstract bits and then I know I have a report that our UI can use, since it relies on the common output the base class enforces on its children.  Don’t know if it’s all that good a solution, we’ll see.