NHibernate Linking Table with Data

Question

SQL 2008 | .NET 4.0 | NHibernate 3.1 | NHibernate.Castle 3.1 | Castle.Core 2.5.2

So I have a linking table with metadata, like the author of this question NHibernate Mapping a Many to Many with Data on Join Table

Initially, I mapped just like the answer to this question as it seemed the most parsimonious way to handle it. However, after turning on show_sql and observing what was going on, the ID lookups ended up yielding N+1 queries where N is the number of associations.

Observe this example database that is analogous to my actual data, defined in sql-like syntax

CREATE TABLE [User] 
(
  Id int PRIMARY KEY 
)

CREATE TABLE UserPref 
(
  Id int PRIMARY KEY,
  Name varchar(32)
)

CREATE TABLE UserPrefAssociation 
(
  UserId int,
  PrefId int,
  Value varchar(32)
)

I hacked the following code together with this User one-to-many object mapping IList<UserPrefAssociation> Preferences { get; set; }

    public IDictionary<string, string> GeneratePrefDict()
    {
        return Preferences
            .ToDictionary(i => i.UserPref.Name, i => i.Value);
    }

Sure, this works great, but as mentioned before, each i.UserPref.Name, is an additional query to SQL.

After playing in SQL, I have found the query that accomplishes what I want. My question then becomes how can I do this with NHibernate?

SELECT UserPref.Name, UserPrefAssociation.Value
FROM [User] 
    INNER JOIN UserPrefAssociation ON [User].Id = UserPrefAssociation.UserId
    INNER JOIN UserPref ON UserPrefAssociation.UserPrefId = UserPref.Id
WHERE [User].Id = 1

~~~~SOLVED~~~~~

    using NHibernate.Linq;
    ...
    public IDictionary<string, string> GeneratePrefDict(ISession s)
    {
        return 
            (from entry in s.Query<User_UserPref>()
             where entry.User == this
             select new
             {
                 key = entry.UserPref.Name,
                 value = entry.Value
             })
             .ToDictionary(i => i.key, i => i.value);
    }

Generates this SQL

NHibernate: select userpref1_.Name as col_0_0_, user_userp0_.Value as col_1_0_ f
rom User_UserPref user_userp0_ left outer join UserPref userpref1_ on user_userp
0_.UserPrefId=userpref1_.Id where user_userp0_.UserId=@p0;@p0 = 1 [Type: Int32 (
0)]

Which is better than N+1 queries, and solves my issue.

Are you wanting to achieve this fetching strategy from a mapping perspective or by using QueryOver or Linq? — Cole W, Oct 27 '11 at 19:56
Any would be good. I mainly just want to use NHibernate to do this. — Squirrelsama, Oct 27 '11 at 22:36

score 0 · Accepted Answer · edited May 23 '17 at 11:47

0

I think you can achieve what you are wanting with Futures and QueryOver. Take a look at the following article:

Fighting cartesian product (x-join) when using NHibernate 3.0.0

If you can't visualize how to accomplish what you need from the above I can tailor that example more to your needs.

edited May 23 '17 at 11:47

Community

1
1

answered Oct 27 '11 at 23:47

Cole W

15,123
6
51
85

Well, I'm accepting this answer because you tried, and it looks like it would indeed solve the problem with a massive code-mess. – Squirrelsama Oct 28 '11 at 18:22

NHibernate Linking Table with Data

1 Answers1