{"id":24,"date":"2014-04-27T22:14:00","date_gmt":"2014-04-27T22:14:00","guid":{"rendered":""},"modified":"2016-01-30T23:33:10","modified_gmt":"2016-01-30T23:33:10","slug":"nhhibernate-multiple-queries-in-one-roundtrip-to-server","status":"publish","type":"post","link":"https:\/\/tpodolak.com\/blog\/2014\/04\/27\/nhhibernate-multiple-queries-in-one-roundtrip-to-server\/","title":{"rendered":"NHhibernate &#8211; multiple queries in one roundtrip to server"},"content":{"rendered":"<p>Usually when we need to retrieve data from database server, we write code which looks like that<\/p>\n<pre lang=\"csharp\">\r\nusing (var session = DataAccesLayer.Instance.OpenSession())\r\n{\r\n    using (var transaction = session.BeginTransaction())\r\n    {\r\n        var projects = session.QueryOver<Project>().Where(val => val.Name == \"Project\").List();\r\n        var tasks = session.QueryOver<Task>().Where(val => val.Name == \"First task\").List();\r\n        \/\/more queries           \r\n        \/\/more queries             \r\n        \/\/exception handling etc.\r\n    }\r\n}\r\n<\/pre>\n<p>This code will execute two queries,each of them in separate roundtrip to the server.<br \/>\n<a href=\"http:\/\/tpodolak.com\/blog\/wp-content\/uploads\/2014\/04\/nhhibernate-multiple-queries-in-one-roundtrip-to-server\/SeveralRoundtrips.png\" rel=\"attachment wp-att-315\"><img decoding=\"async\" src=\"http:\/\/tpodolak.com\/blog\/wp-content\/uploads\/2014\/04\/nhhibernate-multiple-queries-in-one-roundtrip-to-server\/SeveralRoundtrips.png\" alt=\"SeveralRoundtrips\" width=\"700\" class=\"aligncenter size-full wp-image-315\" srcset=\"https:\/\/tpodolak.com\/blog\/wp-content\/uploads\/2014\/04\/nhhibernate-multiple-queries-in-one-roundtrip-to-server\/SeveralRoundtrips.png 754w, https:\/\/tpodolak.com\/blog\/wp-content\/uploads\/2014\/04\/nhhibernate-multiple-queries-in-one-roundtrip-to-server\/SeveralRoundtrips-150x65.png 150w, https:\/\/tpodolak.com\/blog\/wp-content\/uploads\/2014\/04\/nhhibernate-multiple-queries-in-one-roundtrip-to-server\/SeveralRoundtrips-300x130.png 300w\" sizes=\"(max-width: 754px) 100vw, 754px\" \/><\/a><br \/>\nThis is the most popular way of getting data for multiple tables etc. However it is possible to enhance the code above and execute several queries in only one roundtrip to the database server. NHibernate provides us with three different mechanisms to accomplish this goal:<\/p>\n<ul>\n<li><b>MultiQuery <\/b> <\/li>\n<p> First way to execute several queries at once is MultiQuery. In my opinion this is not very handy mechanism because of the fact, that we must use HQL. Here is an example of usage<\/p>\n<pre lang=\"csharp\">\r\nvar projectQuery = session.CreateQuery(\"FROM Project as prj where prj.Name= :projectName\")\r\n                          .SetParameter(\"projectName\", \"Project\");\r\n\r\nvar taskQuery = session.CreateQuery( \"FROM Task as tsk where tsk.Name=:taskName\" )\r\n                       .SetParameter(\"taskName\",\"First task\");\r\n \r\nvar multiQueryByIndex = session.CreateMultiQuery( )\r\n                               .Add<Project>( projectQuery )\r\n                               .Add<Task>( taskQuery );\r\n<\/pre>\n<p>As You can see thanks to <i>CreateMultiQuery<\/i> method we are able to aggregate multiple queries. In order to get access to results, we have to execute code below<\/p>\n<pre lang=\"csharp\">\r\nvar result = multiQueryByIndex.List();\r\n<\/pre>\n<p>Now we can get result of specific query using indexer<\/p>\n<pre lang=\"csharp\">\r\nvar projects = result[0] as List<Project>;\r\nvar tasks = result[1] as List<Task>;\r\n<\/pre>\n<p>If we don&#8217;t like this approach, the <i>CreateMultiQuery<\/i> method has an overload which allows to give &#8220;names&#8221; to queries. Thanks to this we can get result of query by its name.<\/p>\n<pre lang=\"csharp\">\r\nvar multiQueryByKey = session.CreateMultiQuery( )\r\n                             .Add<Project>( \"project\", projectQuery )\r\n                             .Add<Task>( \"task\", taskQuery );\r\n \r\nvar projects = multiQueryByKey.GetResult( \"project\" ) as List<Project>;\r\nvar tasks = multiQueryByKey.GetResult( \"task\" ) as List<Task>;\r\n<\/pre>\n<li><b>MultiCriteria<\/b> <\/li>\n<p> Much better way of creating multiple queries is MultiCriteria. In this case we can use <i>QueryOver<\/i> API, so we don&#8217;t have to rely on <i>HQL<\/i> query strings.<\/p>\n<pre lang=\"csharp\">\r\nvar projectQuery = session.QueryOver<Project>().Where(val => val.Name == \"Project\");\r\nvar taskQuery = session.QueryOver<Task>( ).Where(val => val.Name == \"First task\");\r\nvar multiCriteriaByIndex = session.CreateMultiCriteria( )\r\n                                  .Add( projectQuery )\r\n                                  .Add( taskQuery );\r\n\r\nvar multiCriteriaByKey = session.CreateMultiCriteria( )\r\n                                .Add( \"project\", projectQuery )\r\n                                .Add( \"task\", taskQuery );\r\n\r\nIList result = multiCriteriaByIndex.List( );\r\nvar projects = result[0] as List<Project>;\r\nvar tasks = result[1] as List<Task>;\r\n\r\nprojects = multiCriteriaByKey.GetResult( \"project\" ) as List<Project>;\r\ntasks = multiCriteriaByKey.GetResult( \"task\" ) as List<Task>;\r\n<\/pre>\n<p>As You can see the usage of <i>CreateMultiCriteria<\/i> method is pretty consistent with <i>CreateMultiQuery<\/i>. Unfortunately we still have to retrieve data from specific query by index or by query name.<\/p>\n<li><b>Future<\/b> <\/li>\n<p> My personal favorite for creating multi queries is <i>Future<\/i>. The usage is pretty straightforward<\/p>\n<pre lang=\"csharp\">\r\nvar projects = session.QueryOver<Project>().Where(val => val.Name == \"Project\").Future<Project>();\r\nvar tasks = session.QueryOver<Task>().Where(val => val.Name == \"First task\").Future<Task>();\r\n<\/pre>\n<p>As You can see, we can leverage <i>QueryOver<\/i> API and instead of calling List<T> we call Future<T>. NHibernate will aggregate our queries and will execute them once we start to iterate over one of them.<br \/>\n<a href=\"http:\/\/tpodolak.com\/blog\/wp-content\/uploads\/2014\/04\/nhhibernate-multiple-queries-in-one-roundtrip-to-server\/delayedenumerator.png\" rel=\"attachment wp-att-313\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/tpodolak.com\/blog\/wp-content\/uploads\/2014\/04\/nhhibernate-multiple-queries-in-one-roundtrip-to-server\/delayedenumerator.png\" alt=\"delayedenumerator\" width=\"700\" height=\"139\" class=\"aligncenter size-full wp-image-313\" \/><\/a>\n<\/ul>\n<p>Despite the fact that all three mechanism has completely different syntax ,SQL created by all of them is the same and looks like that<br \/>\n<a href=\"http:\/\/tpodolak.com\/blog\/wp-content\/uploads\/2014\/04\/nhhibernate-multiple-queries-in-one-roundtrip-to-server\/intheend.png\" rel=\"attachment wp-att-315\"><img decoding=\"async\" src=\"http:\/\/tpodolak.com\/blog\/wp-content\/uploads\/2014\/04\/nhhibernate-multiple-queries-in-one-roundtrip-to-server\/intheend.png\" alt=\"multiple queries\" width=\"700\" class=\"aligncenter size-full wp-image-315\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Usually when we need to retrieve data from database server, we write code which looks like that using (var session = DataAccesLayer.Instance.OpenSession()) { using (var transaction = session.BeginTransaction()) { var projects = session.QueryOver().Where(val => val.Name == &#8220;Project&#8221;).List(); var tasks = session.QueryOver().Where(val => val.Name == &#8220;First task&#8221;).List(); \/\/more queries \/\/more queries \/\/exception handling etc. } } [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,46],"tags":[159,193],"class_list":["post-24","post","type-post","status-publish","format-standard","hentry","category-c","category-nhibernate","tag-c","tag-nhibernate"],"_links":{"self":[{"href":"https:\/\/tpodolak.com\/blog\/wp-json\/wp\/v2\/posts\/24","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=24"}],"version-history":[{"count":7,"href":"https:\/\/tpodolak.com\/blog\/wp-json\/wp\/v2\/posts\/24\/revisions"}],"predecessor-version":[{"id":533,"href":"https:\/\/tpodolak.com\/blog\/wp-json\/wp\/v2\/posts\/24\/revisions\/533"}],"wp:attachment":[{"href":"https:\/\/tpodolak.com\/blog\/wp-json\/wp\/v2\/media?parent=24"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tpodolak.com\/blog\/wp-json\/wp\/v2\/categories?post=24"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tpodolak.com\/blog\/wp-json\/wp\/v2\/tags?post=24"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}