NHibernateQueries

NHibernate Query Samples

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