NHibernate projection: How to create AliasToBean projection?

Question

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

What line does that error occur on? Also, you're going to have to explicitly join to `ClientPolicy.Bid` and then again from `Bid` to `Client` — Andrew Whitaker, Jul 12 '13 at 17:55
@Andrew: On MonthlyFeeAmount, and as you said, now when it has been commented out, it complains about the ones that have not been explicitly joined. — epitka, Jul 12 '13 at 18:00
Ah ok, remove the `.ToString` from monthly fee amount--that should get rid of that error. QueryOver won't know how to translate `ToString()` into a SQL command. — Andrew Whitaker, Jul 12 '13 at 18:04
@AndrewWhitaker: But how do you convert from decimal to string, since amount is decimal and target is string? — epitka, Jul 12 '13 at 18:14
I'm not sure you can inside of the QueryOver query. I'd recommend projecting to a decimal and then converting it down the line. Another option would be to provide a method on your result class that does the conversion. — Andrew Whitaker, Jul 12 '13 at 18:22
@AndrewWhitaker: Ah ok, then same thing would be true if property on the entity was a internal mapped atribute. For example PolicyNumber contatenates client, bid, and one more attribute to create it. This would not be possible to project? There is not step in between that can be injected before destination value is set? — epitka, Jul 12 '13 at 18:25
Yes, in other words if you have a getter that computes that property, QueryOver will not be able to calculate it. This is because QueryOver basically is going to try to translate your code directly into SQL. If the property does not exist in the database, you'll get an error. — Andrew Whitaker, Jul 12 '13 at 18:27
@AndrewWhitaker: Thank you very much for your help, I can take it from here. How do I accept your answer? — epitka, Jul 12 '13 at 18:31
I'll add my comments as an answer. No problem! I know QueryOver is terribly documented. Figuring it out has taken years :) — Andrew Whitaker, Jul 12 '13 at 18:32

score 1 · Accepted Answer · answered Jul 12 '13 at 18:35

You're close, a few things though:

This select:
```
.Select(x => x.MontlyFeeAmount.Amount.ToString()).WithAlias(() => feeInfo.MonthlyFee)
```
will not work. QueryOver attempts to turn your code directly into SQL. If the property does not exist as a column in the database, the query won't work properly (unless you're using a mapped custom type, QueryOver can handle those)
Nested property access won't work either:
```
.Select(x => x.ClientPolicy.Bid.Client.Name).WithAlias(() => feeInfo.ClientName)
```
for a similar reason listed above. QueryOver will attempt to turn your property access directly into SQL. You'll need to explicitly join from ClientPolicy to Bid to Client.

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!

NHibernate projection: How to create AliasToBean projection?

1 Answers1