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.
Related videos on Youtube
Author by
maxspan
Always trying to learn better and efficient ways to write code.
Updated on June 04, 2022Comments
-
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 over 9 yearsCouldn't you just make both toupper for the comparison and/or compare case insensitive?
-
maxspan over 9 yearsHow to compare case insensitive in nhibernate
-
-
maxspan over 9 yearsIt needs to be List(). as in your example you made ToList(). And it should be FirsOrDefault rather than SingleOrDefault.
-
Jamie Ide over 9 yearsI think SingleOrDefault should work, did you try it? FirstOrDefault will work as well.
-
Jan Zahradník almost 9 yearsThe 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.