Support for EF Core

[airomero]

Hi, is there any plan to support EF7? EF7 is more stable now and we love EFReversePoco, so it'd be so nice to start playing with both together. Thanks!!

[sjh37]

@cyberbobcity It's a C# 7 course.

[marekvse]

I was so hoping it would be available when I start a new project, which is today. Sad it is not but very happy you are back working on it Simon. :) I hope it will not give you too much more trouble. Thanks for your work!

[sjh37]

You'll be pleased to hear I am now using templates to simplify code generation :-) Here is a tiny snippet from the database context factory for EF 6

public override string DatabaseContextFactory()
{
    return @"
{{classModifier}} class {{contextName}}Factory : System.Data.Entity.Infrastructure.IDbContextFactory<{{contextName}}>
{
    public {{contextName}} Create()
    {
        return new {{contextName}}();
    }
}";
}

In case you're wondering what Mustache library I used. I didn't as I wanted it as simple as possible with zero dependancies.

The transformation code is simply:

public static string Transform(string template, Dictionary<string, string> data)
{
    if (data != null)
    {
        foreach (var variable in data)
        {
            template = template.Replace("{{" + variable.Key + "}}", variable.Value);
        }
    }

    return template;
}

For the above template you could pass in

var data = new Dictionary<string, string>
{
    ["classModifier"] = "public",
    ["contextName"] = "TestContext"
};

I'll be investigating how to easily cope with looping within templates next...

[kinetiq]

@sjh37 Man, you can do that inside T4?

I guess you can if you hack together a simple replacement engine, eh?

[kachalkov]

That is awesome news @sjh37!

[sjh37]

I am now using the full power of mustache-sharp so you lot can do some crazy cool stuff with templates now. Scroll down this page https://github.com/jehugaleahsa/mustache-sharp to see what I mean.

It can now write the database context interface from

var data = new
{
    interfaceModifier = Settings.DbContextInterfaceModifiers ?? "public partial",
    Settings.DbContextInterfaceName,
    Settings.DbContextInterfaceBaseClasses,
    tables = tables
        .Where(t => !t.IsMapping && t.HasPrimaryKey)
        .OrderBy(x => x.NameHumanCase)
        .Select(tbl => new
        {
            dbSetName = tbl.NameHumanCaseWithSuffix(),
            pluralTableName = Inflector.MakePlural(tbl.NameHumanCase),
            hasComment = Settings.IncludeComments != CommentsStyle.None,
            comment = tbl.Name
        })
};

With the template:

public override string DatabaseContextInterface()
{
    return @"
{{interfaceModifier}} interface {{DbContextInterfaceName}} : {{DbContextInterfaceBaseClasses}}
{
{{#each tables}}
    System.Data.Entity.DbSet<{{dbSetName}}> {{pluralTableName}} { get; set; }{{#if hasComment}} // {{comment}}{{/if}}
{{/each}}
}";
}

and have output generated as:

public interface IMyDbContext : System.IDisposable
{
    System.Data.Entity.DbSet<AlphabeticalListOfProduct> AlphabeticalListOfProducts { get; set; } // Alphabetical list of products
    System.Data.Entity.DbSet<Category> Categories { get; set; } // Categories
    ...
}

If you are wondering why I'm using a mustache templates instead of T4 templates:

1. It looks far nicer, and hence is easier to edit
2. It also allows me to bypass T4 completely, and add a dotnet command such as "dotnet efrpg settings.tt" and have the code generated from the command line. Yes, you will still get your much loved database.tt file you can save too to have it instantly generate within Visual Studio as before :-)

Really pleased with how it's coming along.

[kachalkov]

That's is awesome Simon!!! Loving it!

[kinetiq]

Wow, this is great. I might switch to this for email generation - I've been using razor, but in .NET Core 2 it's a bit of a chore due to the libraries not quite being there yet. I've been thinking about alternatives, thanks!

[kinetiq]

@sjh37 Are you doing anything special for IDE support around Mustache? I found this: https://marketplace.visualstudio.com/items?itemName=dawhite.mustache

...And there's also something for Resharper.

[sjh37]

@kinetiq No I haven't, but that looks neat, thanks :-)

[mikaelliljedahl]

If T4 templates is an issue with .Net Core project, i can recommend Scripty, https://github.com/daveaglick/Scripty I replaced the usage of T4 with Scripty to generate API-related stuff. Works better with the VS IDE without paid extensions and you write C# code to generate C# code and it seems it could be compatible with mustache. In my setup the code is created during build (using Scripty.MSBuild) so it is never forgotten.

[sjh37]

The code comming out of this new generator is so much nicer than before

Before (EF 6 snippet)

public interface IMyDbContext : System.IDisposable
{
    System.Data.Entity.DbSet<Category> Categories { get; set; } // Categories
    System.Threading.Tasks.Task<int> SaveChangesAsync();
    System.Collections.Generic.IEnumerable<System.Data.Entity.Validation.DbEntityValidationResult> GetValidationErrors();
    ...

After (EF 6 snippet)

public interface IMyDbContext : IDisposable
{
    DbSet<Category> Categories { get; set; } // Categories
    Task<int> SaveChangesAsync();
    IEnumerable<DbEntityValidationResult> GetValidationErrors();
    ...

Long gone are the System. usings before each item. They are now intelligently handled via the template and placed up the top, just as you would if you hand coded it.

[kinetiq]

Cannot wait for this man. I've been using dotnet ef dbcontext scaffold, and it does not have very many config options at all. :)

It does get the job done however.

[hanjunjun]

The author's plugin is very powerful. Looking forward to the .net core version

[Phylum123]

Is this coming out any time soon? The reason I ask is because in Nov. you said a couple of months.

[ErikEJ]

@Phylum123 In the meantime, why not have a look at EF Core Power Tools? It has object renaming and supports Handlebars templates for some amount of customization.

[sjh37]

I have the new code generating EF 6 code accurately. I needed that to test everything is working with various databases. I'm halfway through creating the cake build for it, which is complicated as T4 code sits in one namespace. After that is the simpler process of generating ef core code. I was over optimistic and now think it will be may

[sjh37]

If only I could work on this full time...

[ClassyCircuit]

@sjh37 If the new code is already able to generate classes, could you release an early version on a separate branch? I would love to try it out, even if it hasn't been fully tested.

[claerhouth]

Just want to express our support here from our development team. We are highly anticipating this feature release. If this is complete we can leave a lot of .net framework dependencies for our project behind and keep our base .net core compatible. So kudos to you sir...

[sjh37]

Performance news. It's fast 🥇

From a cold (no-cache) sql server:

• Northwind database 1.2 total seconds with 0.2 seconds spent inside generator code.
• Large database (1100 tables, 500 stored procs) 27.0 total seconds with 2.8 seconds spent inside generator code.

From a warm (cached) sql server:

• Northwind database 0.6 total seconds with 0.2 seconds spent inside generator code.
• Large database (1100 tables, 500 stored procs) 5.9 total seconds with 2.8 seconds spent inside generator code.

[kinetiq]

@sjh37 That's great! I am now at a place where the built-in CLI database generator actually has an error (one to one relationship) and I have to manually rename this one column every time I re-generate. Can't wait to get my hands on this.

[sjh37]

News update: Fluent index definitions

As part of case #488 I have now added fluent index definitions to the database context. This has been added to the new v3 generator. However, I'm not going to back port it to v2 as that is a lot of work.

Given

CREATE TABLE MultipleKeys
(
    UserId INT NOT NULL,
    FavouriteColourId INT NOT NULL CONSTRAINT UC_MultipleKeys_FavouriteColour UNIQUE,
    BestHolidayTypeId INT NOT NULL,
    BankId INT NOT NULL,
    CarId INT NOT NULL
)
GO
ALTER TABLE MultipleKeys ADD CONSTRAINT PK_MultipleKeys
    PRIMARY KEY CLUSTERED (UserId, FavouriteColourId, BestHolidayTypeId)
CREATE UNIQUE INDEX IX_MultipleKeys_Holiday_Bank ON MultipleKeys(BestHolidayTypeId, BankId)
CREATE INDEX IX_MultipleKeys_BestHolidayType ON MultipleKeys(BestHolidayTypeId)
GO

It will generate:

modelBuilder.Entity<MultipleKey>()
    .Property(e => e.UserId)
    .HasColumnAnnotation(
        IndexAnnotation.AnnotationName,
        new IndexAnnotation(new IndexAttribute("PK_MultipleKeys", 1) { IsUnique = true, IsClustered = true })
    );

modelBuilder.Entity<MultipleKey>()
    .Property(e => e.FavouriteColourId)
    .HasColumnAnnotation(
        IndexAnnotation.AnnotationName,
        new IndexAnnotation(new[]
        {
            new IndexAttribute("PK_MultipleKeys", 2) { IsUnique = true, IsClustered = true },
            new IndexAttribute("UC_MultipleKeys_FavouriteColour", 1) { IsUnique = true }
        }));

modelBuilder.Entity<MultipleKey>()
    .Property(e => e.BestHolidayTypeId)
    .HasColumnAnnotation(
        IndexAnnotation.AnnotationName,
        new IndexAnnotation(new[]
        {
            new IndexAttribute("IX_MultipleKeys_BestHolidayType", 1),
            new IndexAttribute("IX_MultipleKeys_Holiday_Bank", 1) { IsUnique = true },
            new IndexAttribute("PK_MultipleKeys", 3) { IsUnique = true, IsClustered = true }
        }));

modelBuilder.Entity<MultipleKey>()
    .Property(e => e.BankId)
    .HasColumnAnnotation(
        IndexAnnotation.AnnotationName,
        new IndexAnnotation(new IndexAttribute("IX_MultipleKeys_Holiday_Bank", 2) { IsUnique = true })
    );

[kinetiq]

Awesome. Can't wait man. :)

[sjh37]

News update: Filtering has changed for the better I've ripped out the standard way schema/tables/etc were filtered and replaced it with everything you see in the red box below.

The sharp eyed among you may have spotted the SingleDbContextFilter and MultipleDbContextFilter.

SingleDbContextFilter

This contains what you'd expect, and a little more. Filtering can now be done via one or more Regex's and one or more functions. Gone are the days of a single do-it-all regex, you can now split them up into many smaller Regex's. It's now up to you how to want to mix and match them.

Here is a snippet:

public SingleDbContextFilter()
{
    // Filtering *************************************************************************************************************************
    // Use the following filters to exclude or include schemas/tables/views/columns/stored procedures.
    // You can have as many as you like, and mix and match them.
    // They run in the order defined below.
    // For help with Regex's try https://regexr.com
    // Feel free to add more filter types and include them below.
    SchemaFilters.AddRange(new List<IFilterType<Schema>>
    {
        new PeriodFilter(), // Keep this first as EF does not allow schemas to contain a period character

        // To include the only the schemas 'dbo' and 'events'
        //new RegexIncludeFilter("^dbo$|^events$"),

        // Add your own code to these custom filter classes
        new SchemaFilter(),
    });

    TableFilters.AddRange(new List<IFilterType<Table>>
    {
        new PeriodFilter(), // Keep this first as EF does not allow tables to contain a period character

        // To include all the customer tables, but not the customer billing tables
        //new RegexExcludeFilter(".*[Bb]illing.*"), // This excludes all tables with 'billing' anywhere in the name
        //new RegexIncludeFilter("^[Cc]ustomer.*"), // This includes any remaining tables with names beginning with 'customer'

        // To exclude all tables that contain '_FR_' or begin with 'data_'
        //new RegexExcludeFilter("(.*_FR_.*)|(data_.*)"),

        // Pass in your own custom Regex
        //new RegexIncludeFilter(new Regex("^tableName1$|^tableName2$", RegexOptions.IgnoreCase, TimeSpan.FromMilliseconds(200))),

        // Add your own code to these custom filter classes
        new TableFilter(),
        new HasNameFilter(FilterType.Table),
    });

    ColumnFilters.AddRange(new List<IFilterType<Column>>
    {
        new PeriodFilter(), // Keep this first as EF does not allow columns to contain a period character

        // Exclude any columns that begin with 'FK_'
        //new RegexExcludeFilter("^FK_.*$"),

        // Add your own code to these custom filter classes
        new ColumnFilter(),
        new HasNameFilter(FilterType.Column),
    });

    StoredProcedureFilters.AddRange(new List<IFilterType<StoredProcedure>>
    {
        new PeriodFilter(), // Keep this first as EF does not allow stored procedures to contain a period character

        // Add your own code to these custom filter classes
        new StoredProcedureFilter(),
        new HasNameFilter(FilterType.StoredProcedure)
    });
}

MultipleDbContextFilter

More of an enterprise feature. This will read in the filter and setting requirements from a database table, and will now have the ability to generate many db contexts, poco, etc all in a single go. The database is read just once, and many (can be 100's) database contexts are generated, with just the specific tables, columns and stored procs you want in each db context, each within its own namespace.

I'm still working on the multi db context generation, and will be a few weeks doing this. I really can't wait to release v3 of the generator to the world, it blows the socks off v2.

I'll keep you posted as always. Simon Hughes.

[TimSirmovics]

Love your work!

[ClassyCircuit]

@sjh37 How is it coming along? Do you think it could be released this month? Thank you.

[sjh37]

Hi @janissimsons It would of been almost ready by now. However, I've been commisioned to enhance it to generate multiple (100's) db contexts in a single go, all the configuration (which tables/columns to generate for which db context) comes from a database query. I'm working on it as much as I can, which is about 2-4 hours a day, and this commissioned work will also be provided in the v3 generator for you guys to use as no extra charge :-) A win-win, but delays the v3 product launch date as I'm having to spend about 100 hours ripping apart standard filtering code in order to do filtering later on, on a per db context basis afterwards. The end result is a cleaner design, more comprehensive filtering, with an enterprise feature allowing you to generate more than one db context, and having the configuration optionally come from a separate database instead of code.

Before the official launch to everyone, I will be looking for beta testers for both EF6 use and for EF.Core.

[aeslinger0]

@sjh37, since you're reworking so much of the tool right now, have you thought about allowing pluggable providers to supply the database meta data and settings instead of it being embedded in the tool? It would allow the community to contribute providers for databases other than SQL Server and possibly other sources as well. For example, a while back I created a tool called SqlSharpener (which I've pretty much abandoned) but its goal was to generate C# code from SSDT projects. If you allowed pluggable providers, someone (maybe myself) could write a provider that pulls the meta data directly from SSDT projects and avoid having to deploy the database first in order to regenerate.

[sjh37]

Hi @aeslinger0 Interesting. You specify the database type you want to use in Settings:

public static DatabaseType DatabaseType   = DatabaseType.SqlServer; // SqlCe. Coming next: PostgreSQL, MySql, Oracle

How about a custom entry added to:

public enum DatabaseType
{
    SqlServer,
    SqlCe,
    Custom, // Read sql from files
    MySql,      // Not yet implemented
    PostgreSQL, // Not yet implemented
    Oracle      // Not yet implemented
}

That can then be picked up by

public static class DatabaseReaderFactory
{
    public static DatabaseReader Create(DbProviderFactory factory, GeneratedTextTransformation outer)
    {
        switch (Settings.DatabaseType)
        {
            case DatabaseType.SqlServer:
                return new SqlServerDatabaseReader(factory, outer);

            case DatabaseType.SqlCe:
                return new SqlServerCeDatabaseReader(factory, outer);

            case DatabaseType.Custom: // <<<====
                return new CustomDatabaseReader(factory, outer);

            case DatabaseType.MySql:
                return new MySqlDatabaseReader(factory, outer);

            case DatabaseType.PostgreSQL:
                return new PostgreSqlDatabaseReader(factory, outer);

            case DatabaseType.Oracle:
                return new OracleDatabaseReader(factory, outer);

            default:
                throw new ArgumentOutOfRangeException();
        }
    }
}

Which you get back a CustomDatabaseReader that could read in the sql it needs from files that are under your control. Would that work for you, or would you see it working in a different way?

[aeslinger0]

@sjh37, maybe, I'm not quite sure I'm fully visualizing your proposed solution. I'm used to plugins similar to this where I would implement your plugin interface, you'd maybe pass some context, and I'd return objects that implement interfaces to conform to whatever shape you need the data to be in. Then the consumer of your tool would alter the configuration/settings so it knows about the plugin.

I suppose the implementation you choose isn't as important so long as the author of the plugin can implement your interfaces and have the source of the meta data be from anywhere they want... a database, SSDT project, XML, a REST API endpoint, who know what crazy and cool things people will come up with!

[sjh37]

Hi @aeslinger0 , Ok fully understand what you are after now. Shouldn't be hard to implement at all :-) Thanks for the great idea. Can you raise this as a new Git request so it can be tracked separately to this EF.Core thread.

[aeslinger0]

Sure thing :) #501

[gkello]

Hi @sjh37 Cool that You have been investing so much into the tool. We are currently using modified version of Your tool to generate code for ef.core Currently we are facing some issues with relations targeting unique constraint instead of primary key. So would be awesome to test out the new version and see if it supports our case or how to enhance it for our use case. Started to investigate changes to old version, but do not want to do that so much if new one is coming out soon....

[intomneato]

Hi sjh37, I've been following your blog for a while, just one more amongst the horde of developers spoiled by your original tool and now eagerly awaiting the Core capable version :) Can I request a quick update on rough expected timeframe to release? Really I just need the single database simple-case version to use with MS Sql Server, so if scope creep on things like multiple database capability is weighing you down, just know there are some out here who could use a "light" version sooner rather than a super robust version eventually. Thanks for being awesome!

[ErikEJ]

@intomneato In the meantime you could try EF Core Power Tools, they may be able to fulfill your needs.

[intomneato]

@intomneato In the meantime you could try EF Core Power Tools, they may be able to fulfill your needs.

Okay, I'll take a look. Thanks!

[sjh37]

@intomneato Thanks for the kind words! I'm currently working on adding the multi-db context generation, approximately 2 weeks left on that. Then onto the final part, which is the ef.core mustache templates.

I'll be in need for beta testers before I can release to the public at large. I've also specified C# 6.0 as a minimum requirement. Although I'd love to move to the latest C# 7 enhancement, a lot of people still require it to support their existing projects.

As this is a new project, which i've been working on for a long time now, and has largely been re-written, there will be a charge for this new generator for commercial use, however, it will remain free to students, educators, demos, etc. This will allow me to continue to work on this project on a full time basis as there are still so many more enhancements that can be done. Oracle being one. I plan on giving a commercial license to everyone that has ever had a pull request accepted. No details yet, but watch this space.

[Curdie]

Fantastic news. Thanks for the update.

[kinetiq]

@sjh37 Just wanted to say I'm glad you are charging for this. It's an important piece of architecture, and charging means it will more likely be well maintained.

[sjh37]

@kinetiq Hi, just wanted to say thanks. Thought there may have been a little backlash, but it's all been positive. I really want to invest serious time in it and make it simply the must-have tool when working with EF.

Million dollar questions (feedback much appreciated from everyone!)

1. How much would you be happy paying for it?
2. How much would you expect a company wide, unlimited user, license be?

[kinetiq]

@sjh37 I think as it stands, I would comfortably pay $99/user/year. There was a moment where I was a little desperate and I would have probably paid $149 then.

As a company license, first I would point out that not everyone does this anymore. Resharper definitely does not. Redgate does not AFAIK. But if you need this, I would look at competitors and figure out what their formula is. Maybe it's 10*[user] but make sure you figure out whether they have more ways to monetize those large customers. That could be leaving a lot of money on the table.

I love figuring out price tiers. Make sure you brainstorming everything you can think of that could possibly add value... Email support, phone support, professional services, advanced features like what you are building now. Would it make sense to build a nice GUI? Could there be some sort of clever cloud story? Automatic re-generation when the DB changes? Is there more you could do with code generation here? If you are building out entities, you could generate dynamic endpoints for CRUD operations, typescript DTOs, vue.js models, etc. Not saying you should actually do these things - I suppose I am trying to explain what brainstorming might look like here.

If you are struggling to figure out a free tier, consider making it free for open source projects, MVPs, and maybe cheaper for academics.

I bet you can get it up to $249 or higher and then maybe get acquired by red gate. :)

[kinetiq]

@sjh37 Oh, here's one for you. Maybe find a way to make this easy to drop into existing projects generated using, for instance, dotnet ef dbcontext scaffold. It will be hard to snap this into the project I originally wanted to use this on, but if you had some out of the box settings that are compatible with what EF generates, I would buy it and replace this in a heartbeat, because there are a few areas that are just broken.

[ClassyCircuit]

99 usd per year is way too expensive, not everyone is a millionaire. It should be a one time cost, where if you want to upgrade then you can renew your license, otherwise after the license period expires you can keep using the version you paid for legally. Similar to how Sublime Text operates.

[kinetiq]

I mean I am a one man consulting company, not rolling in the dough by any means, and I spend money on:

• Kendo - ~$600/year
• Resharper - $179/year
• OzCode - $175/year
• RedGate - ~$300/year

It does add up, but if a tool makes me more productive, it's almost always worth it, in my opinion. I am glad these companies charge enough that they can continuously improve their products.

As a side note, I believe Sublime does charge you an upgrade fee when they have new major versions.

[baoshenyi]

Personally, I am ok with yearly subscription or one time licence purchase. I like to add one more suggestion. Could you please make a video on udemy instead of www.pluralsight.com since it requires monthly subscription that I don't like. Will try EF6 in .net core soon. Cheers!!!

[cgonzalezri]

We use your tool in a company wide project, and we're happy to pay the price you decide if that means you will have more time and resources to develop it.

99/eur per year seems a nice price!

[Phylum123]

SO it's been since Feb. that I asked if EF Core support was coming out. I figured I could ask again.

1. is EF CORE support coming out?
2. Will it be only in the paid version?
3. Will it be any time soon?

Thanks :).

Since I am a nice guy, here is a work around, that works with ASP core, etc. It works quite well, but has it's issues.

1. Create a REGULAR class library and install ef6 and this tool, etc.

2. Set the .tt file to generate POCO's only (NO mapping, NO DBContext etc).

3. In the .tt file set data annotations to ON.

4. Create a .Net Standard Library to be used as your data communication project (or right into the main project if you want)

5. In the Properties->Build Event -> pre-build event command line for this new project, put in the following: xcopy /Y "$(SolutionDir)\Generated*.cs" "$(ProjectDir)Generated\Models"

6. Use Scaffold DBConext to generate your context.

7. Rebuild your solution. This is important. Scaffold dbcontext will overwrite your models generated with this tool. When you rebuild, thanks to the command line argument, it will overwrite them with the proper version of the models.

If you make a change to your database simply right click and run custom tool as per usual and then rebuild. IF you added tables or changed the structure and you want to regen the DbContext, then run Scaffold first, then run custom tool on the .tt file and then rebuild the solution.

[sjh37]

As of Friday 7th June I have finished adding the new Multi-Context generation feature. This will read in the filter and setting requirements from a database table, and will have the ability to generate many db contexts, poco, etc all in a single go. The database is read just once, and many (can be 100's) database contexts are generated, with just the specific tables, columns and stored procs you want in each db context, each within its own namespace.. More info on that here.

I'm now back on finishing off EF Core, which is actually now just a case of creating the mustache templates for it as all the other code is now written and in place to support it. If all goes well, I'm hoping to have this done before the end of the month. At that point, I will publish a beta version for public testing and feedback. While that is going on I'll create a website to sell it and also sort out any legals.

There will be a free non-commercial version for tutors, students, etc. :) Paid will only ever be for commercial use.

For reference the code has jumped from 6365 lines of code in v2, to 16257 lines in v3. After the initial release of v3, future versions will include support for Oracle, MySql and Postgresql. After that I will add support for command line execution via a dotnet command.

[sjh37]

News: v3 now supports custom templates. Meaning you can use either Ef6, EfCore, or your own. Documentation here.

One handy feature of this is that you can generate standard C# code templates for Entity Framework, and have other templates generating Java classes for your webapp. Or simply alter the C# code generation to suit your needs.

EF6 template files can be downloaded here. Still working on EFCore template files.