sql – Nhibernate计数差异(基于多列)

根基上,我一向在实行这样做(按照两列计较差异)：

select count(distinct(checksum(TableA.PropertyA,TableB.PropertyB))) 
from TableA 
left outer join TableB
on TableA.TableBId = TableB.Id 
where PropertyA like '%123%'

谷歌搜刮怎样做到这一点,但没有命运.试过这个,但从未真正奏效过.这并不明明基于两个表中的两个属性：

var queryOver = c.QueryOver<TableA>();
TableB tableBAlias = null;
TableA tableAAlias = null;
ProjectionList projections = Projections.ProjectionList();

queryOver.AndRestrictionOn(x => x.PropertyA).IsLike("%123%");
projections.Add(Projections.CountDistinct(() => tableAAlias.PropertyA));

queryOver.JoinAlias(x => x.TableB,() => tableBAlias,JoinType.LeftOuterJoin);
projections.Add(Projections.CountDistinct(() => tableBAlias.PropertyB));

queryOver.Select(projections);
queryOver.UnderlyingCriteria.SetProjection(projections);
return queryOver.TransformUsing(Transformers.DistinctRootEntity).RowCount();

办理要领

>建设支持校验和成果的自界说方言：

public class MyCustomDialect : MsSql2008Dialect
{
    public MyCustomDialect()
    {
        RegisterFunction("checksum",new SQLFunctionTemplate(NHibernateUtil.Int32,"checksum(?1,?2)"));
    }
}

>更新您的设置以行使自界说方言(您可以在设置XML文件中或行使代码执行此操纵.有关更多信息,请参阅this answer).以下是我在现有设置代码中的行使要领：

configuration
    .Configure(@"hibernate.cfg.xml")
    .DataBaseIntegration(
        db => db.Dialect<MyCustomDialect>());

>建设一个挪用校验和的自界说投影.这一步是可选的 – 你可以直接挪用Projections.SqlFunction,但我以为将它重构为一个单独的函数更简捷：

public static class MyProjections 
{
    public static IProjection Checksum(params IProjection[] projections)
    {
        return Projections.SqlFunction("checksum",NHibernateUtil.Int32,projections);   
    }
}

>编写QueryOver查询并挪用自界说投影：

int count = session.QueryOver<TableA>(() => tableAAlias)
    .Where(p => p.PropertyA.IsLike("%123%"))
    .Left.JoinQueryOver(p => p.TableB,() => tableBAlias)
    .Select(
        Projections.Count(
            Projections.Distinct(
            MyProjections.Checksum(
                Projections.Property(() => tableAAlias.PropertyA),Projections.Property(() => tableBAlias.PropertyB)))))
    .SingleOrDefault<int>();

这应该天生看起来像你所追求的SQL：

SELECT count(distinct checksum(this_.PropertyA,tableba1_.PropertyB)) as y0_
FROM   [TableA] this_
    left outer join [TableB] tableba1_
    on this_.TableBId = tableba1_.Id
WHERE  this_.PropertyA like '%123%' /* @p0 */

1如故试图找出是否有一种要领来映射函数而无需手动指定参数的数目

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!