CODEWITHSUNDEEP
c#nhibernate
Richard77
Richard77

Reputation: 21609

Where clause in NHibernate

How do I express this in NHibernate?

DECLARE @EntityId INT = 800;

SELECT *
FROM UserAlert
WHERE UserAlertId =  
                  (
                   SELECT MAX(UserAlertId)
                   FROM UserAlert
                   WHERE EntityId = @EntityId
                  )

This is what I'm trying to do. 

var senderUA = session.CreateCriteria<UserAlert>()
                      .Add(Restrictions.Eq("EntityId", id))
                      .SetProjection( Projections.Max("Id") )
                      . UniqueResult();

And I keep getting an error that can convert object to UserAlert type, i.e. it's not even compiling.

Thanks for helping

Upvotes: 1

Views: 3024

Answers (2)

Andrew Shepherd
Andrew Shepherd

Reputation: 45222

Here's a solution using QueryOver.

var maxUserAlertId = QueryOver.Of<UserAlert>
                             .Where(ua => ua.EntityId == id)
                             .Select(
                                 Projections.Max(
                                    Projections.Property<UserAlert>
                                                      (u => u.UserAlertId)
                                 )
                               );
var maxUserQuery = session
                     .QueryOver<UserAlert>()
                     .WithSubquery
                         .WhereProperty(u => u.EntityId)
                         .Eq(maxUserAlertId);

// Dealing with the situation that the maximum value is shared
// by more than one row. If you're certain that it can only
// be one, call SingleOrDefault instead of List
IList<UserAlert> results = maxUserQuery.List();

Upvotes: 0

mehmet mecek
mehmet mecek

Reputation: 2685

Ordering by UserAlertId descending and selecting top 1 would be simpler.

var senderUA = session.CreateCriteria<UserAlert>()
                  .Add(Restrictions.Eq("EntityId", id))
                  .AddOrder(Order.Desc("UserAlertId"))
                  .SetMaxResults(1)
                  .UniqueResult();

Additionally you can

var senderUA = session
                    .Query<UserAlert>()
                    .Where(x=>x.EntityId==id && 
                         x.UserAlertId==session.Query<UserAlert>()
                                           .Where(x=>x.EntiryId==id).Max(x=>x.UserAlertId)
                          ).FirstOrDefault();

Upvotes: 3

Related Questions