SQL Union to NHibernate Criteria

So, I've found two solutions. I perform each query separately than I concat the results. It's like a Union, but isn't performed in the DB, it's performed 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);

Try using a view. It can be mapped directly in NHibernate and does not rely on a specific database implementation. You should 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)