Which ORM supports this

Question

I have an optional part of the request that must be executed in a certain state. Here is a sample 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, there is an if statement in the request. I am currently using Entity Framework and do not support such a scenario. Are there ORMs that support this?

Edit I tried to stub the request. But I have about 20 IF statements, and the queries are very long.

The ORMs I was looking at were:

• NHibernate
• LLBLGen
• Subsonic

I am open to any ORM. Thanks

Answer 1

As already mentioned here, LINQ allows you to expand 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 fine. But, most likely, you know that compiling LINQ queries is quite expensive: for example. Entity Framework can compile about 500 relatively simple queries per second (see, for example, ORMBattle.NET ).

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

• You pass an IQueryable instance for some Compile method and get a delegate that allows you to execute it much faster later, because in this case there will be no recompilation.

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

So, we can execute such requests as compiled without eg. explicit caching?

DataObjects.Net 4 supports the so-called Boolean branching function. This means that any constant Boolean expression is evaluated at the time the query is compiled, and its actual value is entered into the SQL query as a true Boolean constant (i.e. Not as a parameter value or as an expression using parameters).

This function makes it easy to generate various query plans depending on the values ​​of such Boolean expressions. For example. 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, therefore, two different query plans:

• Query plan based on index search (pretty fast), if all == 0
• Query plan based on index scan (rather 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 everything == 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! 

The second case (when everything! = Null), the 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 will compile this for a query using an 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 (like most databases) generates a single version of a query plan for a specific query, it has the only option in this case - create a plan with indexing:

 |--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')) 

Well, that was a “quick” explanation of the usefulness of this feature. Now get back to your business.

Logical branching makes it very easy to implement:

 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 is different from yours, but it illustrates the idea. I used another model mainly to test it (my example is based on the Northwind model).

This request:

• This is not a dynamic query, so you can safely pass it to the Query.Execute(...) method Query.Execute(...) that it Query.Execute(...) as a compiled query.
• However, each execution will produce the same result as if it were done with an “add” to IQueryable .

Answer 2

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 3

NHibernate supports this with the criteria API:

 ICriteria criteria = session.CreateCriteria<Article>(); if (cat > 0) criteria.Add(Expression.Eq("categoryID", cat)); 

Answer 4

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

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

Answer 5

I do all the time at NHibernate.

(I did similar things in Rails. I am very surprised that there are ORMs that do not support this.)

Answer 6

You can easily create queries this way using NHibernate HQL (Hibernate Query Language). It will be an almost identical implementation, but I would personally use the 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 object, ready for use.

Answer 7

You can use Predicate Builder and LINQ to NHibernate to generate a dynamic query as follows:

 //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 benefit of a check such as "Save query and compiler."

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

EDIT: Added example. It can be something like getting all the locations corresponding to N regions of the world, where the user selects the regions that he wants to see, we don’t know how many users will choose, we have to build an expression (OR) 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 use Nest Predicates for 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(); 

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

Answer 8

No love for LLBLGen? Well, he 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 that most ORMs should be able to do this quite easily.