NHibernateQueries

NHibernate Query Samples

OR, Criteria

var query = context.Session.CreateCriteria<Tag>();

query.Add(Restrictions.Or(
    
  Restrictions.Where<Tag>(t => t.NameDe.IsLike(searchString)),
    
  Restrictions.Where<Tag>(t => t.NameEn.IsLike(searchString))));
              

return query.List<Tag>();              


Join without mapping

IList<VATeilnehmerlistePersonViewModel> personenList =
                (from x in Session.Query<VATeilnehmer>()
                join p in Session.Query<Person>() on x.EmployeeID equals p.EmployeeID
                where x.VA.Id == vaId
                select new VATeilnehmerlistePersonViewModel
                           {
                                FullName = p.Fullname,
                                OeName = p.OeName,
                                OeId = p.OeID,
                                OeNameShort = p.OeShort,
                                VaTeilnehmerId = x.Id,
                                TeilnanmeOk = x.TeilnahmeOK,
                                Entschuldigt = x.Gefehlt,
                                
                           }).ToList();

HQL, Latest related object

select item, tag
from MyItem item
    join item.Tags tag
where tag.Id = (                                    
    select  max(tag2.Id)  
    from MyItem item2
        join item2.Tags tag2
    where item2.Id = item.Id
    group by item2.Id     
)

OR, Query Over

using (DbContext context = new DbContext())
{

    Tag tag = null;

    return context.Session.QueryOver<Tag>()
        .Where(t => t.NameDe.IsLike(searchString) || t.NameEn.IsLike(searchString))
        .SelectList(
        list => list.Select(x => x.Id).WithAlias(() => tag.Id)
                    .Select(x => x.NameDe).WithAlias(() => tag.NameDe)
                    .Select(x => x.NameEn).WithAlias(() => tag.NameEn))
                .TransformUsing(Transformers.AliasToBean<Tag>()).List<Tag>();
}          


Query over, SQL Function call

            Vertrag v = null;
            VertragServiceAktuell vsa = null;
            Kostenart kav = null;
            Kostenart kavsa = null;

            var query = Session.QueryOver<BelegGruppe>()
                .Inner.JoinAlias(x => x.Vertrag, () => v)
                .Inner.JoinAlias(() => v.Kostenart, () => kav)
                .Inner.JoinAlias(() => v.VertragServicesAktuell, () => vsa)
                .Left.JoinAlias(() => vsa.Kostenart, () => kavsa)
                .Where(x => x.Id == belegGruppeId)
                .Where(() => vsa.Service.Id == serviceId)
                .Select(Projections.SqlFunction("coalesce",
                                                NHibernateUtil.String,
                                                Projections.Property(() => kavsa.Nummer),
                                                Projections.Property(() => kav.Nummer)));

            return query.SingleOrDefault<string>();

Query over, Grouping und Order by und Skip und Take

            var vertragObjekte = Session.QueryOver<VertragObjekt>()
                                        .Where(x => x.VertragObjektTyp == VertragObjektTyp.Nachher)
                                        .Where(x => x.Vertrag.Id == vertragId)
                                        .OrderByProperty(sortName)
                                        .SelectList(
                                            lst => lst
                                                       .SelectGroup(x => x.ServiceNummer).WithAlias(() => vm.ServiceNummer)
                                                       .SelectGroup(x => x.ServiceBezeichnung).WithAlias(() => vm.ServiceBezeichnung))
                                        .TransformUsing(Transformers.AliasToBean<VertragObjekteServiceViewModel>())
                                        .Skip(startRowIndex)
                                        .Take(maximumRows)
                                        .List<VertragObjekteServiceViewModel>();

Query over, Disjunktion, Concunction

                    var disjunction = new Disjunction();
                    var conjunction = new Conjunction();
                    conjunction.Add(Restrictions.In(Projections.Property(() => v.Id), vertragIds));
                    conjunction.Add(() => v.Typ == VertragsTyp.Vertrag);
                    disjunction.Add(conjunction);
                    conjunction = new Conjunction();
                    conjunction.Add(Restrictions.In(Projections.Property(() => v.VaterVertrag.Id), vertragIds));
                    conjunction.Add(() => v.Typ == VertragsTyp.Untervertrag);
                    disjunction.Add(conjunction);
                    
                    query
                        .Where(disjunction);

Query over, subquery und where exists

          Beleg b = null;
            BelegGruppe bg = null;
            Vertrag v = null;
            Kunde k = null;

            var query = Session.QueryOver(() => bg)
                .Left.JoinAlias(x => x.Vertrag, () => v)
                .Left.JoinAlias(() => v.Kunde, () => k)
                .Where(x => x.VorschauFreigegeben)
                .Where(x => x.Periode.Monat == periode.Monat)
                .Where(x => x.Periode.Jahr == periode.Jahr)
                .WithSubquery.WhereExists(QueryOver.Of(() => b).Where(() => b.BelegGruppe.Id == bg.Id).Select(Projections.Property(() => b.Id)));

Query over, sum, conditional sum und max

    
            VertragWorkflow vw = null;
            WorkflowViewModel vm = null;

            var query = GetWorkflowsQuery(kundeId, wft, freigabestelleUser, senderUserId, serviceNr, leistungNr, false);

            var wfModels = query.Select(
                Projections.Group<Workflow>(x => x.Id).WithAlias(() => vm.WorkflowId),
                Projections.Group<Workflow>(x => x.WorkflowType).WithAlias(() => vm.WorkflowType),
                Projections.Group<Workflow>(x => x.Status).WithAlias(() => vm.WorkflowStatus),
                Projections.Group<Workflow>(x => x.InsertDate).WithAlias(() => vm.InsertDate),
                Projections.Group<Workflow>(x => x.InsertUser).WithAlias(() => vm.UserId),
                Projections.Group<Workflow>(x => x.KundeNr).WithAlias(() => vm.KundeNr),
                Projections.Group<Workflow>(x => x.ServiceNr).WithAlias(() => vm.ServiceNr),
                Projections.Group<Workflow>(x => x.LeistungNr).WithAlias(() => vm.LeistungNr),
                Projections.Group<Workflow>(x => x.Von).WithAlias(() => vm.Von),
                Projections.Group<Workflow>(x => x.Bis).WithAlias(() => vm.Bis),
                Projections.Group<Workflow>(x => x.Preis).WithAlias(() => vm.Preis),
                Projections.Group<Workflow>(x => x.Menge).WithAlias(() => vm.Menge),
                Projections.Group<Workflow>(x => x.ServiceBezeichnung).WithAlias(() => vm.ServiceBezeichnung),
                Projections.Group<Workflow>(x => x.BasisLeistungNr).WithAlias(() => vm.BasisLeistungNr),
                Projections.Group<Workflow>(x => x.BasisLeistungBezeichnung).WithAlias(() => vm.BasisLeistungBezeichnung),
                Projections.Group<Workflow>(x => x.LeistungBezeichnung).WithAlias(() => vm.LeistungBezeichnung),
                Projections.Max(Projections.Property(() => vw.Nachtrag.Id)).WithAlias(() => vm.NachtragId),
                Projections.Sum(Projections.Conditional(Restrictions.IsNull(Projections.Property(() => vw.Nachtrag.Id)), Projections.Constant(0), Projections.Constant(1))).WithAlias(() => vm.AnzahlNachtraege)
            )
            .OrderByProperty(sortName)
            .TransformUsing(Transformers.AliasToBean<WorkflowViewModel>())
            .Skip(startRowIndex)
            .Take(maximumRows)
            .List<WorkflowViewModel>();

Join mit mehreren Kriterien

           BelegGruppe bg = null;
            Beleg b = null;
            Vertrag v = null;
            Kunde k = null;
            FakturaObjekt fo = null;
            Service s = null;
            FakturaParameterService fps = null;
            FakturaParameterKunde fpk = null;

            IList<string> serviceNrList = Session.QueryOver(() => bg)
                .Left.JoinAlias(() => bg.Vertrag, () => v)
                .Left.JoinAlias(() => v.Kunde, () => k)
                .Left.JoinAlias(() => k.FakturaParameterKunden, () => fpk, x => x.Periode == bg.Periode)
                .Left.JoinAlias(() => bg.Belege, () => b)
                .Left.JoinAlias(() => b.FakturaObjekte, () => fo)
                .Left.JoinAlias(() => fo.Service, () => s)
                .Left.JoinAlias(() => fpk.FakturaParameterServices, () => fps, x => x.Service.Id == s.Id)                
                .SelectList(list => list.SelectGroup(() => s.Nummer))
                .Where(() => bg.Id == belegGruppeId && fps.Id == null)
                .List<string>();

Query over mit subselect

           Beleg b = null;
            FakturaObjekt fo = null;
            InventarObjektFakturaObjekt iofo = null;
            InventarObjekt io = null;
            InventarDetail id = null;
            Attribut at = null;
            Service s = null;
            Leistung l = null;
            BelegGruppe bg = null;
            Vertrag v = null;
            Vertrag vv = null;
            Kunde k = null;
            FakturaParameterKunde fpk = null;
            FakturaParameterService fps = null;
            UmbuchungViewModel uvm = null;


            var foIdSubQuery = QueryOver.Of<FakturaObjektLevelLeistung>()
                .Where(x => x.BelegId == belegId);

            // Die SE/BL/ZL müssen nicht mehr zwingend aktiv sein, da die Umbuchung auch später als im Verrechnungsmonat anfallen kann

            IQueryOver<Beleg, Beleg> query = Session.QueryOver(() => b)
                .JoinAlias(() => b.FakturaObjekte, () => fo, JoinType.InnerJoin)
                .JoinAlias(() => fo.InventarObjektFakturaObjekte, () => iofo, JoinType.InnerJoin)
                .JoinAlias(() => iofo.InventarObjekt, () => io, JoinType.InnerJoin)
                .JoinAlias(() => io.InventarDetails, () => id, JoinType.LeftOuterJoin,
                    Restrictions.Where(() => (
                        io.InventarTyp.Id == InventarTyp.Telkon
                        || io.InventarTyp.Id == InventarTyp.TelkonSwisscom
                        || io.InventarTyp.Id == InventarTyp.TelkonSunriseMobile)))
                .JoinAlias(() => id.Attribut, () => at, JoinType.LeftOuterJoin,
                    Restrictions.Where(() => at.Name == Attribut.NumberJoinName))
                .JoinAlias(() => fo.Service, () => s, JoinType.InnerJoin)
                .JoinAlias(() => fo.Leistung, () => l, JoinType.InnerJoin)
                .JoinAlias(() => b.BelegGruppe, () => bg, JoinType.InnerJoin)
                .JoinAlias(() => bg.Vertrag, () => v, JoinType.InnerJoin)
                .JoinAlias(() => v.VaterVertrag, () => vv, JoinType.InnerJoin)
                .JoinAlias(() => v.Kunde, () => k, JoinType.InnerJoin)
                .JoinAlias(() => k.FakturaParameterKunden, () => fpk, JoinType.InnerJoin,
                    Restrictions.Where(
                        () => bg.Periode.Jahr == fpk.Periode.Jahr && bg.Periode.Monat == fpk.Periode.Monat))
                .JoinAlias(() => fpk.FakturaParameterServices, () => fps, JoinType.InnerJoin,
                    Restrictions.Where(() => vv.Id == fps.Vertrag.Id && s.Id == fps.Service.Id))
                .Where(() => b.Id == belegId)
                .Where(() => fo.Kontierung != iofo.Kontierung || fo.KontierungsTyp != iofo.KontierungsTyp)
                .Where(() => io.NichtVerrechnet == false)
                .Where(() => !fpk.UmbuchungTeilweise || fps.Umbuchung)
                .Where(() => !(io.InventarTyp.Id == InventarTyp.Telkon
                               || io.InventarTyp.Id == InventarTyp.TelkonSwisscom
                               || io.InventarTyp.Id == InventarTyp.TelkonSunriseMobile) || at.Id != null)
                .Where(() => fo.Menge > 0 && io.Menge > 0)
                .Where(() => fo.Preis > 0m)
                .WithSubquery.WhereProperty(x => x.Id).In(foIdSubQuery.Select(x => x.Id));

            IQueryOver<Beleg, Beleg> result = query
                .Select(
                    Projections.Group(() => s.Id).WithAlias(() => uvm.ServiceId),
                    Projections.Group(() => l.Id).WithAlias(() => uvm.LeistungId),
                    Projections.Group(() => fo.Kontierung).WithAlias(() => uvm.Kontierung),
                    Projections.Group(() => fo.KontierungsTyp).WithAlias(() => uvm.KontierungsTyp),
                    Projections.Group(() => iofo.Kontierung).WithAlias(() => uvm.KontierungIofo),
                    Projections.Group(() => iofo.KontierungsTyp).WithAlias(() => uvm.KontierungsTypIofo),
                    Projections.Group(() => fo.Menge).WithAlias(() => uvm.MengeFo),
                    Projections.Sum(() => io.Menge).WithAlias(() => uvm.MengeIo),
                    Projections.Group(() => fo.Preis).WithAlias(() => uvm.Preis),
                    Projections.Group(() => iofo.AnteilProzent).WithAlias(() => uvm.AnteilProzent),
                    Projections.Group(() => fo.KostenartNummer).WithAlias(() => uvm.KostenartNummer),
                    Projections.Group(() => s.Bezeichnung).WithAlias(() => uvm.ServiceBezeichnung),
                    Projections.Group(() => l.Bezeichnung).WithAlias(() => uvm.LeistungBezeichnung),
                    Projections.Group(() => s.Nummer).WithAlias(() => uvm.ServiceNummer),
                    Projections.Group(() => l.Nummer).WithAlias(() => uvm.LeistungNummer),
                    Projections.Group(() => fo.PreisTyp).WithAlias(() => uvm.PreisTyp),
                    Projections.Group(() => l.MaterialNummer).WithAlias(() => uvm.MaterialNummer),
                    Projections.Group(() => fo.KontierungsArt).WithAlias(() => uvm.KontierungsArt),
                    Projections.Group(() => io.InventarTyp.Id).WithAlias(() => uvm.InventarTypId),
                    Projections.Group(() => id.Value).WithAlias(() => uvm.NumberJoin),
                    Projections.Group(() => io.UserId).WithAlias(() => uvm.UserId)
                );

            return result
                .TransformUsing(Transformers.AliasToBean<UmbuchungViewModel>())
                .List<UmbuchungViewModel>();