当前位置: 动力学知识库 > 问答 > 编程问答 >

nhibernate - Disjunction on QueryOver<T> always refers to root entity

问题描述:

I'm trying to add a certain numbers of OR conditions using disjunction on X number of entities that implements a certain interface containing date information. My problem is that when the SQL is generated all my disjunction conditions points to the root entity of my QueryOver.

I have created a generic method to add my conditions

 public static QueryOver<T,T2> AddChangedCondition<T,T2>(this QueryOver<T,T2> query, DateTime from, DateTime to, Disjunction disjunction) where T2 : IHaveDate

{

if(disjunction == null )

disjunction = new Disjunction();

disjunction.Add<T2>(k => (k.DeleteDate > from && k.DeleteDate < to)

|| k.CreatedDate > from

|| k.UpdatedDate > from);

return query;

}

I want to use it like this:

Disjunction disjunction = null;

var query = QueryOver.Of<User>()

.AddChangedCondition(fromDate,toDate, disjunction)

.JoinQueryOver<Program>(user => user.Programs)

.AddChangedCondition(fromDate,toDate, disjunction);

query.Where(disjunction);

Sql generated from this will look something along the lines of

select ....

from User

where

(

(

this_.raderadDatum > @p1

and this_.raderadDatum < @p2

)

or this_.skapadDatum > @p3

or this_.uppdateradDatum > @p4

)

or

(

this_.raderadDatum > @p1

and this_.raderadDatum < @p2

)

or this_.skapadDatum > @p3

or this_.uppdateradDatum > @p4

)

I've tried different solutions using aliases but no success. Would be greatful for any help!

网友答案:

try in this way, I've inserted some random conditions and I used Aliases

 var qOver = _session.QueryOver<User>(() => usr)         
     .JoinAliases(() => usr.Programs, prg, JoinType.LeftOuterJoin)
     .Where(Restrictions.Or(
                Restrictions.On(() => usr.ID).IsIn(MyValue)
                ,Restrictions.Or(
                   Restrictions.On(() => prg.ID).IsIn(MyValue)
                  , Restrictions.On(() => prg.ID).IsNull)
                 )
               )
     .List<User>();

In this way your SQL code will be the following

SELECT ....
FROM User
INNER JOIN Program On (...conditions...)
WHERE User.ID = 'MyValue'
OR (Program.ID IN ('Value1','Value2') OR Program.ID IS NULL)

I hope it's helpful

网友答案:

You can get around the magic strings as @Faber suggests in his answer.

I had a similar problem also requiring dates. Here is my code:

var disjunction= new Disjunction();

disjunction.Add(Restrictions.On<LocalAsset>(e => e.AvailableFrom).IsBetween(startDate).And(endDate));
disjunction.Add(Restrictions.On<LocalAsset>(e => e.AvailableTo).IsBetween(startDate).And(endDate));

query.Where(disjunction);
网友答案:

I came up with this solution (unfortunatly I didn't manage to get rid of magic strings)

protected Disjunction GetChangedDisjunction<T>(string alias, Disjunction junction = null) where T : IHaveDate
    {
        if(junction == null)
            junction = new Disjunction();

        var toDatum = DateTime.Now;
        junction.Add(Restrictions.And(
                        Expression.Gt(string.Format("{0}.DeletedDate", alias), fromDate), 
                        Expression.Lt(string.Format("{0}.DeletedDate", alias), toDate)));
        junction.Add(Restrictions.Gt(string.Format("{0}.CreatedDate", alias), fromDate));
        junction.Add(Restrictions.Gt(string.Format("{0}.UpdatedDate", alias), fromDate));

        return junction;
    }

and then I use it like this:

User userAlias= null;
var queryOverUser = QueryOver.Of( () => userAlias);

Program programAlias = null;
queryOverUser 
    .JoinQueryOver(a => a.Programs, () => programAlias, JoinType.LeftOuterJoin)

Disjunction disjunction = new Disjunction();
GetChangedDisjunction<User>("userAlias", disjunction);
GetChangedDisjunction<Program>("programAlias", disjunction);

queryOverUser.Where(disjunction);
分享给朋友:
您可能感兴趣的文章:
随机阅读: