有一种方法可以将此SQL语句转换为NHibernate条件?
(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不支持工会。
因此,我找到了两种解决方案。我会分别执行每个查询,而不是合并结果。这就像一个联合,但不是在数据库中执行,而是在内存中执行。
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);