System.Data.OleDb.OleDbException :Duplicate output alias 'Name' when using a DbContext with ODate Aggrgate

[Avrohom]

Microsoft.EntityFrameworkCore.Database.Command: Error: Failed executing DbCommand (4ms) [Parameters=[@__TypedProperty_0='?' (Size = 50)], CommandType='Text', CommandTimeout='30']
SELECT 'TotalDebit' AS Name, IIF(SUM(t0.AMMOUNT OWE) IS NULL, 0.0, SUM(t0.AMMOUNT OWE)) AS Value, 'TotalPaid' AS Name, IIF(SUM(t0.AMMOUNT PAID) IS NULL, 0.0, SUM(t0.AMMOUNT PAID)) AS Value
FROM (
SELECT t.AMMOUNT OWE, t.AMMOUNT PAID, 1 AS Key
FROM TRANSACTION AS t
WHERE t.ID: = @__TypedProperty_0
) AS t0
GROUP BY t0.Key
Microsoft.EntityFrameworkCore.Query: Error: An exception occurred while iterating over the results of a query for context type 'DataModel.Context'.
System.Data.OleDb.OleDbException (0x80040E14): Duplicate output alias 'Name'.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
at EntityFrameworkCore.Jet.Data.JetCommand.ExecuteDbDataReaderCore(CommandBehavior behavior)
at EntityFrameworkCore.Jet.Data.JetCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
--- End of stack trace from previous location ---
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken) at EntityFrameworkCore.Jet.Storage.Internal.JetExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func4 operation, Func4 verifySucceeded, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.AsyncEnumerator.MoveNextAsync()

System.Data.OleDb.OleDbException (0x80040E14): Duplicate output alias 'Name'.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
at EntityFrameworkCore.Jet.Data.JetCommand.ExecuteDbDataReaderCore(CommandBehavior behavior)
at EntityFrameworkCore.Jet.Data.JetCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
--- End of stack trace from previous location ---
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken) at EntityFrameworkCore.Jet.Storage.Internal.JetExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func4 operation, Func4 verifySucceeded, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.AsyncEnumerator.MoveNextAsync()
Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware: Error: An unhandled exception has occurred while executing the request.

System.Data.OleDb.OleDbException (0x80040E14): Duplicate output alias 'Name'.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
at EntityFrameworkCore.Jet.Data.JetCommand.ExecuteDbDataReaderCore(CommandBehavior behavior)
at EntityFrameworkCore.Jet.Data.JetCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
--- End of stack trace from previous location ---
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken) at EntityFrameworkCore.Jet.Storage.Internal.JetExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func4 operation, Func4 verifySucceeded, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.AsyncEnumerator.MoveNextAsync()
at System.Text.Json.Serialization.Converters.IAsyncEnumerableOfTConverter2.OnWriteResume(Utf8JsonWriter writer, TAsyncEnumerable value, JsonSerializerOptions options, WriteStack& state) at System.Text.Json.Serialization.JsonCollectionConverter2.OnTryWrite(Utf8JsonWriter writer, TCollection value, JsonSerializerOptions options, WriteStack& state)
at System.Text.Json.Serialization.JsonConverter1.TryWrite(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state) at System.Text.Json.Serialization.JsonConverter1.WriteCore(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state)
at System.Text.Json.Serialization.Metadata.JsonTypeInfo1.SerializeAsync(Stream utf8Json, T rootValue, CancellationToken cancellationToken, Object rootValueBoxed) at System.Text.Json.Serialization.Metadata.JsonTypeInfo1.SerializeAsync(Stream utf8Json, T rootValue, CancellationToken cancellationToken, Object rootValueBoxed)
at System.Text.Json.Serialization.Metadata.JsonTypeInfo`1.SerializeAsync(Stream utf8Json, T rootValue, CancellationToken cancellationToken, Object rootValueBoxed)
at Microsoft.AspNetCore.Mvc.Formatters.SystemTextJsonOutputFormatter.WriteResponseBodyAsync(OutputFormatterWriteContext context, Encoding selectedEncoding)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|30_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 ---
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)
The program '[42456] SLWebAPI.exe' has exited with code 4294967295 (0xffffffff).

[ChrisJollyAU]

The key part of that error is the following System.Data.OleDb.OleDbException (0x80040E14): Duplicate output alias 'Name'.

With MS Access, you cannot have duplicate names for any of the culumns in your projection.

A quick look at the sql you show, you have two columns, TotalDebit and TotalPaid being given the alias Name. You will also have the same issue with the other two columns being given the alias of Value

[Avrohom]

Many thanks for pointing that out to me @ChrisJollyAU.

I am still able to get it to work by querying those summary values separately.

Thanks for all.

[ChrisJollyAU]

How are you generating the query? you may be able to change it so that the output names are different. Although right off the top of my head, I'm not aware of a LINQ style query that gives the output as something like Name,Value,Name,Value as column names

[Avrohom]

This naming is happening by the 'Microsoft.AspNetCore.OData' server-side package. So no matter how I issue the URL-query client-side the SQL syntax you saw, this is how it will get translated to.

As for fetching those totals individually, I manually construct a HttpClient request for each of them separately (when data backend is MS Access).

[ChrisJollyAU]

Odata? I think we're going to run into problems here. Mostly due to Jet/MS Access limitations and unsupported SQL capabilities

Its not a common scenario to match an MS Access database as a backend to a webserver with using OData to manage the queries.

You may get better mileage depending on what you want by just using the direct tables from the DBConext and using LINQ