CODEWITHSUNDEEP
c#linqnhibernate
user3257436
user3257436

Reputation: 3

Order by property of nested object with NHibernate Linq

I have class Request with list of Comments. Each Request can have zero, one or many Comments.

public class Request
{
    public virtual string Id
    { get; protected set; }

    public virtual DateTime Date
    { get; set; }

    public virtual byte RequestStatusId
    { get; set; }

    public virtual Payment Payment
    { get; set; }

    public virtual IList<RequestComment> RequestComments
    { get; set; }
}
public class RequestComment
{
    public virtual int Id
    { get; protected set; }

    public virtual DateTime Date
    { get; set; }

    public virtual string CommentText
    { get; set; }

    public virtual Request Request
    { get; set; }

    public virtual User User
    { get; set; }
}

I'm using NHibernate.Linq to get data from database. When I sort, for example by Request Id, it looks like this:

var query = _session.Query<Request>()
                .Where(r => r.RequestStatusId == requestStatusId)
                .OrderBy(r => r.Id)
                .Skip(pageNo * pageSize)
                .Take(pageSize);
return query.ToFuture().AsQueryable();

When I need last comment for Request I get it like this:

public RequestComment GetLastCommentForRequest(string requestId)
{
    var query = _session.Query<RequestComment>()
            .Where(r => r.Request.Id == requestId)
            .OrderByDescending(r => r.Date)
            .FirstOrDefault();

    return query;
}

Now I need to get Requests, with their last comment (if exists) and all sorted by CommentText. I was trying to do order in Request query with:

.OrderBy(x => x.RequestComments.Where(y => y.Request.Id == x.Id).OrderByDescending(y => y.Date).FirstOrDefault())

But it's not working and I'm getting error »Exception has been thrown by the target of an invocation.«

UPDATE

This is ok, but it's not sorted by last comment, but by first found:

.OrderBy(r => r.RequestComments.Select(x => x.CommentText).FirstOrDefault())

Upvotes: 0

Views: 1767

Answers (2)

har07
har07

Reputation: 89295

How about this :

.OrderBy(x => x.RequestComments
                .OrderByDescending(y => y.Date)
                .Select(x => x.CommentText)
                .FirstOrDefault()
        )

UPDATE :

It seems that linq above translated to query with subquery having order by clause which is not allowed. Try this instead :

.OrderBy(x => x.RequestComments
                .Where(y => y.Date == x.RequestComments.Max(o => o.Date))
                .Select(u => u.CommentText)
                .FirstOrDefault()
        )

Upvotes: 1

Kaspars Ozols
Kaspars Ozols

Reputation: 7017

You could try:

.OrderBy(x => x.RequestComments.Select(y => y.Date).DefaultIfEmpty().Max())

Upvotes: 0

Related Questions