Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

EF Core scaffolding support [DNET798] #735

Closed
firebird-automations opened this issue Dec 1, 2017 · 26 comments
Closed

EF Core scaffolding support [DNET798] #735

firebird-automations opened this issue Dec 1, 2017 · 26 comments

Comments

@firebird-automations
Copy link

Submitted by: @cincuranet

Jira_subtask_inward DNET663
Is duplicated by DNET886

Votes: 3

Commits: 88da531

@firebird-automations
Copy link
Author

Modified by: @cincuranet

Fix Version: 6.post [ 10857 ]

@firebird-automations
Copy link
Author

Commented by: Henning Hassmann (henning)

Is there anything new on Scaffold? I can start it, but no database classes are created.

@firebird-automations
Copy link
Author

Commented by: @cincuranet

Nothing new.

@firebird-automations
Copy link
Author

Modified by: @cincuranet

Link: This issue is duplicated by DNET886 [ DNET886 ]

@firebird-automations
Copy link
Author

Commented by: Vince (ramdevteam)

Hello,

I have the same pb.
VS2019 Pro + new http://ASP.NET Core 2.2 project.
In Nuget : installation of FirebirdSql.EntityFrameworkCode.Firebird 6.6.0 (install Firebirdsql.Data.FirebirdClient.6.6.0 + FirebirdSql.EntityFrameworkCode.Firebird.6.6.0)

Before trying with my customer database , I have create a database with one table for testing.

CREATE TABLE TESTITEM ( IDTABLE INTEGER NOT NULL, LIB VARCHAR(255));
ALTER TABLE TESTITEM ADD CONSTRAINT PK_TESTITEM PRIMARY KEY (IDTABLE);

Scaffold-dbcontext "User=SYSDBA;Password=masterkey;Database=D:\Projects\WebAppt\data\testcore.7data;DataSource=127.0.0.1;Port=3050;Dialect=3;Charset=NONE;Role=;Connection lifetime=15;Pooling=true;MinPoolSize=0;MaxPoolSize=50;PacketSize=8192;ServerType=0;" FirebirdSql.EntityFrameworkCore.Firebird -Output "D:\Projects\WebApp\testApp\Models\TestCore2" -Context "TestCore2"

No database class are created. Juste a folder with TestCore2.cs with DbContext, override void OnConfiguring and override void OnModelCreating

I missed something ?

Thanks

@firebird-automations
Copy link
Author

Commented by: @cincuranet

You missed the ticket being still active...

@firebird-automations
Copy link
Author

Modified by: @cincuranet

Fix Version: 6.planned [ 10857 ] =>

@firebird-automations
Copy link
Author

Commented by: Dejan Radic (dejan)

This work for me

Install-package EntityFrameworkCore.FirebirdSql -Version 2.1.2.2
Install-package Microsoft.EntityFrameworkCore.Tools -Version 2.2.6

Scaffold-DbContext "Server=myserver;Database=mydatabase;user id=SYSDBA;password=masterkey;" EntityFrameworkCore.FirebirdSql -Force -OutputDir Models

and then downgrade to
Install-package EntityFrameworkCore.FirebirdSql -Version 2.1.1

@firebird-automations
Copy link
Author

Commented by: Giacomo Policicchio (pgiacomo)

Since i badly needed to scaffold an existing (big) db, I've made a branch with working Scaffolding.
There is a PR on Jiri's repository, but I understand if it doesn't reach contributions quality.
However, in these days I've updated it so it can be used with Microsoft.EntityFrameworkCore 5.0.1, when targeting net 5, but mantaining full compatibility with netstandard2.0 and Microsoft.EntityFrameworkCore 3.1.10

@jiri, I don't now if my work can be useful, because with this modification I needed to update several files, I know that the PR is cluttered with too many commits, if necessary I can cancel it and push a new branch with all modifications in one commit.

thanks

@firebird-automations
Copy link
Author

Commented by: @cincuranet

I know about the PR (at least for 3.1) and I'm planning to merge it. The .NET 5 etc. changes are something that should be kept apart. At the moment I'm not sure how I'm going (if ever) to handle supporting EF Core 3.1 (because of LTS) and 5.0 that's latest. But that's something to think about when I have time and discuss it in the list.

@firebird-automations
Copy link
Author

Commented by: Giacomo Policicchio (pgiacomo)

EF Core in current master is targeted to run in Net 5.0, but compiled against netstandard2.0 API. I had errors running scaffolding when using EF Core with net 5.0, like methods not found, etc. So I recompiled using 5.0 APIs putting conditional defines in right places, remains one method related to annotations for wich there is no substitute, and for now I had no time to investigate it. So, for now the EF does not sense when a key shoud be made autoincremental. I'll see.
I look forward to discuss it more when you want, Bye

@firebird-automations
Copy link
Author

Commented by: Marko P (projo94)

After running this command:
Scaffold-DbContext "User=xxxx;Password=xxxx;Database=xxxx;DataSource=xxxxxx;Port=3050;Dialect=3;Charset=NONE;Role=;Connection lifetime=15;Pooling=true;MinPoolSize=0;MaxPoolSize=50;Packet Size=8192;ServerType=0;" FirebirdSql.EntityFrameworkCore.Firebird -OutputDir Models

I am getting this error:
Method not found: 'Void Microsoft.EntityFrameworkCore.Storage.DecimalTypeMapping..ctor(System.String, System.Nullable`1<System.Data.DbType>)'.

Version of FirebirdSql.EntityFrameworkCore.Firebird is 7.10.1... Also tried with version 7.10.0, and getting the same error.

@firebird-automations
Copy link
Author

Modified by: @cincuranet

Fix Version: vNext [ 10970 ]

@firebird-automations
Copy link
Author

Modified by: @cincuranet

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@sealkeen
Copy link

sealkeen commented Dec 19, 2021

Can you please explain how to change the connection string
Scaffold-DbContext "Server=localhost;Database=C:\Program Files\Firebird\Firebird_3_0\examples\empbuild\EMPLOYEE.FDB;user id=SYSDBA;password=masterkey;" EntityFrameworkCore.FirebirdSql -Force -OutputDir Models

to avoid setting WireCrypt = Enabled in the firebird.conf file while scaffolding the database and while accessing it with Entity Framework?

Although It's in fact working with the WireCrypt = Enabled, But the data won't probably be encrypted with the current configuration.

@cincuranet
Copy link
Member

EntityFrameworkCore.FirebirdSql is not a package developed here.

@sealkeen
Copy link

EntityFrameworkCore.FirebirdSql is not a package developed here.

Alright, then can you please specify which certain EntityFrameworkCore version one should use with the latest version of FirebirdSQL.EntityFramework.Firebird to be able to Scaffold an existing DB? Which EFCore.Tools version should one also use with EFCore.Design?

Is there a short version of the connection string where most of the parameters are set to a default value or identified at runtime?

@cincuranet
Copy link
Member

cincuranet commented Dec 25, 2021 via email

@sealkeen
Copy link

sealkeen commented Dec 27, 2021

For the following set up:

  • microsoft.entityframeworkcore\5.0.9; firebirdsql.data.firebirdclient\8.5.4; microsoft.entityframeworkcore.design\5.0.9; firebirdsql.entityframeworkcore.firebird\8.5.4; microsoft.entityframeworkcore.tools\5.0.9.
  • server - remote, TCP/IP, Server version - Firebird 2.5, Charset - WIN1251, Dialect - 1.

scaffold-dbcontext returns "System.InvalidOperationException: Sequence contains no matching element". There's no 2.5 and lower version support for now, right?

@abdgad
Copy link

abdgad commented Dec 28, 2021

Scaffolding currently only works with EF Core 5. Any updates on scaffolding support for EF Core 6 @cincuranet ?

@cincuranet
Copy link
Member

@sealkeen> Dialect 1 might be a problem. It's basically deprecated in server anyway. FB 2.5 is supported by a contribution, but it's not actively maintained (and 2.5 is also EOL), 3.0 is the lowest version tested.

@abdgad> It's done (#1003), but not yet released in RTM. You can try alpha version.

@Kaster
Copy link

Kaster commented May 17, 2022

Hello,
what is the current status?

I have following and try to scaffold ...

  • Firebird 2.5
  • .NET 6
  • EF Core 6.0.5
  • FirebirdSql.Data.FirebirdClient 9.0.1
  • FirebirdSql.EntityFrameworkCore.Firebird 9.0.1
  1. By simple database X (with only 2 tables) I have successful generation of Context class and Entity classes
  2. By same database X with using of flag "Table" I have successful generation only of context class and not entity class
  3. By other (old and complex) database Y, I have no success with generation and have following error:
System.InvalidCastException: Object cannot be cast from DBNull to other types.
   at System.DBNull.System.IConvertible.ToInt32(IFormatProvider provider)
   at System.Convert.ToInt32(Object value, IFormatProvider provider)
   at FirebirdSql.Data.Common.DbValue.GetInt32()
   at FirebirdSql.Data.FirebirdClient.FbDataReader.GetFieldValue[T](Int32 i)
   at FirebirdSql.Data.FirebirdClient.FbDataReader.GetInt32(Int32 i)
   at FirebirdSql.EntityFrameworkCore.Firebird.Scaffolding.Internal.FbDatabaseModelFactory.GetTables(DbConnection connection, Func`3 filter)
   at FirebirdSql.EntityFrameworkCore.Firebird.Scaffolding.Internal.FbDatabaseModelFactory.Create(DbConnection connection, DatabaseModelFactoryOptions options)
   at FirebirdSql.EntityFrameworkCore.Firebird.Scaffolding.Internal.FbDatabaseModelFactory.Create(String connectionString, DatabaseModelFactoryOptions options)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ReverseEngineerScaffolder.ScaffoldModel(String connectionString, DatabaseModelFactoryOptions databaseOptions, 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, 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)
Object cannot be cast from DBNull to other types.

And, I can't get any information from the logs about the exact table(s) where the error occurred.

What can I do now?

Thanks!

@sealkeen
Copy link

sealkeen commented May 17, 2022

Hello, what is the current status?

I have following and try to scaffold ...

  • Firebird 2.5
  • .NET 6
  • EF Core 6.0.5
  • FirebirdSql.Data.FirebirdClient 9.0.1
  • FirebirdSql.EntityFrameworkCore.Firebird 9.0.1
    ...
    Thanks!

@Kaster, I ended up using FirebirdSql.Data.FirebirdClient, Version=8.0.1.0 without scaffolding.
That's much less errorsome, although you will need to learn some SQL:

        public static int ExecuteFirebirdProcedure(Action<string> log)
        {
            try
            {
                string conString = "character set=WIN1251;data source=127.0.0.1;initial catalog=database_name;user " +
                "id=SYSDBA;password=masterkey;no garbage collect=true;role name = R201; max pool size" +
                "=1000";
                using (FbConnection FBCon = new FbConnection(conString))
                {
                    FBCon.Open();
                    string param1 = "10.5", param2 = "5.1", param3 = "1.2";
                    FbCommand cmd =
                        new FbCommand($"execute procedure SP$STORED_PROCEDURE('{param1}', '{param2}', '{param3}');", FBCon);
                    int res = cmd.ExecuteNonQuery();
                    FBCon.Close();
                    return res;
                }
            }
            catch (Exception ex)
            {
                log?.Invoke($"{nameof(ExecuteFirebirdProcedure)}: {ex.ToString()}. {ex.Message}");
                return 0;
            }
        }

Simple select statement will go through DataAdapter like this:

        using FirebirdSql.Data.FirebirdClient;
        // your method to pull data from database to datatable   
        public void PullData(string query)
        {                       //character set=WIN1251;
            string connString = @"data source=127.0.0.1;initial catalog=database_name;user id=SYSDBA;password=masterkey;no garbage collect=true;role name = R201; max pool size=1000";
            if (string.IsNullOrEmpty(query))
            {
                _log.Invoke("Source query string was empty."); //query = "select * from CLIENTS";
                return;
            }
            FbConnection conn = new FbConnection(connString);
            conn.Open();

            // create firebird command
            FbCommand cmd = new FbCommand(query, conn);
            // create data adapter
            FbDataAdapter da = new FbDataAdapter(cmd);
            // this will query your database and return the result to your datatable
            dataTable = new DataTable();
            da.Fill(dataTable);
            // look for this method on stackoverflow if you need some XML table convertion
            dataTable = XMLChanger.DataTableExtensions.GetNullFilledDataTableForXML(dataTable);
            conn.Close();
            da.Dispose();
        }

@Kaster
Copy link

Kaster commented May 17, 2022

@sealkeen Thanks for the effort! I actually wanted to save myself this trip :-( I have more than 100 tables with an average of 30 columns each.

In the meantime I have updated Firebird to 3.0 ... again there are errors with scaffolding:

System.InvalidOperationException: Sequence contains no matching element
   at System.Linq.ThrowHelper.ThrowNoMatchException()
   at FirebirdSql.EntityFrameworkCore.Firebird.Scaffolding.Internal.FbDatabaseModelFactory.GetIndexes(DbConnection connection, IReadOnlyList`1 tables, Func`3 tableFilter)
   at FirebirdSql.EntityFrameworkCore.Firebird.Scaffolding.Internal.FbDatabaseModelFactory.GetTables(DbConnection connection, Func`3 filter)
   at FirebirdSql.EntityFrameworkCore.Firebird.Scaffolding.Internal.FbDatabaseModelFactory.Create(DbConnection connection, DatabaseModelFactoryOptions options)
   at FirebirdSql.EntityFrameworkCore.Firebird.Scaffolding.Internal.FbDatabaseModelFactory.Create(String connectionString, DatabaseModelFactoryOptions options)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ReverseEngineerScaffolder.ScaffoldModel(String connectionString, DatabaseModelFactoryOptions databaseOptions, 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, 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)

But the classic way remains as an alternative.

@cincuranet
Copy link
Member

@Kaster

What can I do now?

Open a new issue and provide the database structure.

@sealkeen
Copy link

sealkeen commented May 18, 2022

@sealkeen Thanks for the effort! I actually wanted to save myself this trip :-( I have more than 100 tables with an average of 30 columns each.
...

I actually had to deal with more than 1700 tables and 2300+ stored procedures within a single dialect 1 DB, which was larger than a single 220 GB file and I figured out that it's easier to learn SQL, PL/SQL rather than to scaffold it... The output from the database is handled by "for select *" within a stored procedure, returning the fields you need from the tables. And to input something it's just FBCommand ( ... ) ... "insert into tablename( fieldname1, fieldname2 ) values ( 1, 2 )" ... .ExecuteNonQuery();

From what I can tell you, scaffolding that DB resulted fine, except for around 20% of the tables missing (which I'm not exactly sure were) and 56 000 lines of DbContext code...

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

5 participants