Syntactic element ` is not correct

[barbhackk]

Good morning,

I have a problem when I want to query a table on an AS400 server with DB2.

I created a new .NET Core 8.0 API project
For this I use an ODBC provider with EntityFrameworkCore.Jet.Odbc.

When I execute:

var exchg = from s in _context.Exchange select s.Nim;
var ex = exchg.ToArray();

I have an error:
Syntactic element ` is not correct. Possible elements: ( + * - ?: ON DAY INF LAG NAN NOT OFF RID ROW RRN CASE CAST CHAR

If I display the generated query I have quotes, not understandable by db2, which have been added :

SELECT `y`.`ADPABC`
FROM `YXCTRT` AS `y`

Stack Trace :

System.Data.Odbc.OdbcException (0x80131937): ERROR [42000] [IBM][Pilote ODBC System i Access][DB2 for i5/OS]SQL0104 - Elément syntaxique ` n'est pas correct. Eléments possibles : ( + * - ? : ON DAY INF LAG NAN NOT OFF RID ROW RRN CASE CAST CHAR.
   at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, SQLRETURN retcode)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
   at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at EntityFrameworkCore.Jet.Data.JetCommand.ExecuteDbDataReaderCore(CommandBehavior behavior)
   at EntityFrameworkCore.Jet.Data.JetCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__21_0(DbContext _, Enumerator enumerator)
   at EntityFrameworkCore.Jet.Storage.Internal.JetExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at DqsConnectApi.Controllers.ExchangeController.Get() in C:\Users\Ext.DOUTRE\OneDrive - MLP SAS\Documents\Analyse DQS\DQS_API\DqsConnectApi\Controllers\ExchangeController.cs:line 26
   at lambda_method2(Closure, Object, Object[])
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncActionResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
   at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)

HEADERS
=======
Accept: */*
Connection: keep-alive
Host: localhost:5278
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/124.0.0.0 Safari/537.36
Accept-Encoding: gzip, deflate, br, zstd
Accept-Language: fr-FR,fr;q=0.9,en-US;q=0.8,en;q=0.7
Referer: http://localhost:5278/swagger/index.html
sec-ch-ua: "Chromium";v="124", "Google Chrome";v="124", "Not-A.Brand";v="99"
sec-ch-ua-mobile: ?0
sec-ch-ua-platform: "Windows"
Sec-Fetch-Site: same-origin
Sec-Fetch-Mode: cors
Sec-Fetch-Dest: empty

My models class :

[Keyless]
[Table("YXCTRT")]
public class Exchange
{
    [Column("ADPABC")]
    public string? Nim { get; set; }
}

EF Core version: 8.0.4
Database provider: EntityFrameworkCore.Jet.Odbc (Driver={iSeries Access ODBC Driver};System...)
Target framework: .NET 8.0
Operating system: Windows 10
IDE: VS code

Thanks,

[ErikEJ]

Since this is an unsupported scenario, you are most likely better off simply using raw ADO.NET

[roji]

@barbhackk "Jet" usually refers to accessing Microsoft Access files - that's very different from the IBM DB2 database. Are you sure you're using the right EF provider? Take a look at our providers page to see available providers for DB2.