Which ORM Supports this

Question

I have an optional part of query that needs to be executed on a certain condition. Here is the example code:

int cat = 1;
int UserID = 12;
string qry = "select * from articles";
if(cat > 0)
     qry += " where categoryID = " + cat;
if(UserID > 0)
     qry += " AND userid = " + UserID;  //The AND may be a WHERE if first condition is false

As you can see I have an if statement in the query. i am currently using Entity Framework and it does not support this kind of scenario. Is there an ORM out there that support this?

Edit I tried to dummy down the query. But I have about 20 "IF" statements and the querys are very long.

The ORMs I was looking at were:

• NHibernate
• LLBLGen
• Subsonic

I am open to any ORM. Thanks

Answer 1

You can probably do this with any LINQ provider, but I know the LightSpeed ORM supports it:

var query = UnitOfWork.Articles;
if (cat > 0)
  query = query.Where(a => a.CategoryId == cat);

Answer 2

I do this kind of thing in NHibernate all the time.

(I've done similar things in Rails. I'm kind of surprised that there are ORMs that don't support this.)

Answer 3

this can be done using linq to sql...

IQueryable<Article> query = yourDataContext.Articles;

if (catId > 0)
  query = query.Where(x => x.CategoryId == catId);

return query.ToList();

Answer 4

You can easily build queries in this way using NHibernate's HQL (Hibernate Query Language). It would be an almost identical implementation but I would personally use parameters.

public List<Article> GetCat(int cat)

    {
        string qry = "select ap from Article a";
        if(cat > 0)
             qry += " where a.categoryID = :cat";

        IQuery query = session.CreateQuery(qry).SetInt32("cat",cat);

        return query.List<Article>();
    }

This returns a List<> of Article objects ready for use.

Answer 5

NHibernate supports this using the Criteria API:

ICriteria criteria = session.CreateCriteria<Article>();

if (cat > 0)
    criteria.Add(Expression.Eq("categoryID", cat));

Answer 6

You can use the Predicate Builder and LINQ to NHibernate to generate dynamic query's like this:

//using Predicate Builder
        public List<Location> FindAllMatching(string[] filters)
        {
           var db = Session.Linq<Location>();
           var expr = PredicateBuilder.False<Location>(); //-OR-
           foreach (var filter in filters)
           {
               string temp = filter;
               expr = expr.Or(p => p.Name.Contains(temp));
           }

           return db.Where(expr).ToList();
         }

You get the advantage of Type Save Query's and Compiler check.

You can also use the same approach of predicate builder with Linq to Sql and Entity Framework.

EDIT: Added example. It could be something like get all the locations matching N regions of the world, where the user select the regions he want to see, we don't know how many the user will select, we must build the (OR) expression on the fly, you can do something like:

public ActionResult Action(string[] filters)
{
    /*This values are provided by the user, maybe its better to use
     an ID instead of the name, but for the example is OK.
     filters will be something like : string[] filters = {"America", "Europe", "Africa"};
    */
    List<Location> LocationList = FindAllMatchingRegions(filters);
    return View(LocationList);
}

public List<Location> FindAllMatchingRegions(string[] filters)
        {
            var db = Session.Linq<Location>();
            var expr = PredicateBuilder.False<Location>(); //-OR-
            foreach (var filter in filters)
            {
                string temp = filter;
                expr = expr.Or(p => p.Region.Name == filter);
            }

            return db.Where(expr).ToList();
        }

You can Nest Predicates for a complex scenarios like this:

If you want to do something like

p => p.Price > 99 &&
     p.Price < 999 &&
     (p.Description.Contains ("foo") || p.Description.Contains ("far"))

you can build:

var inner = PredicateBuilder.False<Product>();
inner = inner.Or (p => p.Description.Contains ("foo"));
inner = inner.Or (p => p.Description.Contains ("far"));

var outer = PredicateBuilder.True<Product>();
outer = outer.And (p => p.Price > 99);
outer = outer.And (p => p.Price < 999);
outer = outer.And (inner);

And use it like :

var pr = db.Products.Where(outer).ToList();

The Predicate Builder Source and examples are available at http://www.albahari.com/nutshell/predicatebuilder.aspx

Answer 7

No love for LLBLGen? Well it can can do it too.

Using the 'adapter' style:

RelationPredicateBucket filters = new RelationPredicateBucket();
if (cat > 0)
    filters.Predicate.Add(Article.Fields.CategoryID == cat);
if (userId > 0)
    filters.Predicate.Add(Article.Fields.UserID == userId);
// And so on.

var adapter = new DataAccessAdapter();
var results = new EntityCollection<Article>(new ArticleFactory());
adapter.FetchEntityCollection(results, filters);

I would suspect most ORMs should be able to do this pretty easily.

Answer 8

As it was already mentioned here, LINQ allows to extend any query by simply adding more criteria to it.

var query = 
  from x in xs 
  where x==1
  select x;

if (mustAddCriteria1)
  query = 
    from x in query 
    where ... // criteria 1
    select x;

if (mustAddCriteria2)
  query = 
    from x in query 
    where ... // criteria 2
    select x;

And so on. This approach works just perfectly. But likely, you know that compilation of LINQ queries is pretty expensive: e.g. Entity Framework can compile just about 500 relatively simple queries per second (see e.g. ORMBattle.NET).

On the other hand, many ORM tools support compiled queries:

• You pass an IQueryable instance to some Compile method, and get a delegate allowing to execute it much faster later, because no recompilation would occur in this case.

But if we'd try to use this approach here, we immediately notice that our query is actually dynamic: IQueryable we execute each time might differ from the previous one. Presence of query parts there is determined by values of external parameters.

So can we execute such queries as compiled without e.g. explicit caching?

DataObjects.Net 4 support so-called "boolean branching" feature. It implies any constant boolean expression is evaluated during query compilation and its actual value is injected into SQL query as true boolean constant (i.e. not as parameter value or as an expression utilizing parameters).

This feature allows to generate different query plans dependently on values of such boolean expressions with ease. E.g. this code:

  int all = new Random().Next(2);
  var query = 
    from c in Query<Customer>.All
    where all!=0 || c.Id=="ALFKI"
    select c;

will be executed using two different SQL queries, and thus - two different query plans:

• Query plan based on index seek (quite fast), if all==0
• Query plan based on index scan (quite slow), if all!=0

Case when all==null, SQL query:

SELECT
  [a].[CustomerId],
  111 AS [TypeId] ,
  [a].[CompanyName]
FROM
  [dbo].[Customers] [a]
WHERE(( CAST( 0 AS bit ) <> 0 ) OR( [a].[CustomerId] = 'ALFKI' ) );

Case when all==null, query plan:

|--Compute Scalar(DEFINE:([Expr1002]=(111)))
   |--Clustered Index Seek(OBJECT:([DO40-Tests].[dbo].[Customers].[PK_Customer] AS [a]), SEEK:([a].[CustomerId]=N'ALFKI') ORDERED FORWARD)

Second case (when all!=null), SQL query:

SELECT
  [a].[CustomerId],
  111 AS [TypeId] ,
  [a].[CompanyName]
FROM
  [dbo].[Customers] [a]
WHERE(( CAST( 1 AS bit ) <> 0 ) OR( [a].[CustomerId] = 'ALFKI' ) );
-- Notice the ^ value is changed!

Second case (when all!=null), query plan:

|--Compute Scalar(DEFINE:([Expr1002]=(111)))
   |--Clustered Index Scan(OBJECT:([DO40-Tests].[dbo].[Customers].[PK_Customer] AS [a]))
-- There is index scan instead of index seek!

Note that almost any other ORM would compile this to a query utilizing integer parameter:

SELECT
  [a].[CustomerId],
  111 AS [TypeId] ,
  [a].[CompanyName]
FROM
  [dbo].[Customers] [a]
WHERE(( @p <> 0 ) OR ( [a].[CustomerId] = 'ALFKI' ) );
--      ^^ parameter is used here

Since SQL Server (as well as most of databases) generates a single version of query plan for a particular query, it has the only option in this case - generate a plan with index scan:

|--Compute Scalar(DEFINE:([Expr1002]=(111)))
   |--Clustered Index Scan(OBJECT:([DO40-Tests].[dbo].[Customers].[PK_Customer] AS [a]), WHERE:(CONVERT(bit,[@p],0)<>(0) OR [DO40-Tests].[dbo].[Customers].[CustomerId] as [a].[CustomerId]=N'ALFKI'))

Ok, that was a "quick" explanation of usefulness of this feature. Let's return back to your case now.

Boolean branching allows to implement it in very simple fashion:

var categoryId = 1;
var userId = 1;

var query = 
  from product in Query<Product>.All
  let skipCategoryCriteria = !(categoryId > 0)
  let skipUserCriteria = !(userId > 0)
  where skipCategoryCriteria ? true : product.Category.Id==categoryId
  where skipUserCriteria ? true : 
  (
    from order in Query<Order>.All
    from detail in order.OrderDetails
    where detail.Product==product
    select true
  ).Any()
  select product;

The example differs from yours, but it illustrates the idea. I used different model mainly to be able to test this (my example is based om Northwind model).

This query is:

• Not a dynamic query, so you can safely pass it to Query.Execute(...) method to get it executed as compiled query.
• Nevertheless each its execution will lead to the same result as if this would be done with "appending" to IQueryable.