{"id":22,"date":"2014-08-18T10:08:00","date_gmt":"2014-08-18T10:08:00","guid":{"rendered":""},"modified":"2016-01-30T23:30:31","modified_gmt":"2016-01-30T23:30:31","slug":"nhibernate-generating-where-in-or-queries","status":"publish","type":"post","link":"https:\/\/tpodolak.com\/blog\/2014\/08\/18\/nhibernate-generating-where-in-or-queries\/","title":{"rendered":"NHibernate – generating WHERE IN \u2026 OR \u2026 queries"},"content":{"rendered":"

Let’s assume that we have a simple table GL_Task<\/i> which looks like this
\n\"Table\"<\/a>
\nI was asked to rewrite simple SQL<\/i> query<\/p>\n

\r\nSELECT Id,Name,IdProject FROM GL_Task WHERE Name = 'First task' OR Id IN (3,4 \/*more id's to come*\/)\r\n<\/pre>\n
using NHibernate’s QueryOver API. As simple as it may seem, solution for this particular problem is not straightforward. My first (not so clever) attempt was simply combining WhereRestrictionOn<\/i> and Where<\/i> clause<\/p>\n
\r\nvar result = session.QueryOver().Where(val=> val.Name == \"First task\")\r\n                                      .WhereRestrictionOn(val=> val.Id)\r\n                                      .IsIn(new []{3,4})\r\n                                      .List();\r\n<\/pre>\n
Of course this query returns wrong data because of the fact, that NHiberante<\/i> by default “joins” queries using AND<\/i> operator. So above query will result in generating something like that<\/p>\n
\r\nSELECT Id,Name,IdProject FROM GL_Task WHERE Name = 'First task' AND Id IN (3,4 \/*more id's to come*\/)\r\n<\/pre>\n
After a little digging I managed to write appropriate query<\/p>\n
\r\nvar orRestriction = Restrictions.Or(\r\n                         Restrictions.Where(val => val.Name == \"First task\"),\r\n                         Restrictions.On(val => val.Id).IsIn(new[] { 3,4 }));\r\n\r\nvar result = session.QueryOver()\r\n                    .Where(orRestriction)\r\n                    .List();\r\n<\/pre>\n
Although this code generates correct SQL, it is hard to use it in case of many OR<\/i> statements. For example, adding one more OR<\/i> force us to write<\/p>\n
\r\nvar orRestriction = Restrictions.Or(\r\n      Restrictions.Or(\r\n            Restrictions.Where(val => val.Name == \"First task\"),\r\n            Restrictions.On(val => val.Id).IsIn(new[] {3, 4}) \r\n       ),\r\n       Restrictions.On(val => val.Name).IsIn(new[] {\"FirstTask\", \"SecondTask\"})\r\n);\r\n\r\nvar result = session.QueryOver()\r\n                    .Where(orRestriction)\r\n                    .List();\r\n<\/pre>\n
In my opinion this is a little bit hard to read, so I continued my searching to find more elegant solution. My third attempt was based on Restrictions.Disjunction<\/i><\/p>\n
\r\nvar disjunctinos = Restrictions.Disjunction()\r\n                           .Add(Restrictions.Where(val => val.Name == \"First task\"))\r\n                           .Add(Restrictions.On(val => val.Id).IsIn(new[] { 3,4 }));\r\n                           \r\nvar result = session.QueryOver()                    \r\n                    .Where(disjunctinos)                    \r\n                    .List();\r\n<\/pre>\n
This was almost perfect solution. Adding new OR<\/i> statements do not require us to do function nesting<\/p>\n
\r\nvar disjunctinos = Restrictions.Disjunction()\r\n                          .Add(Restrictions.Where(val => val.Name == \"First task\"))\r\n                          .Add(Restrictions.On(val => val.Id).IsIn(new[] { 3,4 }))\r\n                          .Add(Restrictions.On(val => val.Name).IsIn(new[] { \"FirstTask\", \"SecondTask\" }));\r\n\r\nvar result = session.QueryOver()\r\n                    .Where(disjunctinos)\r\n                    .List();\r\n<\/pre>\n
However the ultimate solution is to just add NHibernate.Criterion<\/i> namespace and use IsIn<\/i> extension method. This operation simplify our query into single lambda expression<\/p>\n
\r\nvar result = session.QueryOver()\r\n                    .Where(val => val.Name == \"First task\" || val.Id.IsIn(new[] { 3,4 }))\r\n                    .List();\r\n<\/pre>\n
Source code for this post can be found here<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Let’s assume that we have a simple table GL_Task which looks like this I was asked to rewrite simple SQL query SELECT Id,Name,IdProject FROM GL_Task WHERE Name = ‘First task’ OR Id IN (3,4 \/*more id’s to come*\/) using NHibernate’s QueryOver API. As simple as it may seem, solution for this particular problem is not […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,3,47,46,49,48],"tags":[160,159,192,193,194,195],"class_list":["post-22","post","type-post","status-publish","format-standard","hentry","category-net","category-c","category-disjunction","category-nhibernate","category-queryover","category-restrictions","tag-net","tag-c","tag-disjunction","tag-nhibernate","tag-queryover","tag-restrictions"],"_links":{"self":[{"href":"https:\/\/tpodolak.com\/blog\/wp-json\/wp\/v2\/posts\/22","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tpodolak.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tpodolak.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tpodolak.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/tpodolak.com\/blog\/wp-json\/wp\/v2\/comments?post=22"}],"version-history":[{"count":4,"href":"https:\/\/tpodolak.com\/blog\/wp-json\/wp\/v2\/posts\/22\/revisions"}],"predecessor-version":[{"id":310,"href":"https:\/\/tpodolak.com\/blog\/wp-json\/wp\/v2\/posts\/22\/revisions\/310"}],"wp:attachment":[{"href":"https:\/\/tpodolak.com\/blog\/wp-json\/wp\/v2\/media?parent=22"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tpodolak.com\/blog\/wp-json\/wp\/v2\/categories?post=22"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tpodolak.com\/blog\/wp-json\/wp\/v2\/tags?post=22"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}