Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Fluent nHibernate Query => QueryOver, Join, Distinct

I try to change that query to QueryOver<> to be able to do the Distinct operation yet inside the (generated sql) query

var result = (from x in Session.Query<Events>()
              join o in Session.Query<Receivers>() on x.ID equals o.ID
              where x.Owner.ID == 1 //the user is the owner of that Event (not null)
                    ||
                    x.EVType.ID == 123 //(not null)
                    ||
                    x.Receivers.Count(y => y.User.ID == 1) > 0 //the user is one of the Event Receivers
              select x.StartDate)
              .Distinct();

I tried something like that

Events x = null;
List<Receivers> t = null;

var result = Session.QueryOver<Events>(() => x)
                    .JoinAlias(() => x.Receivers, () => t)
                    .Where(() => x.Owner.ID == 1
                                ||
                                x.EVType.ID == 123
                                ||
                                t.Count(y => y.User.ID == 1) > 0)
                   .TransformUsing(Transformers.DistinctRootEntity)
                   .Select(a => a.StartDate)
                   .List();

but then I got the Value can not be null. Parameter name: source exception. Any ideas how can I fix that query ?

edit

thanks to the xanatos' answer, the final SQL query is correct (I used his 2nd approach):

SELECT distinct this_.StartDate as y0_ 
FROM Events this_ 
WHERE 
(
    this_.UserID = ? 
    or
    this_.EventTypeID = ? 
    or
    exists (SELECT this_0_.ID as y0_ 
            FROM Receivers this_0_ 
            WHERE this_0_.UserID = ?)
)
like image 622
Tony Avatar asked Mar 16 '23 23:03

Tony


1 Answers

"In QueryOver, aliases are assigned using an empty variable. The variable can be declared anywhere (but should be empty/default at runtime). The compiler can then check the syntax against the variable is used correctly, but at runtime the variable is not evaluated (it's just used as a placeholder for the alias)." http://nhibernate.info/blog/2009/12/17/queryover-in-nh-3-0.html

Setting List<Receivers> t to empty collection as you did (as you have mentioned in comments) means that you check is event id in local empty collection - doesn't have sense at all.

You can try do your query with subquery (should work but i'm not sure, I wrote it without testing, "by hand"):

Receivers receiversSubQueryAlias = null;
var subquery = session.QueryOver<Events>()
                      .JoinQueryOver<Receivers>(x => x.Receivers, () => receiversSubqueryAlias, JoinType.Inner)
                      .Where(()=> receiversSubQueryAlias.UserId == 1)
                      .Select(x => x.Id)
                      .TransformUsing(Transformers.DistinctRootEntity);

Events eventsAlias = null;
var mainQueryResults = session.QueryOver<Events>(() => eventsAilas)
                       .Where(Restrictions.Disjunction()
                             .Add(() => eventAlias.OwnerId == 1)
                             .Add(() => eventAlias.EVType.Id == 123)
                             .Add(Subqueries.WhereProperty<Events>(() => eventAlias.Id).In(subquery))
                        ).Select(x => x.StartDate)
                        .TransformUsing(Transformers.DistinctRootEntity)
                        .List();
like image 192
fex Avatar answered Mar 27 '23 10:03

fex