Difference between revisions of "NHibernateQueries"
(→= Query over, where exists) |
(→Query over, subquery und where exists) |
||
Line 69: | Line 69: | ||
.Where(x => x.Periode.Jahr == periode.Jahr) | .Where(x => x.Periode.Jahr == periode.Jahr) | ||
.WithSubquery.WhereExists(QueryOver.Of(() => b).Where(() => b.BelegGruppe.Id == bg.Id).Select(Projections.Property(() => b.Id))); | .WithSubquery.WhereExists(QueryOver.Of(() => b).Where(() => b.BelegGruppe.Id == bg.Id).Select(Projections.Property(() => b.Id))); | ||
+ | </source> | ||
+ | |||
+ | === Query over, sum, conditional sum und max === | ||
+ | <source lang="csharp"> | ||
+ | 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>(); | ||
</source> | </source> |
Revision as of 13:59, 15 August 2014
NHibernate Query Samples
Contents
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>();