query did not return a unique result: 2 . Issue with Nhibernate Query

Question

I am current having an issue with getting records from database using nhibernate.

My 'publicstring' column is having two values, one with lower case and the other with upper case. I am trying to fetch one of them depending on what is typed.

Below is my query

 private string _publicId;
 var _mediaFileShare = this.Session.QueryOver<MediaFileShare>()
                                   .Where(q => q.Publicsting == _publicstring)
                                   .SingleOrDefault();

Answers would be appreciated..

Couldn't you just make both toupper for the comparison and/or compare case insensitive? — Allan S. Hansen
– Allan S. Hansen, Commented Apr 8, 2014 at 6:38
Generally NHibernate just translates the comparison, which is then actually executed in SQL, so Allan certainly meant, you should tell your SQL column to be case insensitive — Andreas H.
– Andreas H., Commented Apr 8, 2014 at 8:26

Jamie Ide · Accepted Answer · 2014-04-09 12:03:24Z

2

A naive solution is:

var _mediaFileShare = this.Session.QueryOver<MediaFileShare>()
    .Where(q => q.PublicString == _publicString)
    .List()
    .SingleOrDefault(r => string.Compare(r.PublicString, _publicString, StringComparison.Ordinal) == 0);

If you're confident that the query will always return two rows then performance should be very acceptable. Alternatively you could use a dynamic order by, something like:

    var query = this.Session.QueryOver<MediaFileShare>()
        .Where(q => q.PublicString == _publicString);

    query = _publicString == _publicString.ToLower() 
        ? query.OrderBy(q => q.PublicString) 
        : query.OrderByDescending(q => q.PublicString);

    var _mediaFileShare = query.Take(1).SingleOrDefault();

The results using an order by will be dependent on your database and its collation setting.

edited Apr 9, 2014 at 12:03

answered Apr 8, 2014 at 23:48

Jamie Ide

49.4k18 gold badges85 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

maxspan Over a year ago

It needs to be List(). as in your example you made ToList(). And it should be FirsOrDefault rather than SingleOrDefault.

Jamie Ide Over a year ago

I think SingleOrDefault should work, did you try it? FirstOrDefault will work as well.

Jan Zahradník Over a year ago

The naive solution will fail when more then one result is returned from database and fulfills the SingleOrDefault condition. Add .SetMaxResults(1) before List() method. It has also performance benefits.

Collectives™ on Stack Overflow

query did not return a unique result: 2 . Issue with Nhibernate Query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related