NHibernate with AutoMapper and OData

[fairking]

NH v. 5.2.7
Not sure if it's a bug or not but I got an error trying to run Odata with projected AutoMapper Dto:

public class Ticket: IEntity
{
    public virtual string Id { get; set; }
    public virtual string Name { get; set; }
}
...
public class TicketDataVm
{
    public string Id { get; set; }
    public string Name { get; set; }
}
...
// AutoMapper
CreateMap<Ticket, TicketDataVm>();
...
[HttpGet]
[EnableQuery()] // OData
public IActionResult Get()
{
    return Ok(_mapper.ProjectTo<TicketDataVm>(_dbService.Query<Ticket>()));
}

The url: http://localhost:8050/odata/Tickets?$select=id,name gives the following error:

NHibernate.Hql.Ast.ANTLR.QuerySyntaxException: A recognition error occurred. [.Select[MyProject.Core.ViewModels.TicketDataVm, MyProject.Core, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null,Microsoft.AspNet.OData.Query.Expressions.SelectExpandBinder+SelectSome`1[[MyProject.Core.ViewModels.TicketDataVm, MyProject.Core, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]], Microsoft.AspNetCore.OData, Version=7.4.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35](.Select[MyProject.Core.Entities.Ticket, MyProject.Core, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null,MyProject.Core.ViewModels.TicketDataVm, MyProject.Core, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null](NHibernate.Linq.NhQueryable`1[MyProject.Core.Entities.Ticket], Quote((dtoTicket, ) => (new MyProject.Core.ViewModels.TicketDataVm, MyProject.Core, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null()dtoTicket.IddtoTicket.Name)), ), Quote((, ) => (new Microsoft.AspNet.OData.Query.Expressions.SelectExpandBinder+SelectSome`1[[MyProject.Core.ViewModels.TicketDataVm, MyProject.Core, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]], Microsoft.AspNetCore.OData, Version=7.4.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35()p1new Microsoft.AspNet.OData.Query.Expressions.PropertyContainer+NamedPropertyWithNext0`1[[System.String, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]], Microsoft.AspNetCore.OData, Version=7.4.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35()p2Equal(, NULL) ? NULL : .Idnew Microsoft.AspNet.OData.Query.Expressions.PropertyContainer+NamedProperty`1[[System.String, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]], Microsoft.AspNetCore.OData, Version=7.4.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35()p4Equal(, NULL) ? NULL : .Name)), )]
 ---> MismatchedTreeNodeException(87!=3)
   --- End of inner exception stack trace ---
   at NHibernate.Hql.Ast.ANTLR.ErrorCounter.ThrowQueryException()
   at NHibernate.Hql.Ast.ANTLR.HqlSqlTranslator.Translate()
   at NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.Analyze(String collectionRole)
   at NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.DoCompile(IDictionary`2 replacements, Boolean shallow, String collectionRole)
   at NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(IASTNode ast, String queryIdentifier, String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory)
   at NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory)
   at NHibernate.Engine.Query.QueryExpressionPlan.CreateTranslators(IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory)
   at NHibernate.Engine.Query.QueryExpressionPlan..ctor(IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory)
   at NHibernate.Engine.Query.QueryPlanCache.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters)
   at NHibernate.Impl.AbstractSessionImpl.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow)
   at NHibernate.Impl.AbstractSessionImpl.CreateQuery(IQueryExpression queryExpression)
   at NHibernate.Linq.DefaultQueryProvider.PrepareQuery(Expression expression, IQuery& query)
   at NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression)
   at NHibernate.Linq.DefaultQueryProvider.Execute[TResult](Expression expression)
   at Remotion.Linq.QueryableBase`1.System.Collections.IEnumerable.GetEnumerator()
   at System.Text.Json.JsonSerializer.HandleEnumerable(JsonClassInfo elementClassInfo, JsonSerializerOptions options, Utf8JsonWriter writer, WriteStack& state)
   at System.Text.Json.JsonSerializer.Write(Utf8JsonWriter writer, Int32 originalWriterDepth, Int32 flushThreshold, JsonSerializerOptions options, WriteStack& state)
   at System.Text.Json.JsonSerializer.WriteAsyncCore(Stream utf8Json, Object value, Type inputType, JsonSerializerOptions options, CancellationToken cancellationToken)
   at Microsoft.AspNetCore.Mvc.Formatters.SystemTextJsonOutputFormatter.WriteResponseBodyAsync(OutputFormatterWriteContext context, Encoding selectedEncoding)
   at Microsoft.AspNetCore.Mvc.Formatters.SystemTextJsonOutputFormatter.WriteResponseBodyAsync(OutputFormatterWriteContext context, Encoding selectedEncoding)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResultFilterAsync>g__Awaited|29_0[TFilter,TFilterAsync](ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResultExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.ResultNext[TFilter,TFilterAsync](State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeResultFilters()
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeFilterPipelineAsync()
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Builder.Extensions.MapMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.StatusCodePagesMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
   at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
   at Microsoft.AspNetCore.Server.Kestrel.Core.Internal.Http.HttpProtocol.ProcessRequests[TContext](IHttpApplication`1 application)

[fredericDelaporte]

It seems unlikely to me that someone will investigate this with the information you currently supply.
Can you elaborate a minimal but complete example demonstrating the issue please ?

[fairking]

@fredericDelaporte I have created an example you asked: https://github.com/fairking/NhOdataTest

• OK https://localhost:5001/odata/weatherforecast
• OK https://localhost:5001/odata/weatherforecast?$orderby=temperatureF
• OK https://localhost:5001/odata/weatherforecast?$filter=summary eq 'Warm'
• ERROR https://localhost:5001/odata/weatherforecast?$select=id,summary
• ERROR https://localhost:5001/odata/weatherforecast?$apply=groupby((summary), aggregate(temperatureC with average as total))

[fredericDelaporte]

Thanks, I may have a look. But to be fair, this is not at the top of my priorities list.

You may try investigate this a bit more by writing directly the equivalent Linq queries and check how they behave. Maybe you are just running into queries unsupported by Linq-to-nh.

[fairking]

Thanks for your reply @fredericDelaporte :

But to be fair, this is not at the top of my priorities list

I don't know what is the NH priority, but it must be top 1 priority if NH wants to stay on top today.

Hope you will find my map bellow useful:

[maca88]

The url https://localhost:5001/odata/weatherforecast?$select=id,summary throws an error;

This will be fixed by #2079. The query generated when using the mentioned PR:

select
   weatherfor0_.date as col_0_0_,
   weatherfor0_.id as col_1_0_,
   weatherfor0_.summary as col_2_0_,
   weatherfor0_.temperature_c as col_3_0_,
   @p0 + cast(cast(weatherfor0_.temperature_c as DOUBLE) / @p1 as INT) as col_4_0_,
   weatherfor0_.id as col_5_0_ 
from
   weather_forecast weatherfor0_

and this is how the expression from OData looks like:

.New Microsoft.AspNet.OData.Query.Expressions.SelectExpandBinder+SelectSome`1[NhOdataTest.ViewModels.WeatherForecastVm]()
{
    ModelID = "04304d83-8f11-467d-9b6b-3121a399d0f8",
    Container = .New Microsoft.AspNet.OData.Query.Expressions.PropertyContainer+NamedPropertyWithNext0`1[System.Nullable`1[System.Guid]]()
    {
        Name = "id",
        Value = .If (.New NhOdataTest.ViewModels.WeatherForecastVm(){
            Date = (.Extension<Remotion.Linq.Clauses.Expressions.QuerySourceReferenceExpression>).Date,
            Id = (.Extension<Remotion.Linq.Clauses.Expressions.QuerySourceReferenceExpression>).Id,
            Summary = (.Extension<Remotion.Linq.Clauses.Expressions.QuerySourceReferenceExpression>).Summary,
            TemperatureC = (.Extension<Remotion.Linq.Clauses.Expressions.QuerySourceReferenceExpression>).TemperatureC,
            TemperatureF = 32 + (System.Int32)((System.Double)(.Extension<Remotion.Linq.Clauses.Expressions.QuerySourceReferenceExpression>).TemperatureC /
            0,5556D)
        } == null) {
            null
        } .Else {
            (System.Nullable`1[System.Guid])(.Extension<Remotion.Linq.Clauses.Expressions.QuerySourceReferenceExpression>).Id
        },
        Next0 = .New Microsoft.AspNet.OData.Query.Expressions.PropertyContainer+NamedProperty`1[System.String](){
            Name = "summary",
            Value = .If (.New NhOdataTest.ViewModels.WeatherForecastVm(){
                Date = (.Extension<Remotion.Linq.Clauses.Expressions.QuerySourceReferenceExpression>).Date,
                Id = (.Extension<Remotion.Linq.Clauses.Expressions.QuerySourceReferenceExpression>).Id,
                Summary = (.Extension<Remotion.Linq.Clauses.Expressions.QuerySourceReferenceExpression>).Summary,
                TemperatureC = (.Extension<Remotion.Linq.Clauses.Expressions.QuerySourceReferenceExpression>).TemperatureC,
                TemperatureF = 32 + (System.Int32)((System.Double)(.Extension<Remotion.Linq.Clauses.Expressions.QuerySourceReferenceExpression>).TemperatureC /
                0,5556D)
            } == null) {
                null
            } .Else {
                (.Extension<Remotion.Linq.Clauses.Expressions.QuerySourceReferenceExpression>).Summary
            }
        }
    }
}

[fairking]

@maca88 Thanks a lot mate. Any chance to get groupby working too? Or it is a Odata issue? That one I think is most important to get running reports from api.

[maca88]

For group by the following expression is created by Automapper + OData:

value(NHibernate.Linq.NhQueryable`1[NhOdataTest.Entities.WeatherForecast])
.Select(dtoWeatherForecast => new WeatherForecastVm()
{
	Date = dtoWeatherForecast.Date,
	Id = dtoWeatherForecast.Id,
	Summary = dtoWeatherForecast.Summary,
	TemperatureC = dtoWeatherForecast.TemperatureC,
	TemperatureF = (32 + Convert((Convert(dtoWeatherForecast.TemperatureC, Double) / 0,5556), Int32))})
.Select($it => new FlatteningWrapper`1()
{
	Source = $it,
	GroupByContainer = new LastInChain() {Name = "Property0", Value = Convert($it.TemperatureC, Object)}
})
.GroupBy($it => new GroupByWrapper() {GroupByContainer = new LastInChain() {Name = "summary", Value = $it.Source.Summary}})
.Select($it => new AggregationWrapper() {GroupByContainer = $it.Key.GroupByContainer, Container = new LastInChain()
{
	Name = "total",
	Value = Convert(Convert($it, IEnumerable`1).Average($it => Convert($it.GroupByContainer.Value, Int32)), Object)
}})

NHibernate would need to be able to rewrite it to:

value(NHibernate.Linq.NhQueryable`1[NhOdataTest.Entities.WeatherForecast])
.GroupBy($it => new {GroupByContainer = $it.Summary})
.Select($it => new AggregationWrapper() {GroupByContainer = $it.Key.GroupByContainer, Container = new LastInChain() {
	Name = "total",
	Value = Convert(Convert($it, IEnumerable`1).Average($it => Convert($it.TemperatureC, Int32)), Object)
}})

which could be then translated to sql.
Even by just using Automapper the query won't work as the expression contains a Select before GroupBy method, which is reported in #2221.

it is a Odata issue?

Not really, NHibernate is currently not smart enough to translate such qureries.

Any chance to get groupby working too?

Most likely yes, I will give it a try in the near future.

[fairking]

Thanks @maca88 I will include nh dev branch in my project temporary and give it a go with testing. My people are chasing me with reports working on odata api. And $top and $groupby are most important things for the reporting. Making a result like session.Query<MyEntity>().ToList().AsQueriable() is not an option.
No complicated stuff, just very basic queries like https://localhost:5001/odata/weatherforecast?$apply=groupby((summary), aggregate(temperatureC with average as total))
I am very appreciate with your help guys. 👍

[maca88]

@fairking the group by query is fixed in #2322. You can try if it works for the queries that you need.

[fairking]

I will definitely do testing of your branch. I'm really very thankful for your hard work. 👍
This also will allow me to promote the NH as the only tool which supports OData in most cases.
Especcialy in reporting when you have no access to API source code but you want to do queries.

[fairking]

Tested and it's working like a charm 🥇 :

OK https://localhost:5001/odata/weatherforecast
OK https://localhost:5001/odata/weatherforecast?$orderby=temperatureF
OK https://localhost:5001/odata/weatherforecast?$filter=summary eq 'Warm'
OK https://localhost:5001/odata/weatherforecast?$select=id,summary (Fixed: #2079)
OK https://localhost:5001/odata/weatherforecast?$apply=groupby((summary), aggregate(temperatureC with average as total)) (Fixed: #2322)

You have to make sure you are using Microsoft.AspNetCore.Mvc.NewtonsoftJson in your asp.net core app:

services.AddControllers().AddNewtonsoftJson();

Hope both of those fixed are included to the next release.