37

I just started with NHibernate (using SQLite) in my current project and I mostly used Query<>, because I was familiar writing db queries in Linq.

When I was confronted with some more complex queries, I did some research on QueryOver<> and figured that it should be favored over Query<> because "QueryOver syntax is NH specific". Also, there seems to be nothing that Query<> can do that QueryOver<> can't accomplish.

So I began replacing all usages of Query<> accordingly. It wasn't long before I had the first "issue" where using Query<> seemed just more convenient. Example (select highest value from column CustomNumber in table BillingDataEntity):

int result = Session.Query<BillingDataEntity>().Select(x => x.CustomNumber).OrderByDescending(a => a).FirstOrDefault();
int result = Session.QueryOver<BillingDataEntity>().Select(x => x.CustomNumber).OrderBy(a => a.CustomNumber).Desc.Take(1).SingleOrDefault<int>();

What I dislike is the need to explicitly cast the result to int and that the the Query<> version is just easier to read. Am i getting the query totally wrong, or in other words: Is there a better way to do it?

I took a look at the generated SQL output:

NHibernate: select billingdat0_.CustomNumber as col_0_0_ from "BillingDataEntity" billingdat0_ order by billingdat0_.CustomNumber desc limit 1
NHibernate: SELECT this_.CustomNumber as y0_ FROM "BillingDataEntity" this_ ORDER BY this_.CustomNumber desc limit @p0;@p0 = 1 [Type: Int32 (0)]

What exactly am i looking at? Is this the "internal" (method dependent) query that NHibernate further translates into the actual database query?

mipe34
  • 5,596
  • 3
  • 26
  • 38
Rev
  • 5,827
  • 4
  • 27
  • 51

2 Answers2

42

There are plenty of answers regarding QueryOver versus Query here on Stackoverflow but in a nutshell:-

QueryOver is a strongly-typed version of Criteria, and is more NHibernate specific. Pretty much anything you can do in ICriteria can be done with QueryOver. In the golden days of ICriteria NH2 you always had to cast, hence this is why now you need to cast at the end of the chain back to an int.

LINQ (Query) is a standard query method that works on IQueryable that doesn't need explicit references to NHibernate and can be considered more ORM agnostic and therefore follows the linq standard. As you rightly pointed out you do not need to cast to an int as you are selecting into the result the customNumber.

I would be very surprised for your simple example if the generated SQL was very different.

I was a big fan of QueryOver but as the Linq provider is getting more mature then 95% of my queries I use Query but for some Nhibernate specific stuff I resort back down to QueryOver. Either way I recommend using a profiling tool to see what you can live with.

Refs: Tradeoffs or versus and versus

ESV
  • 7,620
  • 4
  • 39
  • 29
Rippo
  • 22,117
  • 14
  • 78
  • 117
  • I am not sure you can answer your first question directly as my answer points out that QueryOver is based on ICiteria and `this is the way it has to be done you are doing it correctly`. To view generated SQL I would use a commercial tool like NHProf as it is the best out there. Not meaning to be blunt and HTH clarifies the answer I have given. – Rippo Feb 22 '13 at 11:14
  • 2
    Also, `Query` returns an `IQueryable` which IS an `IEnumerable` so it's super convenient. – Jess Nov 06 '14 at 14:19
  • @Rippo: second and third link point to the same question – janv8000 Sep 03 '21 at 08:39
2

About your QueryOver version, I would have written :

int result = Session.QueryOver<BillingDataEntity>()
               .Select(Projections.Max<BillingDataEntity>(x => x.CustomNumber))
               .SingleOrDefault<int>();

It seems quite readable, and the resulting SQL would be something like :

SELECT max(this_.CustomNumber) as y0_ FROM "BillingDataEntity" this_

Hope this will help

jbl
  • 15,179
  • 3
  • 34
  • 101