有没有办法将此 SQL 语句转换为 NHibernate Criteria？

(select b1.FieldA as Name, b1.FieldA as FullName from Sale b1 where b1.FieldA like '%john%' or b1.FieldA like '%john%' order by b1.Id desc)
union 
(select b2.FieldC as Name, b2.FieldD as FullName from Sale b2 where b2.FieldC like '%john%' or b2.FieldD like '%john%' order by b2.Id desc)
union 
(select c.FieldE as Name, c.FieldF as FullName from Client c where c.FieldE like '%john%' or c.FieldF like '%john%' order by c.Id desc)

我发现 NHibernate 不支持联合。

最佳答案

所以，我找到了两个解决方案。我分别执行每个查询而不是连接结果。它就像一个 Union，但不是在 DB 中执行，而是在内存中执行。

var b1 = Session.Query<Sale>()
            .Where(x => x.FiledA.Contains(filter) || x.FiledB.Contains(filter))
            .OrderBy(x => x.Id)
            .GroupBy(x => new { x.FiledA, x.FiledB })
            .Select(x => new Foo { FullName = x.Key.FiledA, Name = x.Key.FiledB })
            .Take(30)
            .ToList();

var b2 = Session.Query<Sale>()
            .Where(x => x.FiledC.Contains(filter) || x.FiledD.Contains(filter))
            .OrderBy(x => x.Id)
            .GroupBy(x => new {x.FiledC, x.FiledD})
            .Select(x => new Foo {FullName = x.Key.FiledC, Name = x.Key.FiledD})
            .Take(30)
            .ToList();


var c = Session.Query<Client>()
            .Where(x => x.FiledE.Contains(filter) || x.FiledF.Contains(filter))
            .OrderBy(x => x.Id)
            .GroupBy(x => new { x.FiledE, x.FiledF })
            .Select(x => new Foo { FullName = x.Key.FiledE, Name = x.Key.FiledF })
            .Take(30)
            .ToList();

return b1.Concat(b2)
         .Concat(c)
         .ToList()
         .GroupBy(x => new { x.Name, x.FullName })
         .Select(x => x.First())
         .Take(30);

或

var b1 = Session.CreateCriteria<Sale>()
    .SetProjection(Projections.ProjectionList()
        .Add(Projections.Distinct(Projections.Property("FiledA")), "Name")
        .Add(Projections.Property("FiledB"), "FullName"))
    .Add(Restrictions.Or(Restrictions.InsensitiveLike("FiledA", filter),
        Restrictions.InsensitiveLike("FiledB", filter)))
    .AddOrder(Order.Desc("Id"))
    .SetMaxResults(30)
    .SetResultTransformer(Transformers.AliasToBean<Foo>())
    .List<Foo>();

var b2 = Session.CreateCriteria<Sale>()
    .SetProjection(Projections.ProjectionList()
        .Add(Projections.Distinct(Projections.Property("FiledC")), "Name")
        .Add(Projections.Property("FiledD"), "FullName"))
    .Add(Restrictions.Or(Restrictions.InsensitiveLike("FiledC", filter),
        Restrictions.InsensitiveLike("FiledD", filter)))
    .AddOrder(Order.Desc("Id"))
    .SetMaxResults(30)
    .SetResultTransformer(Transformers.AliasToBean<Foo>())
    .List<Foo>();

var c = Session.CreateCriteria<Client>()
    .SetProjection(Projections.ProjectionList()
        .Add(Projections.Distinct(Projections.Property("FiledE")), "Name")
        .Add(Projections.Property("FieldF"), "FullName"))
    .Add(Restrictions.Or(Restrictions.InsensitiveLike("FiledE", filter),
        Restrictions.InsensitiveLike("FieldF", filter)))
    .AddOrder(Order.Desc("Id"))
    .SetMaxResults(30)
    .SetResultTransformer(Transformers.AliasToBean<Foo>())
    .List<Foo>();

return b1.Concat(b2)
         .Concat(c)
         .ToList()
         .GroupBy(x => new {x.FullName, x.Name})
         .Select(x => x.First())
         .Take(30);

关于SQL 联合到 NHibernate 标准，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/18623146/