nHibernate query syntax

Question

I'm working on a project that uses nHibernate (v 3.3.2) with fluent where all existing queries are some IQueryOver with something like Linq...

I thought I could avoid learning that confusing syntax by using stored procedures. But calling them has proven to be very difficult.

I tried CreateSqlQuery but got a GenericADOException "could not execute query". I tried defining a .hbm.xml file and loading that in:

m.HbmMappings.AddFromAssembly(System.Reflection.Assembly.GetExecutingAssembly());

then:

IQuery query = session.GetNamedQuery("sp_GetTagCount");

But the named query was not found. I think that perhaps the XMLfile is not being loaded.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <sql-query name="sp_GetTagCount"  callable="true" >
    <return class="TagCount" />

    exec sp_GetTagCount
  </sql-query>
</hibernate-mapping>

That is the only XML or config I have added to the app so perhaps more configuration is required.

I would be happy to use a fluent query instead of the sproc but the only ones I have to copy are single table selects.

The SQL is very simple:

SELECT TOP 10
    t.[Id], t.[Name], count(*) as [Count]
FROM [dbo].[Tag] t inner join 
     [dbo].[TagEntry] te on t.Id = te.TagId
GROUP BY t.[Id], t.[Name]
ORDER BY [Count] DESC

I just need any way at all to get this query into nHibernate. I've tried numerous google searches on "nhibernate fluent tutorial" but all they seem to discuss is DB configuration and class configuration. I need to learn this query syntax. I have spent many hours on this and seem to be making no progress.

If anyone can direct me to a good beginners tutorial to replicate joins, grouping and aggregates in this fluent query syntax that would be great!

I made some progress using Nhibernate.Linq as suggested by Oskar.

I was writing this out:

var results = (from t in session.Query<Tag>()
             join e in session.Query<TagEntry>()

But TagEntry doesn't exist in the model.

HasManyToMany(x => x.Entries)
                .Table("TagEntry")
                .ParentKeyColumn("TagId")
                .ChildKeyColumn("EntryId")
                .Inverse()
                .LazyLoad();

Each Tag has an Entries property which is nice but how do I group by?

for a good resource (though old) is [Summer of nhibernate](http://summerofnhibernate.com/) as well as [NHibernate Cookbook](http://www.packtpub.com/nhibernate-3-0-cookbook/book) — Nathan Fisher, Sep 18 '13 at 05:00
You don't state the version of NHibernate, but recent versions have pretty good support for actual LINQ too. — Oskar Berggren, Sep 18 '13 at 08:22
Version is 3.32. Using linq would be good it's similar enough to SQL. Is it possible with nHibernate to just run arbitrary SQL without having to fall back to XML coniguration? — Des, Sep 18 '13 at 09:26

Dave · Accepted Answer · 2013-09-18T11:12:57.317

1

You can use Linq but don't need a join I think nHibernate does that for you automatically with that mapping so then just count the Entries property:

var results = (from t in Session.Query<Tag>()
    select new { Id = t.Id, TagUseCount = t.Entries.Count(), TagName = t.Name });

edited Sep 18 '13 at 11:12

answered Sep 18 '13 at 11:03

Dave

1,484
3
14
17

nHibernate query syntax

1 Answers1