How to provide default values in select clause of a query in LINQ to NHibernate

Question

My task at hand is to extract DTOs directly from the DB. As ORM I use NHibernate with LINQ as a means of querying. Below are my domain classes and a DTO class which I use to return data to the client side (classes are reduced to contain only a few properties to demonstrate the point).

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; }
}

Here is a query I use to load DTOs from the DB.

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

It produces the following 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'

It works great until there is no NULL value in docLanguageId field (which is not mandatory). In another case it throws an exception:

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:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\SessionImpl.cs:line 653
   at NHibernate.Impl.AbstractSessionImpl.List(IQueryExpression queryExpression, QueryParameters parameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\AbstractSessionImpl.cs:line 92
   at NHibernate.Impl.ExpressionQueryImpl.List() in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\ExpressionQueryImpl.cs:line 61
   at NHibernate.Linq.DefaultQueryProvider.ExecuteQuery(NhLinqExpression nhLinqExpression, IQuery query, NhLinqExpression nhQuery) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Linq\DefaultQueryProvider.cs:line 103
   at NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Linq\DefaultQueryProvider.cs:line 35
   at NHibernate.Linq.DefaultQueryProvider.Execute[TResult](Expression expression) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Linq\DefaultQueryProvider.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:\CSharp\NH\NH\nhibernate\src\NHibernate\Linq\ResultTransformer.cs:line 25
        at NHibernate.Hql.HolderInstantiator.Instantiate(Object[] row) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Hql\HolderInstantiator.cs:line 80
        at NHibernate.Hql.Ast.ANTLR.Loader.QueryLoader.GetResultList(IList results, IResultTransformer resultTransformer) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Hql\Ast\ANTLR\Loader\QueryLoader.cs:line 302
        at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1497
        at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1491
        at NHibernate.Hql.Ast.ANTLR.Loader.QueryLoader.List(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Hql\Ast\ANTLR\Loader\QueryLoader.cs:line 288
        at NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.List(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Hql\Ast\ANTLR\QueryTranslatorImpl.cs:line 112
        at NHibernate.Engine.Query.HQLQueryPlan.PerformList(QueryParameters queryParameters, ISessionImplementor session, IList results) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Engine\Query\HQLQueryPlan.cs:line 105
        at NHibernate.Impl.SessionImpl.List(IQueryExpression queryExpression, QueryParameters queryParameters, IList results) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\SessionImpl.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: 

I've tried to explicitly check for nullity and provide default values to the DTO properties with this code:

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
                       }

It results in this 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'

It seems that NHibernate LINQ provider bluntly converts all member access expressions in the LINQ query into SQL query producing duplicate field selects. In fact, it works, and maybe the optimizer makes this query process as fast as the previous one. But it is ugly and is getting uglier as more properties are involved.

I think this task is a common one and I'd like to know if there exists the right way of providing properties in the select clause with default values.

Answer 1

This probably isn't the greatest solution, but I found a blog post that shows you how to get NHibernate to recognize and translate custom extension methods used in your Linq queries, and the example specifically deals with the "coalesce" operation. The solution is to create a Coalesce() extension method, then "register" it with NHibernate by specifying a custom HQL generator for the method, then using a custom registry generator specified in the default configuration to add them to NHibernate's "knowledge base" for HQL generation.

You could theoretically do this for any extension method you have that would convert to valid SQL for your target schema; you might even use this to make NHibernate call custom SQL functions you've put in your database (but I digress).

Answer 2

You can solve this problem on DTO level. You can bind your DB object to internal nullable field and use it as a backing field for your public property, where you'll place default value logic. Like so:

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