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

16,056

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.

Share:
16,056

Related videos on Youtube

maxspan
Author by

maxspan

Always trying to learn better and efficient ways to write code.

Updated on June 04, 2022

Comments

  • maxspan
    maxspan over 1 year

    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..

    • Allan S. Hansen
      Allan S. Hansen over 9 years
      Couldn't you just make both toupper for the comparison and/or compare case insensitive?
    • maxspan
      maxspan over 9 years
      How to compare case insensitive in nhibernate
  • maxspan
    maxspan over 9 years
    It needs to be List(). as in your example you made ToList(). And it should be FirsOrDefault rather than SingleOrDefault.
  • Jamie Ide
    Jamie Ide over 9 years
    I think SingleOrDefault should work, did you try it? FirstOrDefault will work as well.
  • Jan Zahradník
    Jan Zahradník almost 9 years
    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.