NHibernate - Moving LINQ/Lambda Expression into method changes the generated SQL - Why?

Question

I'm using NHibernate 5.2.0.0 on .NET Framework 4.7.2 and I have noticed that it generates different SQL, depending on whether the LINQ/lambda expression is in a method or not. I would like to put it in a method (to make it reusable and more easy to refactor) and still get the better SQL generated.

Here is the whole statement, the relevant part is DocumentType = .... That's the part I want to use in like 100 other controllers too and don't want to copy & paste it there.

var billingDocumentList = from billingDoc in billingDocuments
                          where branchIdPermissions.Contains(billingDoc.Branch.Id)
                          let taxAmount = billingDoc.BillingDocumentPositions.Sum(p => p.Amount * (p.TaxRate / 100M))
                          select new BillingDocumentOverviewViewModel
                                  {
                                      Id = billingDoc.Id,
                                      BillingDocumentNumber = billingDoc.BillingDocumentNumber,
                                      DocumentStatus = billingDoc.DocumentStatus.Description,
                                      BillingDocumentDate = billingDoc.BillingDocumentDate.Date,
                                      Company = billingDoc.Branch.Company.CompanyNumber + "-" + billingDoc.Branch.Company.Description,
                                      DocumentType =billingDoc.DocumentType.Description2.Translations.Where(x => x.Language.ISO2 == Thread.CurrentThread.CurrentCulture.TwoLetterISOLanguageName || x.Language.ISO2 == "en").Select(n => new { n.Value, fallback = n.Language.ISO2 == "en" ? 1 : 0 }).OrderBy(x => x.fallback).Select(x => x.Value).FirstOrDefault(),
                                      RecipientName1 = billingDoc.RecipientName1,
                                  };

So I tried putting it into the Phrase class (type of Description2 property)

public virtual string InCurrentLocale()
{
    return Translations.Where(x => x.Language.ISO2 == Thread.CurrentThread.CurrentCulture.TwoLetterISOLanguageName || x.Language.ISO2 == "en")
             .Select(n => new { n.Value, fallback = n.Language.ISO2 == "en" ? 1 : 0 })
             .OrderBy(x => x.fallback)
             .Select(x => x.Value)
             .FirstOrDefault();
}

While Nhibernates produces a (fast) subquery for the inline variant, it produces additional standalone queries when moving it into a method, what I want to avoid.

My goal is to produce the same SQL as in first variant, but make it reusable over a huge software product and keep the logic on one place.

I already tried playing around with the method implementation, with extension methods, but nothing worked.

Currently I'm trying to achieve what I want by digging deep into NHibernate. I'm trying to write a custom BaseHqlGeneratorForMethod implementation and put all the Linq statements into that. That would be fine for me. Imaging something like that:

DocumentType = billingDocs.DocumentType.Description2.InCurrentLocale(),
.......
.....

public class InCurrentLocaleGenerator2 : BaseHqlGeneratorForMethod
{
    public InCurrentLocaleGenerator2()
    {
        SupportedMethods = new[]
        {
            ReflectHelper.GetMethodDefinition<Phrase>((x) => x.InCurrentLocale())
        };
    }

    public override HqlTreeNode BuildHql(MethodInfo method, Expression targetObject, ReadOnlyCollection<Expression> arguments, HqlTreeBuilder treeBuilder, IHqlExpressionVisitor visitor)
    {
      Expression<Func<Translation, bool>> languageExp = (x) => x.Language.ISO2 == currentLanguage || x.Language.ISO2 == "en";
      
      /// + the other parts of the complete LINQ statement

        return visitor.Visit(languageExp).AsExpression();
..........
......
}

But I always get different errors, because I'm not well experiencced with writing Expression objects and all that stuff related to that.

Is there any way to achieve what I want? It has not be the HQL way, I would be very glad and thankful for every other idea/way or a guideline how to achieve that with HQL. Thanks!

Answer 1

It is because InCurrentLocale is not an Expression and no one LINQ Provider can parse that. Easiest approach here is to use LINQKit which can inject expression into current Expression Tree:

public virtual Expression<Func<IEnumerable<Translation>, string>> InCurrentLocale()
{
    return (translations) => translations.Where(x => x.Language.ISO2 == Thread.CurrentThread.CurrentCulture.TwoLetterISOLanguageName || x.Language.ISO2 == "en")
             .Select(n => new { n.Value, fallback = n.Language.ISO2 == "en" ? 1 : 0 })
             .OrderBy(x => x.fallback)
             .Select(x => x.Value)
             .FirstOrDefault();
}

And use this function in your query. Don't forget to place .AsExpandable()

var billingDocumentList = from billingDoc in billingDocuments.AsExpandable()
                          where branchIdPermissions.Contains(billingDoc.Branch.Id)
                          let taxAmount = billingDoc.BillingDocumentPositions.Sum(p => p.Amount * (p.TaxRate / 100M))
                          select new BillingDocumentOverviewViewModel
                                  {
                                      Id = billingDoc.Id,
                                      BillingDocumentNumber = billingDoc.BillingDocumentNumber,
                                      DocumentStatus = billingDoc.DocumentStatus.Description,
                                      BillingDocumentDate = billingDoc.BillingDocumentDate.Date,
                                      Company = billingDoc.Branch.Company.CompanyNumber + "-" + billingDoc.Branch.Company.Description,
                                      DocumentType = InCurrentLocale().Invoke(billingDoc.DocumentType.Description2.Translations),
                                      RecipientName1 = billingDoc.RecipientName1,
                                  };