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

RevEng: Generate IEntityTypeConfiguration classes (avoids large OnConfiguring) #8434

Open
JuanIrigoyen opened this Issue May 10, 2017 · 21 comments

Comments

Projects
None yet
8 participants
@JuanIrigoyen

JuanIrigoyen commented May 10, 2017

When you work with models with many Entities and the context file is too big I have some problems when I try to modify the file.

Many times Visual Studio is blocked and closed when I work with the context file. I can´t not use Resharper or Coderush with this file because it´s completely impossible to work. I need to add more files but every time is more difficult by the size of the file, I will have to start to edit it in the notepad or another editor, but I don´t have intellisence and is very difficult.

I think that a good approximation is separate the configuration of the entities into different files class on the OnModelCreating method.

Steps to reproduce

Create one model with more than 22000 lines of code.

Further technical details

EF Core version: 1.1
Database Provider: (Microsoft.EntityFrameworkCore.SqlServer)
Operating system: Windows 10 Pro
IDE: (Visual Studio 2017)
Computer. Intel Core I7 - 6700 HQ - 256 SSD Hard Drive

@cdie

This comment has been minimized.

Show comment
Hide comment
@cdie

cdie May 10, 2017

Simple question : why not using partial classes ?

And what you can do to solve it easily is to create "contract" classes for bunch of entites, and into your OnModelCreating, retrieve all contract classes by reflection and execute them

cdie commented May 10, 2017

Simple question : why not using partial classes ?

And what you can do to solve it easily is to create "contract" classes for bunch of entites, and into your OnModelCreating, retrieve all contract classes by reflection and execute them

@ajcvickers

This comment has been minimized.

Show comment
Hide comment
@ajcvickers

ajcvickers May 10, 2017

Member

@JuanIrigoyen I'm curious how you ended up with 22000 lines. Was this generated by reverse engineering an existing database using Scaffold-DbContext? If so, approximately how many tables are mapped? Also, can you post an example of the the configuration generated for a single table?

Member

ajcvickers commented May 10, 2017

@JuanIrigoyen I'm curious how you ended up with 22000 lines. Was this generated by reverse engineering an existing database using Scaffold-DbContext? If so, approximately how many tables are mapped? Also, can you post an example of the the configuration generated for a single table?

@JuanIrigoyen

This comment has been minimized.

Show comment
Hide comment
@JuanIrigoyen

JuanIrigoyen May 10, 2017

@cdie If I regenerate the model using Scaffold-DbContext I lost all.
@ajcvickers Yes ajcvikers, I generate my model using Scaffold-DbContext, I attach an example.
In this example I have about 500 tables mapped.

MaldivasMainContext.zip

JuanIrigoyen commented May 10, 2017

@cdie If I regenerate the model using Scaffold-DbContext I lost all.
@ajcvickers Yes ajcvikers, I generate my model using Scaffold-DbContext, I attach an example.
In this example I have about 500 tables mapped.

MaldivasMainContext.zip

@ErikEJ

This comment has been minimized.

Show comment
Hide comment
@ErikEJ

ErikEJ May 11, 2017

Contributor

@JuanIrigoyen You are aware that you can choose which tables to scaffold? Do you REALLY need them all?

Contributor

ErikEJ commented May 11, 2017

@JuanIrigoyen You are aware that you can choose which tables to scaffold? Do you REALLY need them all?

@lajones

This comment has been minimized.

Show comment
Hide comment
@lajones

lajones May 11, 2017

Member

@JuanIrigoyen I would echo what @ErikEJ said - you can choose which schemas and/or tables to put in any one model - so you could have multiple models each representing a smaller part of the overall database. Also it may help a little to use the -DataAnnotations flag - wherever possible that will replace fluent API in the context class with annotations in the individual entity classes.

Member

lajones commented May 11, 2017

@JuanIrigoyen I would echo what @ErikEJ said - you can choose which schemas and/or tables to put in any one model - so you could have multiple models each representing a smaller part of the overall database. Also it may help a little to use the -DataAnnotations flag - wherever possible that will replace fluent API in the context class with annotations in the individual entity classes.

@JuanIrigoyen

This comment has been minimized.

Show comment
Hide comment
@JuanIrigoyen

JuanIrigoyen May 11, 2017

@ErikEJ & @lajones, yes because when you have a lot of tables and relationship between them is complicated to write all changes in the model. For me it's faster to regenerate the model. I cannot divide it in small context because I lost the relationships. I think that it's the same as the entities. Why Entity Framework don't write all entities in one file? I think that if you separate the configuration files is better in a big models like this.

JuanIrigoyen commented May 11, 2017

@ErikEJ & @lajones, yes because when you have a lot of tables and relationship between them is complicated to write all changes in the model. For me it's faster to regenerate the model. I cannot divide it in small context because I lost the relationships. I think that it's the same as the entities. Why Entity Framework don't write all entities in one file? I think that if you separate the configuration files is better in a big models like this.

@jemiller0

This comment has been minimized.

Show comment
Hide comment
@jemiller0

jemiller0 May 11, 2017

I have 1,500 entities in my model generated from a legacy database I have no control over. It is good to see that I'm not the only one dealing with a large database. The EF 6 tools couldn't handle it. I had to write my own reverse engineering tool to do it. One problem that I ran into was that I found that the ADO.NET meta data API is pretty poor. I found that it is better to use the INFORMATION_SCHEMA tables directly. I was running into performance problems when using the metadata API. Not sure what EF is doing. I just know that the GUI tool in EF 6 and before was terribly slow. In my case, yes, I wanted all the entities in the model because I wanted to be able to see all the relationships between entities and use navigation properties to navigate between any of the tables. Breaking the model up into smaller models wouldn't have worked very well. A problem that I had with EF 6 was that it is slow to initialize when you have that many tables. EF Core is better, but, is still slow. I'm not sure what the exact architectural differences are between EF/EF Core and something like NHibernate, but, NHibernate has basically zero startup time. So, apparently, they are doing something right. Maybe it's just the fact that the relationships are stored in XML mapping files rather than attributes.

jemiller0 commented May 11, 2017

I have 1,500 entities in my model generated from a legacy database I have no control over. It is good to see that I'm not the only one dealing with a large database. The EF 6 tools couldn't handle it. I had to write my own reverse engineering tool to do it. One problem that I ran into was that I found that the ADO.NET meta data API is pretty poor. I found that it is better to use the INFORMATION_SCHEMA tables directly. I was running into performance problems when using the metadata API. Not sure what EF is doing. I just know that the GUI tool in EF 6 and before was terribly slow. In my case, yes, I wanted all the entities in the model because I wanted to be able to see all the relationships between entities and use navigation properties to navigate between any of the tables. Breaking the model up into smaller models wouldn't have worked very well. A problem that I had with EF 6 was that it is slow to initialize when you have that many tables. EF Core is better, but, is still slow. I'm not sure what the exact architectural differences are between EF/EF Core and something like NHibernate, but, NHibernate has basically zero startup time. So, apparently, they are doing something right. Maybe it's just the fact that the relationships are stored in XML mapping files rather than attributes.

@JuanIrigoyen

This comment has been minimized.

Show comment
Hide comment
@JuanIrigoyen

JuanIrigoyen May 12, 2017

I know these problems with EF 6. It´s impossible for me to manage this model. I have tried to split my context in smaller ones. It´s not possible to manage the model using the graphic designer. I wrote my previous model based in POCO entities and reflection using attributes and my own tool to generate my entities, business and data classes. With this program I only loaded the metadata of the entities that I was using and I didn’t have this penalty at the beginning. I think NHibernate will do something similar.

Now my model works with EF Core

Although I still have several problems using EF Core:

  • EF Core doesn’t support Views. I must write my view class directly in the model, these views require a key field for EF Core to be manage.
  • EF Core doesn’t support Store Procedures. I manage this issue using Ado 2.0
  • The main problem is when I start up the contexts, my current model with 700 entities and five different contexts takes about 9 seconds to open. I think this is because it loads all the metadata at the beginning. For me this is a mistake, since different users usually handle a limited number of tables and they don´t need all information about their model because it is not necessary.

I think they do this to have information about their relationships with other tables but I'm not sure.
Even with these problems, I think EF Core brings me many advantages over my previous model, performance has improved a lot and minimized the use of stored procedures writing queries directly, development is also faster, and I think the use of Views and Stored Procedures will be enabled in the following versions.

The problem with 1500 entities is the initial loading time when the context is started up. You need to divide this into different contexts, but you will lose the relationships between them. I hope someday this behavior can be changed in future versions.

JuanIrigoyen commented May 12, 2017

I know these problems with EF 6. It´s impossible for me to manage this model. I have tried to split my context in smaller ones. It´s not possible to manage the model using the graphic designer. I wrote my previous model based in POCO entities and reflection using attributes and my own tool to generate my entities, business and data classes. With this program I only loaded the metadata of the entities that I was using and I didn’t have this penalty at the beginning. I think NHibernate will do something similar.

Now my model works with EF Core

Although I still have several problems using EF Core:

  • EF Core doesn’t support Views. I must write my view class directly in the model, these views require a key field for EF Core to be manage.
  • EF Core doesn’t support Store Procedures. I manage this issue using Ado 2.0
  • The main problem is when I start up the contexts, my current model with 700 entities and five different contexts takes about 9 seconds to open. I think this is because it loads all the metadata at the beginning. For me this is a mistake, since different users usually handle a limited number of tables and they don´t need all information about their model because it is not necessary.

I think they do this to have information about their relationships with other tables but I'm not sure.
Even with these problems, I think EF Core brings me many advantages over my previous model, performance has improved a lot and minimized the use of stored procedures writing queries directly, development is also faster, and I think the use of Views and Stored Procedures will be enabled in the following versions.

The problem with 1500 entities is the initial loading time when the context is started up. You need to divide this into different contexts, but you will lose the relationships between them. I hope someday this behavior can be changed in future versions.

@ErikEJ

This comment has been minimized.

Show comment
Hide comment
@ErikEJ

ErikEJ May 12, 2017

Contributor

Only 9 seconds? With EF6 it could take hours!

Contributor

ErikEJ commented May 12, 2017

Only 9 seconds? With EF6 it could take hours!

@JuanIrigoyen

This comment has been minimized.

Show comment
Hide comment
@JuanIrigoyen

JuanIrigoyen May 12, 2017

Yes!!!, you can watch this in the first seconds of this video, in EF 6.1 in some computers this take about 22 minutes. Maldivas.zip

JuanIrigoyen commented May 12, 2017

Yes!!!, you can watch this in the first seconds of this video, in EF 6.1 in some computers this take about 22 minutes. Maldivas.zip

@jemiller0

This comment has been minimized.

Show comment
Hide comment
@jemiller0

jemiller0 May 12, 2017

I think the reverse engineering tool could take hours. With the latest EF 6 and the 1,500 entities that I have, I think it takes between 20 and 30 seconds for mine to initialize on my desktop. However, it is longer than that on a server. I have always found server hardware to be slower than desktop hardware. Not sure how much of it is due to the overhead of VMs or differences in server CPUs versus desktop ones. I think my model was taking about 2 to 3 minutes to initialize in the bad old days. A more recent version of EF 6 seemed to knock it down to around a minute or less. In any case, I always wondered why the model wasn't lazily initialized. I was previously told that this wouldn't be needed in EF Core since it is much faster overall. It is significantly faster. However, I still don't think it is great for large models. I think what would be needed is lazy initialization. With the way the fluent API works, I'm not sure if that would be possible. I should say that my model also has all kind of crazy things going on with tables that have lots of columns as well as concatenated keys. Overall, the database schema is a pretty big train wreck. Definitely not how I would design a system if I had a choice.

jemiller0 commented May 12, 2017

I think the reverse engineering tool could take hours. With the latest EF 6 and the 1,500 entities that I have, I think it takes between 20 and 30 seconds for mine to initialize on my desktop. However, it is longer than that on a server. I have always found server hardware to be slower than desktop hardware. Not sure how much of it is due to the overhead of VMs or differences in server CPUs versus desktop ones. I think my model was taking about 2 to 3 minutes to initialize in the bad old days. A more recent version of EF 6 seemed to knock it down to around a minute or less. In any case, I always wondered why the model wasn't lazily initialized. I was previously told that this wouldn't be needed in EF Core since it is much faster overall. It is significantly faster. However, I still don't think it is great for large models. I think what would be needed is lazy initialization. With the way the fluent API works, I'm not sure if that would be possible. I should say that my model also has all kind of crazy things going on with tables that have lots of columns as well as concatenated keys. Overall, the database schema is a pretty big train wreck. Definitely not how I would design a system if I had a choice.

@jemiller0

This comment has been minimized.

Show comment
Hide comment
@jemiller0

jemiller0 May 12, 2017

One problem that I ran into with EF Core is that when it initializes the context, it apparently uses a lot of stack memory. I was getting a stack overflow when I would try to use my model in a Web Forms app. It would work fine in a console app. The workaround I found was that I created a thread and specified a larger stack size for it and initialized the DbContext in that thread. The context worked fine in other threads after that. Not sure if this is still needed in 2.0.

jemiller0 commented May 12, 2017

One problem that I ran into with EF Core is that when it initializes the context, it apparently uses a lot of stack memory. I was getting a stack overflow when I would try to use my model in a Web Forms app. It would work fine in a console app. The workaround I found was that I created a thread and specified a larger stack size for it and initialized the DbContext in that thread. The context worked fine in other threads after that. Not sure if this is still needed in 2.0.

@JuanIrigoyen

This comment has been minimized.

Show comment
Hide comment
@JuanIrigoyen

JuanIrigoyen May 12, 2017

@jemiller0 I agree with this, I believe that lazy loading of metadata only when needed is the solution to all these performance problems. In the web part I have not had any problem even with EF 6, because my contexts only use 20 or 30 tables. For EF It isn´t a problem.

As @lajones comments a possible solution is use DataAnnotations to make this file smaller, but I will have to modify many aspects of my model related with the metadata. I do not know if my model will work the same and if the performance will remain stable. I will try it.

JuanIrigoyen commented May 12, 2017

@jemiller0 I agree with this, I believe that lazy loading of metadata only when needed is the solution to all these performance problems. In the web part I have not had any problem even with EF 6, because my contexts only use 20 or 30 tables. For EF It isn´t a problem.

As @lajones comments a possible solution is use DataAnnotations to make this file smaller, but I will have to modify many aspects of my model related with the metadata. I do not know if my model will work the same and if the performance will remain stable. I will try it.

@ajcvickers

This comment has been minimized.

Show comment
Hide comment
@ajcvickers

ajcvickers May 12, 2017

Member

There are two main things being discussed here:

  • The large context file
  • The startup time for large models

With regard to the context file, switching to data annotations should make the context file smaller and will not change the functionality at all--or if it does, then please report it as a bug. Reverse engineering will only substitute a data annotation if it will maintain the same behavior as the fluent API call it is replacing.

Beyond that, we have talked about providing an option to reverse engineering that would cause it to create a configuration class per entity, such as is described here: #2805. We discuss this as part of post-2,0 planning.

With regard to startup time, this is an area where we still need to do some optimization, and where also some form of compiled model would help--see #1906.

If anybody has large models that they could share with us to help provide data for the perf work, then that would be much appreciated.

Member

ajcvickers commented May 12, 2017

There are two main things being discussed here:

  • The large context file
  • The startup time for large models

With regard to the context file, switching to data annotations should make the context file smaller and will not change the functionality at all--or if it does, then please report it as a bug. Reverse engineering will only substitute a data annotation if it will maintain the same behavior as the fluent API call it is replacing.

Beyond that, we have talked about providing an option to reverse engineering that would cause it to create a configuration class per entity, such as is described here: #2805. We discuss this as part of post-2,0 planning.

With regard to startup time, this is an area where we still need to do some optimization, and where also some form of compiled model would help--see #1906.

If anybody has large models that they could share with us to help provide data for the perf work, then that would be much appreciated.

@ajcvickers ajcvickers changed the title from Main Context File is too big to Enable splitting the output from reverse engineering so that a huge DbContext file can be avoided May 15, 2017

@ajcvickers ajcvickers added this to the Backlog milestone May 15, 2017

@ajcvickers

This comment has been minimized.

Show comment
Hide comment
@ajcvickers

ajcvickers May 15, 2017

Member

Note from triage: consider using EntityTypeConfiguration or partial classes.

Member

ajcvickers commented May 15, 2017

Note from triage: consider using EntityTypeConfiguration or partial classes.

@JuanIrigoyen

This comment has been minimized.

Show comment
Hide comment
@JuanIrigoyen

JuanIrigoyen Aug 16, 2017

I have tested a new solution using the latest version of EF core 2 and DataAnnotations, my current file pass from 22000 lines to 10000. However most of the lines in this file refer to the default values, I do not agree with this approach, since the default values should be part of the metadata of the entity using an attribute how [DefaultValue(0)]. In any case use a file with half of lines makes it easier to work with it.

Example of Entity:
modelBuilder.Entity(entity =>
{
entity.Property(e => e.Completado).HasDefaultValueSql("((0))");
entity.Property(e => e.Diferencia).HasDefaultValueSql("((0))");
entity.Property(e => e.Divisa).HasDefaultValueSql("('EUR')");
entity.Property(e => e.Fecha_anticipo).HasDefaultValueSql("(getdate())");
entity.Property(e => e.Fecha_creacion).HasDefaultValueSql("(getdate())");
entity.Property(e => e.Importe_anticipo).HasDefaultValueSql("((0))");
entity.Property(e => e.Importe_gastado).HasDefaultValueSql("((0))");
entity.Property(e => e.Saldo).HasDefaultValueSql("((0))");
entity.HasOne(d => d.PersonalNavigation)
.WithMany(p => p.Anticipos)
.HasForeignKey(d => d.Personal)
.HasConstraintName("FK_Anticipos_Personal");
});

For my the entity must be:

modelBuilder.Entity(entity =>
{
entity.HasOne(d => d.PersonalNavigation)
.WithMany(p => p.Anticipos)
.HasForeignKey(d => d.Personal)
.HasConstraintName("FK_Anticipos_Personal");
});

JuanIrigoyen commented Aug 16, 2017

I have tested a new solution using the latest version of EF core 2 and DataAnnotations, my current file pass from 22000 lines to 10000. However most of the lines in this file refer to the default values, I do not agree with this approach, since the default values should be part of the metadata of the entity using an attribute how [DefaultValue(0)]. In any case use a file with half of lines makes it easier to work with it.

Example of Entity:
modelBuilder.Entity(entity =>
{
entity.Property(e => e.Completado).HasDefaultValueSql("((0))");
entity.Property(e => e.Diferencia).HasDefaultValueSql("((0))");
entity.Property(e => e.Divisa).HasDefaultValueSql("('EUR')");
entity.Property(e => e.Fecha_anticipo).HasDefaultValueSql("(getdate())");
entity.Property(e => e.Fecha_creacion).HasDefaultValueSql("(getdate())");
entity.Property(e => e.Importe_anticipo).HasDefaultValueSql("((0))");
entity.Property(e => e.Importe_gastado).HasDefaultValueSql("((0))");
entity.Property(e => e.Saldo).HasDefaultValueSql("((0))");
entity.HasOne(d => d.PersonalNavigation)
.WithMany(p => p.Anticipos)
.HasForeignKey(d => d.Personal)
.HasConstraintName("FK_Anticipos_Personal");
});

For my the entity must be:

modelBuilder.Entity(entity =>
{
entity.HasOne(d => d.PersonalNavigation)
.WithMany(p => p.Anticipos)
.HasForeignKey(d => d.Personal)
.HasConstraintName("FK_Anticipos_Personal");
});

@bricelam bricelam self-assigned this Aug 16, 2017

@bricelam

This comment has been minimized.

Show comment
Hide comment
@bricelam

bricelam Aug 16, 2017

Member

I will look into this as part of #831.

It's pretty easy to do now that we have IEntityTypeConfiguration<T> (#2805). We just need to decide on the best default behavior.

Member

bricelam commented Aug 16, 2017

I will look into this as part of #831.

It's pretty easy to do now that we have IEntityTypeConfiguration<T> (#2805). We just need to decide on the best default behavior.

@ajcvickers ajcvickers modified the milestones: 2.1.0, Backlog Sep 7, 2017

@bricelam bricelam changed the title from Enable splitting the output from reverse engineering so that a huge DbContext file can be avoided to RevEng: Generate IEntityTypeConfiguration classes (avoids large OnConfiguring) Sep 8, 2017

@piyey

This comment has been minimized.

Show comment
Hide comment
@piyey

piyey Sep 12, 2017

Is this working on today's update?

piyey commented Sep 12, 2017

Is this working on today's update?

@bricelam

This comment has been minimized.

Show comment
Hide comment
@bricelam

bricelam Sep 13, 2017

Member

Nope. Work is still open/scheduled for the 2.1.0 milestone/release.

Member

bricelam commented Sep 13, 2017

Nope. Work is still open/scheduled for the 2.1.0 milestone/release.

@piyey

This comment has been minimized.

Show comment
Hide comment
@piyey

piyey Sep 13, 2017

Ups, didn't see that sorry x'D (milestones: 2.1.0)

piyey commented Sep 13, 2017

Ups, didn't see that sorry x'D (milestones: 2.1.0)

@bricelam

This comment has been minimized.

Show comment
Hide comment
@bricelam

bricelam Sep 13, 2017

Member

@piyey You're not the first, and you certainly won't be the last. 😉

Member

bricelam commented Sep 13, 2017

@piyey You're not the first, and you certainly won't be the last. 😉

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