如何在LINQ to NHibernate中的查询的select子句中提供默认值

Question

我手头的任务是直接从数据库中提取DTO。作为ORM，我使用带有LINQ的NHibernate作为查询手段。下面是我的域类和一个DTO类，我用它们向客户端返回数据（类被缩减为只包含几个属性来证明这一点）。

public class DocLanguage
{
  public Guid Id { get; set; }
  public string Name { get; set; }
  public string PublicCode { get; set; }
}
public class Document
{
  public Guid Id { get; set; }
}
public class OutgoingDocument: Document
{
  public DocLanguage DocLanguage { get; set; }
}
public class OutgoingDocumentDto
{
  public Guid Id { get; set; }
  public Guid DocLanguageId { get; set; }
  public string DocLanguageName { get; set; }
}

下面是一个我用来从数据库加载DTO的查询。

IQueryable<OutgoingDocument> documents = GetQueryable();
var query = from doc in documents
            select new OutgoingDocumentDto
                       {
                         Id = doc.Id,
                         DocLanguageId = doc.DocLanguage.Id,
                         DocLanguageName = doc.DocLanguage.Name
                       }
var documentList = query.ToList();

它生成以下SQL。

exec sp_executesql N'select
  outgoingdo0_.documentId as col_0_0_,
  doclanguag1_.Id as col_1_0_,
  doclanguag1_.name as col_2_0_
from OutgoingDocuments outgoingdo0_
  inner join Documents outgoingdo0_1_
    on outgoingdo0_.documentId=outgoingdo0_1_.Id
  left outer join DicDocLanguages doclanguag1_
    on outgoingdo0_1_.docLanguageId=doclanguag1_.Id'

在docLanguageId字段中没有NULL值（这不是强制性的）之前，它工作得很好。在另一种情况下，它抛出了一个异常：

NHibernate.Exceptions.GenericADOException was unhandled by user code
  Message=Could not execute query[SQL: SQL not available]
  Source=NHibernate
  SqlString=SQL not available
  StackTrace:
   at NHibernate.Impl.SessionImpl.List(IQueryExpression queryExpression, QueryParameters queryParameters, IList results) in d:CSharpNHNHnhibernatesrcNHibernateImplSessionImpl.cs:line 653
   at NHibernate.Impl.AbstractSessionImpl.List(IQueryExpression queryExpression, QueryParameters parameters) in d:CSharpNHNHnhibernatesrcNHibernateImplAbstractSessionImpl.cs:line 92
   at NHibernate.Impl.ExpressionQueryImpl.List() in d:CSharpNHNHnhibernatesrcNHibernateImplExpressionQueryImpl.cs:line 61
   at NHibernate.Linq.DefaultQueryProvider.ExecuteQuery(NhLinqExpression nhLinqExpression, IQuery query, NhLinqExpression nhQuery) in d:CSharpNHNHnhibernatesrcNHibernateLinqDefaultQueryProvider.cs:line 103
   at NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression) in d:CSharpNHNHnhibernatesrcNHibernateLinqDefaultQueryProvider.cs:line 35
   at NHibernate.Linq.DefaultQueryProvider.Execute[TResult](Expression expression) in d:CSharpNHNHnhibernatesrcNHibernateLinqDefaultQueryProvider.cs:line 40
   at Remotion.Linq.QueryableBase`1.GetEnumerator() in :line 0
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
  InnerException: System.Reflection.TargetInvocationException
   HResult=-2146232828
   Message=Exception has been thrown by the target of an invocation.
   Source=mscorlib
   StackTrace:
        at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
        at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
        at System.Delegate.DynamicInvokeImpl(Object[] args)
        at System.Delegate.DynamicInvoke(Object[] args)
        at NHibernate.Linq.ResultTransformer.TransformTuple(Object[] tuple, String[] aliases) in d:CSharpNHNHnhibernatesrcNHibernateLinqResultTransformer.cs:line 25
        at NHibernate.Hql.HolderInstantiator.Instantiate(Object[] row) in d:CSharpNHNHnhibernatesrcNHibernateHqlHolderInstantiator.cs:line 80
        at NHibernate.Hql.Ast.ANTLR.Loader.QueryLoader.GetResultList(IList results, IResultTransformer resultTransformer) in d:CSharpNHNHnhibernatesrcNHibernateHqlAstANTLRLoaderQueryLoader.cs:line 302
        at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) in d:CSharpNHNHnhibernatesrcNHibernateLoaderLoader.cs:line 1497
        at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) in d:CSharpNHNHnhibernatesrcNHibernateLoaderLoader.cs:line 1491
        at NHibernate.Hql.Ast.ANTLR.Loader.QueryLoader.List(ISessionImplementor session, QueryParameters queryParameters) in d:CSharpNHNHnhibernatesrcNHibernateHqlAstANTLRLoaderQueryLoader.cs:line 288
        at NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.List(ISessionImplementor session, QueryParameters queryParameters) in d:CSharpNHNHnhibernatesrcNHibernateHqlAstANTLRQueryTranslatorImpl.cs:line 112
        at NHibernate.Engine.Query.HQLQueryPlan.PerformList(QueryParameters queryParameters, ISessionImplementor session, IList results) in d:CSharpNHNHnhibernatesrcNHibernateEngineQueryHQLQueryPlan.cs:line 105
        at NHibernate.Impl.SessionImpl.List(IQueryExpression queryExpression, QueryParameters queryParameters, IList results) in d:CSharpNHNHnhibernatesrcNHibernateImplSessionImpl.cs:line 643
   InnerException: System.NullReferenceException
        HResult=-2147467261
        Message=Object reference not set to an instance of an object.
        Source=Anonymously Hosted DynamicMethods Assembly
        StackTrace:
             at lambda_method(Closure , Object[] )
        InnerException: 

我尝试用以下代码显式地检查无效性，并为DTO属性提供默认值：

var query = from doc in documents
            select new OutgoingDocumentDto
                       {
                         Id = doc.Id,
                         DocLanguageId = doc.DocLanguage == null ?
                                             Guid.Empty :
                                             doc.DocLanguage.Id,
                         DocLanguageName = doc.DocLanguage == null ?
                                               Guid.Empty :
                                               doc.DocLanguage.Name
                       }

它会导致此SQL。

exec sp_executesql N'select
  outgoingdo0_.documentId as col_0_0_,
  doclanguag1_.Id as col_1_0_,
  doclanguag1_.Id as col_2_0_,
  doclanguag1_.Id as col_3_0_,
  doclanguag1_.name as col_4_0_,
  doclanguag1_.Id as Id40_0_,
  doclanguag1_.Id as Id40_1_,
  doclanguag1_.name as nameRU40_0_,
  doclanguag1_.publicCode as publicCode40_0_,
  doclanguag1_.name as nameRU40_1_,
  doclanguag1_.publicCode as publicCode40_1_
from OutgoingDocuments
  outgoingdo0_ inner join Documents outgoingdo0_1_
    on outgoingdo0_.documentId=outgoingdo0_1_.Id
  left outer join DicDocLanguages doclanguag1_
    on outgoingdo0_1_.docLanguageId=doclanguag1_.Id'

NHibernate LINQ提供程序似乎直接将LINQ查询中的所有成员访问表达式转换为SQL查询，从而产生重复的字段选择。事实上，它是有效的，并且优化器可能使这个查询过程与前一个查询过程一样快。但它很丑陋，而且随着涉及更多房产，它变得越来越丑陋。

我认为这是一个常见的任务，我想知道是否存在为select子句中的属性提供默认值的正确方法。

Answer 1

这可能不是最好的解决方案，但我发现了一篇博客文章，向您展示了如何让NHibernate识别和翻译Linq查询中使用的自定义扩展方法，该示例专门处理"联合"操作。解决方案是创建一个Coalesce（）扩展方法，然后通过为该方法指定一个自定义HQL生成器来向NHibernate"注册"该方法，然后使用默认配置中指定的自定义注册表生成器将它们添加到NHibernat的"知识库"中以生成HQL。

理论上，您可以对任何将转换为目标模式的有效SQL的扩展方法执行此操作；您甚至可以使用它来让NHibernate调用您在数据库中放置的自定义SQL函数（但我跑题了）。

Answer 2

您可以在DTO级别上解决此问题。您可以将DB对象绑定到内部可为null的字段，并将其用作公共属性的后备字段，在其中放置默认值逻辑。像这样：

internal virtual DocLanguage docLanguage {get;set;}
public int DocLanguageName 
{
    get{ return docLanguage == null ? Guid.Empty : docLanguage.Name; }
}