NHibernate 3.0: No FirstOrDefault() with QueryOver?

Question

I am playing with FluentNHibernate and NH 3.0, using the LINQ provider and the new QueryOver syntax.

Now with QueryOver I want to get an item (called result) with a timestamp value as close as possible to a given value, but not greater:

 Result precedingOrMatchingResult = Session.QueryOver<Result>().
        Where(r => r.TimeStamp < timeStamp).
        OrderBy(r => r.TimeStamp).Desc.                
        FirstOrDefault(); //get the preceding or matching result, if there is any

Now, Intellisense tells me that there is no such thing as a FirstOrDefault() method. I could, of course, enumerate my ordered query, and then use LINQ to get my item. But this would load all items into memory first.

Is there an alternative to FirstOrDefault(), or have I understood something completely wrong?

Ly

Look for SingleOrDefault(). Please accept @RRR 's answer. – Schwinn

Answer 1

NH 3 has an integrated LINQ provider (queries are translated internally to HQL/SQL). You have to add the NHibernate.Linq namespace and then:

Result precedingOrMatchingResult = Session.Query<Result>().
    Where(r => r.TimeStamp < timeStamp).
    OrderByDescending(r => r.TimeStamp).
    FirstOrDefault();

Superego

Looks great, I'll give it a try (only next year however, since I am off now!) – Ly

This does the trick. However, since in my Where clauses, there are some additional restrictions, I found that LINQ to NH does not Support the .HasValue() method support for Nullable types. Too bad, but I now check for != null which works. – Ly

This doesn't answer the question of doing this with QueryOver<T>. The answer is valid just not for the question asked. @RRR's is more correct. – Intranuclear

Answer 2

I have now found out that I could use the Take() extension method on the IQueryOver instance, and only the enumerate to a list, like so:

Result precedingOrMatchingResult = Session.QueryOver<Result>().
        Where(r => r.TimeStamp < timeStamp).
        OrderBy(r => r.TimeStamp).Desc.   
        Take(1).List(). //enumerate only on element of the sequence!
        FirstOrDefault(); //get the preceding or matching result, if there is any

Ly

Yup. Take is what I was going to recommend. Combine that with RRR's SingleOrDefault instead of List().FirstOrDefault() and it's perfect. – Haggadah

Actually .Take(1) will do the same thing as .SetFetchSize(1) in the sense that it will tell SQL that you only want one result. In both cases you are not executing a query for all matching results and then enumerating over the first one. – Induplicate

Just wanted to mention that there is a typo in the comment. 'enumerate only on' -> 'enumerate only one'. Good answer. – Selimah

Answer 3

Result precedingOrMatchingResult = Session.QueryOver<Result>()
                                          .Where(r => r.TimeStamp < timeStamp)
                                          .OrderBy(r => r.TimeStamp).Desc
                                          .SingleOrDefault();

Antifebrile

Causes NHibernate.NonUniqueResultException – Microhenry

Try .Take(1) before .SingleOrDefault() in that case. See @Marcel's answer. – Gabi

This is not the correct answer. SingleOrDefault throws an exception if there are more than one results. FirstOrDefault does not. You could however try .Take(1).SingleOrDefault() – Eulau

@Eulau it's a good point, but the OP expressed no need for the exceptional behavior - looks like he didn't know or care about it - he just asked for a one-call alternative to enumerating everything and taking the 1st – Vullo

@Vullo no, OP is sorting and want the top result, thus SingleOrDefault does not answer the question (on its own). – Oppilate

Answer 4

Try

Result precedingOrMatchingResult = Session.QueryOver<Result>().
        Where(r => r.TimeStamp < timeStamp).
        OrderBy(r => r.TimeStamp).Desc.
        SetFetchSize(1).
        UniqueResult();

UniqueResult will return a single value, or null if no value is found, which is kinda what First or Default does.

Setting the Fetch Size to 1 may or may not be required, I'd test that with a profiler.

Induplicate

I had a look into it. UniqueResult seems to be similar to SingleOrDefault, but I do not have a single item. I have a list, and I want to pick the first item only. – Ly

What you want your query to return is the first item only, right? Why return a list from the database server and then throw away most of the work? – Induplicate

Answer 5

SetFetchSize(1) is required. If your LINQ query returns more than one result, it will throw an NHibernate exception using UniqueResult(), as it is only expecting one result to be returned from the query.

Usurp

Welcome to Stack Overflow and thanks for your contribution! Your answer seems not being 100% related to the question as SetFetchSize was not mentioned in the question. Perhaps you can explain more clearly or correct. Our guide How to write a good answer might give you useful hints. Thanks! – Th

Hi David, I meant to comment on the answer above me, not provide a whole new answer. My mistake. – Usurp