Nhibernate计数不同(基于多个列) [英] Nhibernate count distinct (based on multiple columns)

问题描述

基本上，我一直在尝试执行此操作(基于两列来进行计数):

Basically, i have been trying to do this (count distinct based on two columns):

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

喜欢谷歌搜索该怎么做，但是没有运气.尝试过此方法，但从未真正起作用.根据两个表的两个属性，这并没有明显的不同:

Been googling on how to do this but with no luck. Tried this, but never actually worked. This does not count distinctly based on the two properties from two tables:

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();

推荐答案

好的，这需要采取一些步骤，请多多包涵.我在这里假设使用SQL Server，但说明应适用于支持checksum ¹ :

Okay this is going to take a few steps, so bear with me. I'm assuming SQL server here, but the instructions should work for any dialect that supports checksum¹:

1. 创建支持checksum功能的自定义方言:

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

更新您的配置以使用自定义方言(您可以在配置XML文件中或使用代码进行此操作.请参见此答案以获取更多信息).这是我在现有配置代码中执行的操作:

Update your configuration to use the custom dialect (you can do this either in your configuration XML file or with code. See this answer for more information). Here's how I did it inside of my existing configuration code:

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

创建一个调用checksum的自定义投影.此步骤是可选的-您可以根据需要直接调用Projections.SqlFunction，但是我认为将其重构为一个单独的函数更为简洁:

Create a custom projection that calls checksum. This step is optional-- you can call Projections.SqlFunction directly if you'd like, but I think refactoring it into a separate function is cleaner:

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

编写您的QueryOver查询并调用自定义投影:

Write your QueryOver query and call the custom projection:

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:

This should generate SQL that looks like what you're after:

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 */

---

¹ 仍然试图找出是否存在一种无需手动指定参数数量即可映射函数的方法

---

¹Still trying to figure out if there's a way to map a function without manually specifying the number of arguments

这篇关于Nhibernate计数不同(基于多个列)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！