nhibernatequeryovernhibernate-projections

NHibernate projection: How to create AliasToBean projection?


I am trying to convert this inefficient query into one that projects into a dto. Original query looks like this:

var flatFeePolicies = _session.QueryOver<FlatChargeAccessFee>(() => flatChargeAccessFeeAlias)
                                .JoinAlias(x => x.AgreementAccessFee, () => agreementAccessFeeAlias)
                                .JoinQueryOver(x => x.ClientPolicy, () => clientPolicyAlias)
                                .Where(y => agreementAccessFeeAlias.Agreement.Id == request.AgreementId)
                                .List()
                                .Select(x => new FlatChargeAccessFeeInfo()
                                    {
                                        FlatChargeAccessFeeId = x.Id,
                                        ClientName = x.ClientPolicy.Bid.Client.Name,
                                        PolicyNumber = x.ClientPolicy.PolicyNumber,
                                        ClientPolicyId = x.ClientPolicy.Id,
                                        AgreementAccessFeeId = x.AgreementAccessFee.Id,
                                        ShouldCheckBeGenerated = x.ShouldCheckBeGenerated,
                                        MonthlyFee = x.MontlyFeeAmount.Amount.ToString(),
                                        PolicyYear = x.ClientPolicy.PolicyNumber.Year
                                    })
                                .ToList();

I tried it like this:

var flatFeePolicies = _session.QueryOver<FlatChargeAccessFee>(() => flatChargeAccessFeeAlias)
                              .JoinAlias(x => x.AgreementAccessFee, () => agreementAccessFeeAlias)
                              .JoinQueryOver(x => x.ClientPolicy, () => clientPolicyAlias)
                              .Where(y => agreementAccessFeeAlias.Agreement.Id == request.AgreementId)
                              .SelectList(list => list
                                                      .Select(x => x.Id).WithAlias(() => feeInfo.FlatChargeAccessFeeId)
                                                      .Select(x => x.ClientPolicy.Bid.Client.Name).WithAlias(() => feeInfo.ClientName)
                                                      .Select(x => x.ClientPolicy.PolicyNumber).WithAlias(() => feeInfo.PolicyNumber)
                                                      .Select(x => x.ClientPolicy.Id).WithAlias(() => feeInfo.ClientPolicyId)
                                                      .Select(x => x.AgreementAccessFee.Id).WithAlias(() => feeInfo.AgreementAccessFeeId)
                                                      .Select(x => x.ShouldCheckBeGenerated).WithAlias(() => feeInfo.ShouldCheckBeGenerated)
                                                      .Select(x => x.MontlyFeeAmount.Amount.ToString()).WithAlias(() => feeInfo.MonthlyFee)
                                                      .Select(x => x.ClientPolicy.PolicyNumber.Year).WithAlias(() => feeInfo.PolicyYear)
                               )
                               .TransformUsing(Transformers.AliasToBean<FlatChargeAccessFeeInfo>())
                               .List<FlatChargeAccessFeeInfo>();

and I am getting an error that variable "x" has been referenced in scope but was not defined. What is the proper syntax to convert this?

After help from Andrew, here is the correct version that works

ClientPolicy clientPolicyAlias = null;
Client clientAlias = null;
Bid bidAlias = null;
AgreementAccessFee agreementAccessFeeAlias = null;
FlatChargeAccessFee flatChargeAccessFeeAlias = null;
FlatChargeAccessFeeInfo feeInfo = null;


var flatFeePolicies = _session.QueryOver<FlatChargeAccessFee>(() => flatChargeAccessFeeAlias)
                              .JoinAlias(a => a.AgreementAccessFee, () => agreementAccessFeeAlias)
                              .JoinQueryOver(b => b.ClientPolicy, () => clientPolicyAlias)
                              .JoinAlias(b=>b.Bid,()=>bidAlias)
                              .JoinAlias(b=>b.Client, ()=>clientAlias)
                              .Where(c => agreementAccessFeeAlias.Agreement.Id == request.AgreementId)

                              .SelectList(list => list
                                                      .Select(d => d.Id).WithAlias(() => feeInfo.FlatChargeAccessFeeId)
                                                      .Select(e => clientAlias.Name).WithAlias(() => feeInfo.ClientName)
                                                      .Select(e => clientAlias.Number).WithAlias(() => feeInfo.ClientNumber)
                                                      .Select(f => bidAlias.OptionNumber).WithAlias(() => feeInfo.BidOptionNumber)
                                                      .Select(f => bidAlias.Year).WithAlias(()=>feeInfo.PolicyYear)
                                                      .Select(g => clientPolicyAlias.Id).WithAlias(() => feeInfo.ClientPolicyId)
                                                      .Select(h => agreementAccessFeeAlias.Id).WithAlias(() => feeInfo.AgreementAccessFeeId)
                                                      .Select(j => j.ShouldCheckBeGenerated).WithAlias(() => feeInfo.ShouldCheckBeGenerated)
                                                      .Select(k => k.MontlyFeeAmount.Amount).WithAlias(()=>feeInfo.MonthlyFee)

                               )
                               .TransformUsing(Transformers.AliasToBean<FlatChargeAccessFeeInfo>())
                               .List<FlatChargeAccessFeeInfo>();

Solution

  • You're close, a few things though:

    In general, remember that you're writing code that's going to be turned into SQL. In fact, I normally write the SQL I want to generate first and then write the QueryOver that corresponds to that. Hope that helps!