Linq to NHibernate - ordering anonymous type

Question

I am using Nhibernate v2.1.2.4000. With many-to-many relationship between Posts an Tags I have the query:

tags
.Select(t => new { Name = t.Name, Count = t.Posts.Count })
.OrderBy(x => x.Count);

Ordering anonymous type fails (reference not set to an instance of an object). Is this issue something related to LinqToNH? What can be the source of this error? What is the solution? If it is something related to LinqToNH then how it can be solved with some other option (ie Criteria API)?

EDIT: When I try Adam's ICriteria option, SqlProfiler says executed script is:

SELECT this_.Name as y0_, count(this_.Id) as y1_ FROM Tag this_ GROUP BY this_.Name ORDER BY count(this_.Id) asc

Mapping for Tag:

public class TagMap : ClassMap<Tag>
{
    public TagMap()
    {
        Table("Tag");
        Id(x => x.Id).GeneratedBy.GuidComb();
        Map(x => x.Name);
        HasManyToMany(x => x.Posts)
            .Table("PostTags")
            .ChildKeyColumn("Post")
            .ParentKeyColumn("Tag")
            .Cascade.None().Inverse();
    }
}

Adam Boddington · Accepted Answer · 2010-12-05T20:09:01.447

There are many things in NHibernate.Linq for NHibernate 2.1.2.4000 that just won't work. You could use HQL or ICriteria instead, or upgrade to NHibernate 3.0, or if you're going to use all the data, force your Linq query to execute after the Select by adding a ToList.

tags
    .Select(t = new { t.Name, t.Posts.Count })
    .ToList()
    .OrderBy(x => x.Count);

The anonymous object by itself is something that NHibernate.Linq can definitely handle.

By the way, you don't have to specify the field name in an anonymous object if it's the same as the field/property you're dragging it from.

EDIT: An ICriteria version of this query would look like this...

var tags = session.CreateCriteria(typeof(Tag), "tag")
    .SetProjection(
        Projections.GroupProperty("tag.Name"),
        Projections.Count("tag.Posts"))
    .AddOrder(Order.Asc(Projections.Count("tag.Posts")))
    .List();

EDIT: With a proper mapping I'm getting the same SQL, Arch. My earlier mapping was wrong. This one seems to work however.

var tags = session.CreateCriteria(typeof(Tag), "tag")
    .CreateCriteria("tag.Posts", "post")
    .SetProjection(
        Projections.GroupProperty("tag.Name"),
        Projections.Count("post.Id"))
    .AddOrder(Order.Asc(Projections.Count("post.Id")))
    .List();

The SQL I get is this...

SELECT this_.Name as y0_, count(post1_.Id) as y1_ FROM Tag this_ inner join Post_Tags posts3_ on this_.Id=posts3_.Tag inner join Post post1_ on posts3_.Post=post1_.Id GROUP BY this_.Name ORDER BY count(post1_.Id) asc

thanks for the answer. upgrading to 3.0 unfortunately is not an option at this time. how can it be written with Criteria API? also I edited the question for the Criteria API option. — rovsen, Dec 01 '10 at 21:02
Adam, when I try this ICriteria version in resultset count is always 1, although not all them are 1. Do you have any idea why this is happening? — rovsen, Dec 02 '10 at 21:57
Could you have a one-to-many relationship defined instead of a many-to-many? Do you have access to SQL Server Profiler? Try running that and seeing what SQL you get out of the call. I built a demo app to test my ICriteria but I don't have access to it here. — Adam Boddington, Dec 02 '10 at 22:23
I edited the question, added the script executed and the mapping for Tag. — rovsen, Dec 03 '10 at 21:03
thanks a lot. it's working now. one more question Adam: how can I change your criteria so that it generates outer join, not inner join. — rovsen, Dec 07 '10 at 07:20

rebelliard · Answer 2 · 2010-12-01T03:30:32.027

1

Try ordering first and then selecting. I have very similar queries on 2.1.2.4 that work perfectly.

Edit: Also try switching between Count and Count()

edited Dec 01 '10 at 03:30

answered Dec 01 '10 at 02:58

rebelliard

9,592
6
47
80

Linq to NHibernate - ordering anonymous type

2 Answers2