LINQ: QuerySyntaxException

Question

I've written some complex LINQ query which I'm trying to run against NHibernate, unfortunately this causes an exception when trying to evaluate the query (call .ToList()).

Here is the code, which is supposed to retrieve Issue objects with some extra data:

var list =  from i in sess.Query<Issue>()
        let readFlag = (from f in i.ReadFlags
                        where (f.User != null && f.User.Username == request.UserName)
                              ||
                              (f.Device.Name == request.MachineName)
                        select f).FirstOrDefault()
        let isUnread = readFlag == null
        let unreadCommentsCount = isUnread ? 0 : (from c in i.Comments
                                   where c.DateCreated > readFlag.LastSeenCommentDate
                                   select c).Count()

        let statusChanged = isUnread && i.Status != readFlag.LastSeenStatus
        where isUnread || unreadCommentsCount > 0 || statusChanged
        select new
        {
            Issue = i,
            ReadFlag = readFlag,
            IsUnread = isUnread,
            UnreadCommentsCount = unreadCommentsCount
        };

i.e. - for each Issue object look for related IssueReadFlag which matches current username or machine name. if the Flag exists, it also contains the "last seen comment date", so the code calculates the number of new comments for this Issue.

Unfortunately NHibernate is not able to handle this query (Exception details).

I'm looking for the most optimal solution for this problem.

Obtaining all the Issue objects and applying the query criteria on the client-side is unacceptable for me.

trying to find out what causes the issue: It started working as I've commented out evaluation of unreadCommentsCount and statusChanged both in "select" and "where" statements. Following this lead, It seems that referencing readFlag in any of these subqueries causes the issue (after removing reference to readFlag in "let unreadCommentsCount .." and "let statusChanged.." conditions, the query doesn't throw exceptions). Now I need a tip on how to make it functional (working as expected) again ...

Have you considered narrowing down the issue by starting with a general `Query` and adding these different checks in? — Ocelot20, Nov 15 '13 at 17:58
@Ocelot20 tried this in the meantime. see the update of the question — migajek, Nov 15 '13 at 21:41

score 0 · Accepted Answer · answered Nov 24 '13 at 09:31

Further investigating the issue I've found out that NHibernate can't handle the uncertain state of readFlag existence.

I've ended up splitting the query into two parts, first of them takes all the Issues where readFlag does not exists, the second one queries the IssueReadFlag directly, calculating the Unread Comments Count and Changed Status.

This seems to be the only solution as for now.

LINQ: QuerySyntaxException

1 Answers1