All geek questions in one place

SQL join criteria for NHibernate

Is there a way to convert this SQL statement to 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)

I found that NHibernate does not support Unions.

+6
source share
2 answers

So, I found two solutions. I execute each request separately, than I complete the results. It is like a union, but it is not executed in the database; it is executed in memory.

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

OR

 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);
+3
source

Try using a view. It can be displayed directly in NHibernate and is independent of the specific database implementation. You must remove the where clauses, and then you can build your NHibernate criteria against "Name" and "FullName".

  (select b1.FieldA as Name, b1.FieldA as FullName from Sale b1 order by b1.Id desc) union (select b2.FieldC as Name, b2.FieldD as FullName from Sale b2 order by b2.Id desc) union (select c.FieldE as Name, c.FieldF as FullName from Client c order by c.Id desc)
+2
source

Source: https://habr.com/ru/post/953191/

More articles:


All Articles