alexwiese/entityframeworkcore.openedge

Entity Framework Core provider for Progress OpenEdge

entityframeworkcore.openedge's Issues

How to work with datetime fields in a update transaction?

Hello @alexwiese,
First at all, your code is help me too much.
I'm trying to add a record, but a datetime field is crashing code execution, without the field the code execute perfectly, but with it doesn't and return this message:
ERROR [42522] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Column "TIMESTAMP" cannot be found or is not specified for query. (13865).
May you help me?

Follow the link with an example code and the table's .df.
https://github.com/NatanVieira/ErrorTimestamp.git

thanks.

is OpenEdge 10.2 A version supported

in appsettings.json file we have connection string as:

"ConnectionStrings": {
"OpenEdgeDbContextConnStr": "DRIVER={Progress OpenEdge 10.2A driver};host=localhost;port=12345;db=modhotel;uid=myUser;PWD=myPass;DIL=READ UNCOMMITTED;",
}

When we run the below code

public async Task<IEnumerable> GetTest()
{
using (var ctx = dbContext)
{
var test = ctx.o_kuradi.ToList();
...
}
}

we get the following error:

ERROR [IM003] Specified driver could not be loaded due to system error 193: (Progress OpenEdge 10.2A driver, C:\Progress\OpenEdge\bin\pgoe1023.dll).

Our OpenEdge version is 10.2A and our version is 32bit.

.Net 6 support

Any plans on making this library .Net 6.0 compatible?

Scaffold and Multi Database Connection

Hi Alex,
I'm trying use your library to EF Core and Progress OpenEdge Multi Database Connection.
MultiDb Connect it a tool of progress alow ODBC Connection to multiple databases
( https://knowledgebase.progress.com/articles/Article/P119452 )

I'm trying to scaffold db in this connections, but scaffold don't run.
Only primary database are mapped.

I'm try clone your project and debug, but on scaffold i can't debug step by step.
I'm remove NuGet reference and add a project reference, but even so step by step don't work.
How you debug your library on scaffold?

I would like correct this and make a PR to your project.

[OPENEDGE]Column "TIMESTAMP" cannot be found or is not specified for query.

When i'm add date time filter, the SQL was be generated with "TIMESTAMP" literal before date value.
Like:
... WHERE DtCreate <= TIMESTAMP '2020-05-01 00:00:00.0000000' ...

And OpenEdge returns.
[OPENEDGE]Column "TIMESTAMP" cannot be found or is not specified for query.

In my domain, this is a DateTime property.
In Progress Database this is Date field.

I'm using version 1.0.8 of this library and EF Core 2.2.6

What I need SQL will be generated without "TIMESTAMP" for?
Like: ... WHERE DtCreate <= '2020-05-01 00:00:00.0000000' ...

No params allowed in select list

OpenEdge does not allow selection of parameters.

var name = "Jane";
context.People.Select(p => new { name, p.Age });

ERROR [HY000] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]No Params allowed in select list'

Generated SQL

  SELECT ? AS "name", "p"."Age"
  FROM "pub"."person" AS "p"

  Parameters=[@__name_0='?']

Scaffold-DbContext remoting error

Managed to get the scaffold-dbcontext to work once successfully. Not sure what happened after that with a VS2019 update but this is the current error we receive while trying to scaffold an old Progress 10.2B SP8 for a client.
Currently limited into using 32bit odbc driver (Progress OpenEdge 10.2B 5.30.00.109) with .net 4.72 due to current installation. Unfortunately limited Progress knowledge within the team so any help appreciated.

PM> Scaffold-DbContext "dsn=CCCTEST1; password=;" EntityFrameworkCore.OpenEdge -Context CedarContext -OutPutDir Models -verbose
Using project 'Affco.Domain.Db.Cedar.Scaffold'.
Using startup project 'Affco.Domain.Db.Cedar.Scaffold'.
Build started...
Build succeeded.
C:\Users\kyc.nuget\packages\microsoft.entityframeworkcore.tools\2.1.4\tools\net461\win-x86\ef.exe dbcontext scaffold "dsn=CCCTEST1; password=;" EntityFrameworkCore.OpenEdge --json --output-dir Models --context CedarContext --verbose --no-color --prefix-output --assembly C:\Source\Affco.Domain.Db\Affco.Domain.Db.Cedar\Affco.Domain.Db.Cedar.Scaffold\bin\Debug\Affco.Domain.Db.Cedar.Scaffold.exe --startup-assembly C:\Source\Affco.Domain.Db\Affco.Domain.Db.Cedar\Affco.Domain.Db.Cedar.Scaffold\bin\Debug\Affco.Domain.Db.Cedar.Scaffold.exe --project-dir C:\Source\Affco.Domain.Db\Affco.Domain.Db.Cedar\Affco.Domain.Db.Cedar.Scaffold\ --language C# --working-dir C:\Source\Affco.Domain.Db\Affco.Domain.Db.Cedar --root-namespace Affco.Domain.Db.Cedar.Scaffold
Using assembly 'Affco.Domain.Db.Cedar.Scaffold'.
Using startup assembly 'Affco.Domain.Db.Cedar.Scaffold'.
Using application base 'C:\Source\Affco.Domain.Db\Affco.Domain.Db.Cedar\Affco.Domain.Db.Cedar.Scaffold\bin\Debug'.
Using working directory 'C:\Source\Affco.Domain.Db\Affco.Domain.Db.Cedar\Affco.Domain.Db.Cedar.Scaffold'.
Using root namespace 'Affco.Domain.Db.Cedar.Scaffold'.
Using project directory 'C:\Source\Affco.Domain.Db\Affco.Domain.Db.Cedar\Affco.Domain.Db.Cedar.Scaffold'.
Using configuration file 'C:\Source\Affco.Domain.Db\Affco.Domain.Db.Cedar\Affco.Domain.Db.Cedar.Scaffold\bin\Debug\Affco.Domain.Db.Cedar.Scaffold.exe.config'.
Finding design-time services for provider 'EntityFrameworkCore.OpenEdge'...
Using design-time services from provider 'EntityFrameworkCore.OpenEdge'.
Finding design-time services referenced by assembly 'Affco.Domain.Db.Cedar.Scaffold'.
No referenced design-time services were found.
Finding IDesignTimeServices implementations in assembly 'Affco.Domain.Db.Cedar.Scaffold'...
No design-time services were found.
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.Remoting.RemotingException: Cannot load type 'Microsoft.EntityFrameworkCore.Design.IOperationResultHandler, Microsoft.EntityFrameworkCore.Design, Version=2.1.4.0, Culture=neutral, PublicKeyToken=adb9793829ddae60'.

Server stack trace:
at System.Runtime.Remoting.Messaging.MethodCall.ResolveMethod(Boolean bThrowIfNotResolved)
at System.Runtime.Remoting.Messaging.MethodCall..ctor(SmuggledMethodCallMessage smuggledMsg, ArrayList deserializedArgs)
at System.Runtime.Remoting.Channels.CrossAppDomainSink.DoDispatch(Byte[] reqStmBuff, SmuggledMethodCallMessage smuggledMcm, SmuggledMethodReturnMessage& smuggledMrm)
at System.Runtime.Remoting.Channels.CrossAppDomainSink.DoTransitionDispatchCallback(Object[] args)

Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
at Microsoft.EntityFrameworkCore.Design.IOperationResultHandler.OnError(String type, String message, String stackTrace)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute[T](Func1 action) at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContext..ctor(OperationExecutor executor, Object resultHandler, IDictionary args) --- End of inner exception stack trace --- at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeConstructorInfo.Invoke(BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes, StackCrawlMark& stackMark) at System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes) at System.Activator.CreateInstance(String assemblyString, String typeName, Boolean ignoreCase, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes, Evidence securityInfo, StackCrawlMark& stackMark) at System.Activator.CreateInstance(String assemblyName, String typeName, Boolean ignoreCase, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes) at System.AppDomain.CreateInstance(String assemblyName, String typeName, Boolean ignoreCase, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes) at System.AppDomain.CreateInstance(String assemblyName, String typeName, Boolean ignoreCase, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes) at Microsoft.EntityFrameworkCore.Tools.AppDomainOperationExecutor.Execute(String operationName, Object resultHandler, IDictionary arguments) at CallSite.Target(Closure , CallSite , OperationExecutorBase , String , Object , IDictionary ) at System.Dynamic.UpdateDelegates.UpdateAndExecuteVoid4[T0,T1,T2,T3](CallSite site, T0 arg0, T1 arg1, T2 arg2, T3 arg3) at Microsoft.EntityFrameworkCore.Tools.OperationExecutorBase.InvokeOperationImpl(String operationName, IDictionary arguments) at Microsoft.EntityFrameworkCore.Tools.OperationExecutorBase.ScaffoldContext(String provider, String connectionString, String outputDir, String outputDbContextDir, String dbContextClassName, IEnumerable1 schemaFilters, IEnumerable`1 tableFilters, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)
at Microsoft.EntityFrameworkCore.Tools.Commands.DbContextScaffoldCommand.Execute()
at Microsoft.EntityFrameworkCore.Tools.Commands.CommandBase.<>c__DisplayClass0_0.b__0()
at Microsoft.DotNet.Cli.CommandLine.CommandLineApplication.Execute(String[] args)
at Microsoft.EntityFrameworkCore.Tools.Program.Main(String[] args)
Exception has been thrown by the target of an invocation.
PM>

Count() expects Int32. OpenEdge returns Int64

The following throws an exception

   People.Count();

An exception occurred while reading a database value. The expected type was 'System.Int32' but the actual value was of type 'System.Int64'

Workaround for now is to always use LongCount() instead.

Scaffold-DbContext doesn't work

Hi,

I have running an OpenEdge V12.1 database called stb. I have started VS2019, made 1 solution with some projects and I have done some Install-Package statements in the Nuget packagemanager, so far so good no problem.

I want to generate automatically the classes for my tables in database stb. I run the following in Package Manager console:

Scaffold-DbContext "Driver=Progress OpenEdge 12.1 Driver;HOST=localhost;port=12000;UID=GEERTJ01;PWD=JG1312;DIL=1;Database=stb" EntityFrameworkCore.OpenEdge -OutputDir Models

The following output appears:

Build started...
Build succeeded.
System.TypeLoadException: Method 'Create' in type 'EntityFrameworkCore.OpenEdge.Scaffolding.Internal.OpenEdgeDatabaseModelFactory' from assembly 'EntityFrameworkCore.OpenEdge, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b11b5ededc51fdb3' does not have an implementation.
   at EntityFrameworkCore.OpenEdge.Design.Internal.OpenEdgeDesignTimeServices.ConfigureDesignTimeServices(IServiceCollection serviceCollection)
   at Microsoft.EntityFrameworkCore.Design.Internal.DesignTimeServicesBuilder.ConfigureDesignTimeServices(Type designTimeServicesType, IServiceCollection services)
   at Microsoft.EntityFrameworkCore.Design.Internal.DesignTimeServicesBuilder.ConfigureProviderServices(String provider, IServiceCollection services, Boolean throwOnError)
   at Microsoft.EntityFrameworkCore.Design.Internal.DesignTimeServicesBuilder.Build(String provider)
   at Microsoft.EntityFrameworkCore.Design.Internal.DatabaseOperations.ScaffoldContext(String provider, String connectionString, String outputDir, String outputContextDir, String dbContextClassName, IEnumerable`1 schemas, IEnumerable`1 tables, String modelNamespace, String contextNamespace, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames, Boolean suppressOnConfiguring, Boolean noPluralize)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContextImpl(String provider, String connectionString, String outputDir, String outputDbContextDir, String dbContextClassName, IEnumerable`1 schemaFilters, IEnumerable`1 tableFilters, String modelNamespace, String contextNamespace, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames, Boolean suppressOnConfiguring, Boolean noPluarlize)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContext.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
**Method 'Create' in type 'EntityFrameworkCore.OpenEdge.Scaffolding.Internal.OpenEdgeDatabaseModelFactory' from assembly 'EntityFrameworkCore.OpenEdge, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b11b5ededc51fdb3' does not have an implementation.**
PM> 

What is wrong here?
Do I have forgotten something?

The project .cproj file:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp3.1</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="EntityFrameworkCore.OpenEdge" Version="1.0.12-rc3" />
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="5.0.3" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="5.0.3">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="5.0.3" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="5.0.3">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
  </ItemGroup>

</Project>

Thanks in advance.

Johan Geerts
Progress Developer Axians

Add <owner>.pub.<table> on TableVisit

When i use Progress MultiDB Connection, i need explicit set the table owner on SQL.
select fieldA, fieldB from owner.pub.tableA
Against
select fieldA, fieldB from pub.tableA

I'm already code this issue for select statements.
On Insert, Delete, Update i'm working yet.
Can i create a branch to resolve this?

The 'OpenEdgeBoolTypeMapping' does not support value conversions.

When I run the Scaffold-DbContext cmdlet, I get the following error.

The 'OpenEdgeBoolTypeMapping' does not support value conversions. Support for value conversions typically requires changes in the database provider.

I'm not familiar with creating a db context provider. How do you debug one. Is it a matter of running the edmgen.exe or does that not work with .NET core?

More info:

Scaffold-DbContext "dsn=mydata;password=password1" EntityFrameworkCore.OpenEdge -StartupProject 'MyApi' -OutputDir Data
System.NotImplementedException: The 'OpenEdgeBoolTypeMapping' does not support value conversions. Support for value conversions typically requires changes in the database provider.
   at Microsoft.EntityFrameworkCore.Storage.RelationalTypeMapping.Clone(RelationalTypeMappingInfo& mappingInfo)
   at EntityFrameworkCore.OpenEdge.Storage.Internal.Mapping.OpenEdgeTypeMappingSource.FindMapping(RelationalTypeMappingInfo& mappingInfo)
   at Microsoft.EntityFrameworkCore.Storage.RelationalTypeMappingSource.<FindMappingWithConversion>b__7_0(ValueTuple`3 k)
   at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd(TKey key, Func`2 valueFactory)
   at Microsoft.EntityFrameworkCore.Storage.RelationalTypeMappingSource.FindMappingWithConversion(RelationalTypeMappingInfo& mappingInfo, IReadOnlyList`1 principals)
   at Microsoft.EntityFrameworkCore.Storage.RelationalTypeMappingSource.FindMapping(String storeTypeName)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ScaffoldingTypeMapper.FindMapping(String storeType, Boolean keyOrIndex, Boolean rowVersion)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.GetTypeScaffoldingInfo(DatabaseColumn column)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitColumn(EntityTypeBuilder builder, DatabaseColumn column)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitColumns(EntityTypeBuilder builder, ICollection`1 columns)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitTable(ModelBuilder modelBuilder, DatabaseTable table)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitTables(ModelBuilder modelBuilder, ICollection`1 tables)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitDatabaseModel(ModelBuilder modelBuilder, DatabaseModel databaseModel)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.Create(DatabaseModel databaseModel, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ReverseEngineerScaffolder.ScaffoldModel(String connectionString, IEnumerable`1 tables, IEnumerable`1 schemas, String namespace, String language, String contextDir, String contextName, ModelReverseEngineerOptions modelOptions, ModelCodeGenerationOptions codeOptions)
   at Microsoft.EntityFrameworkCore.Design.Internal.DatabaseOperations.ScaffoldContext(String provider, String connectionString, String outputDir, String outputContextDir, String dbContextClassName, IEnumerable`1 schemas, IEnumerable`1 tables, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContextImpl(String provider, String connectionString, String outputDir, String outputDbContextDir, String dbContextClassName, IEnumerable`1 schemaFilters, IEnumerable`1 tableFilters, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContext.<>c__DisplayClass0_1.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
The 'OpenEdgeBoolTypeMapping' does not support value conversions. Support for value conversions typically requires changes in the database provider.

Migrations do not work

Migrations do not work. No work has been done on this yet.

PopulateDebugInfo missing

Is it working copy of OpenEdge PROGRESS DB provider?

I have such error message: "TypeLoadException: Method 'PopulateDebugInfo' .. does not have an implementation." It looks like OpenEdgeOptionsExtension should be expanded about method like:
public virtual void PopulateDebugInfo(IDictionary<string, string> debugInfo)
{
debugInfo["OpenEdge"] = "1";
}
and probably also an interface is missing. Am I right?

Support for extent specifier?

Hi. Since Progress supports array types with a field value, called "Extent," I have a decimal? array type that gets scaffolded as just plain decimal?, which causes queries to fail, listing the actual datatype as string. Is it possible to support this data type specification on the provider level, so the columns could be correctly scaffolded as decimal? [] ?

Tables and Columns

Hi,

Hmm .. I see that PROGRESS database which I am working with have a different system metadata -> ERROR from GetColumns method: "Column 't.rowid' cannot be found or is not specified for query".

Is exists any reason why you did not look up into SYSPROGRESS and SYSTABLES, SYSCOLUMNS, SYSINDEXES tables?

Regards
Marek

Select query returns mangled data.

Hi. I scaffolded with version 1.08 and had the same "OpenEdge does not support bool mapping" problem so I fixed the issue myself and then re-scaffolded.

I began to develop an API and came across this issue where I call a certain table to list.

This code:

var r = _context.PromoTransHdr.Where(hdr => hdr.ContractNo == "Jeff").ToList();

Results in this query:

SELECT "hdr"."contract-no", "hdr"."expire-date", "hdr"."Billing-code", "hdr"."description", "hdr"."orig-amt", "hdr"."orig_date", "hdr"."promo-id", "hdr"."promo-transno", "hdr"."restricted", "hdr"."trans-date", "hdr"."used-amt" FROM "pub"."promo-trans-hdr" AS "hdr" WHERE "hdr"."contract-no" = 'Jeff'

If I run this query in Progress' developer studio's db navigator, I get 269 rows of data, all correct, unique data. However, in my API, I get 269 results containing repeated rows and certain rows are omitted completely.

I tried recreating a new project, this time net core 2.1/asp net core and added only your package, EntityFrameworkCore.OpenEdge, the new pre-release 1.09rc1, and rescaffolded. The same issue exists, though I am able to scaffold without modification.