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?

Look for `SingleOrDefault()`. Please accept @RRR 's answer. – Brian Chavez Sep 10 '11 at 01:11 — Brian Chavez, Sep 10 '11 at 01:11

score 40 · Answer 1 · answered Dec 29 '10 at 14:04

40

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

answered Dec 29 '10 at 14:04

Marcel

15,039
20
92
150

6

Yup. `Take` is what I was going to recommend. Combine that with RRR's `SingleOrDefault` instead of `List().FirstOrDefault()` and it's perfect. – Daniel Schilling Sep 11 '11 at 03:40
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. – tom.dietrich Oct 24 '13 at 13:21
Just wanted to mention that there is a typo in the comment. 'enumerate only on' -> 'enumerate only one'. Good answer. – Tony Aug 04 '21 at 14:08

score 23 · Answer 2 · edited Sep 10 '11 at 03:35

23

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

edited Sep 10 '11 at 03:35

Brian Chavez

8,048
5
54
47

answered Aug 25 '11 at 02:03

RRR

271
2
2

3

Causes NHibernate.NonUniqueResultException – Graham Jan 25 '12 at 09:06
12

Try `.Take(1)` before `.SingleOrDefault()` in that case. See @Marcel's answer. – Joel Purra Mar 29 '12 at 17:04
4

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() – wezzix Feb 15 '16 at 13:45
@wezzix 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 – PandaWood Oct 24 '17 at 23:23
@PandaWood no, OP is sorting and want the top result, thus SingleOrDefault does not answer the question (on its own). – AnorZaken Feb 25 '20 at 15:35

score 11 · Accepted Answer · answered Dec 29 '10 at 22:19

11

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();

answered Dec 29 '10 at 22:19

Diego Mijelshon

52,548
16
116
154

Looks great, I'll give it a try (only next year however, since I am off now!) – Marcel Dec 29 '10 at 22:40
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. – Marcel Jan 04 '11 at 11:43
8

This doesn't answer the question of doing this with QueryOver. The answer is valid just not for the question asked. @RRR's is more correct. – Jafin Jan 23 '12 at 03:24

score 10 · Answer 4 · answered Dec 29 '10 at 13:44

10

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.

answered Dec 29 '10 at 13:44

tom.dietrich

8,219
2
39
56

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. – Marcel Dec 29 '10 at 13:52
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? – tom.dietrich Dec 29 '10 at 14:55

score 0 · Answer 5 · edited Apr 11 '19 at 23:52

0

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.

edited Apr 11 '19 at 23:52

Ru Chern Chong

3,692
13
33
43

answered Apr 11 '19 at 16:15

hplodur

1

1

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](https://stackoverflow.com/help/how-to-answer) might give you useful hints. Thanks! – David Apr 11 '19 at 16:44
Hi David, I meant to comment on the answer above me, not provide a whole new answer. My mistake. – hplodur Apr 11 '19 at 17:23

NHibernate 3.0: No FirstOrDefault() with QueryOver?

5 Answers5