Category: ef

Comparing EFCore Database Providers-part-2

1 Part 1
2 Part 2
3 Part 3

I have started with a simple table  –  Department( id autogenerated , name) and generate EFCore context and classes with scaffolding templates .

Then I have created a simple text ( XUnit + LightBDD ) in order to test

–  generateDatabase from classes


– some simple search .

Works perfectly, so to the next – more different tables ( datetime, binary, money and so on)  => scaffold code => make tests.

the first problem – column type

When generating the scaffolding the OnModelCreating generates .HasColumnType – for example,

modelBuilder.Entity<Tbl_DATETIME>(entity =>
    entity.HasKey(e => e.ID).HasName("PK__Tbl_DATE__3214EC27B76F5021");

    entity.Property(e => e.DataColumn).HasColumnType("datetime");

And PostgresSql has no type that is called “datetime”, but “timestamp” –

So what I do is to have OnModelCreatingPartial that is called AFTER all set and re-write

modelBuilder.Entity<Tbl_DATETIME>(entity =>
    entity.HasKey(e => e.ID).HasName("PK__Tbl_DATE__3214EC27B76F5021");

    entity.Property(e => e.DataColumn).HasColumnType(null);

Even if column type is null, the CreateDatabase knows that the field is DateTime

public partial class Tbl_DATETIME
    public int ID { get; set; }

    public DateTime? DataColumn { get; set; }

so all is ok.

Second Problem – datetime in UTC

The DateTime that is on the class generates a column that supports ONLY UTC kind in datetimes . The error is

–> System.InvalidCastException : Cannot write DateTime with Kind=Local to PostgreSQL type ‘timestamp with time zone’, only UTC is supported. Note that it’s not possible to mix DateTimes with different Kinds in an array/range. See the Npgsql.EnableLegacyTimestampBehavior AppContext switch to enable legacy behavior.


Makes sense, usual in database you want just the UTC and transform for each user

Third Problem – datetime have a millisecond difference sometimes

This is the test:

var dataM = new Tbl_DATETIME();
dataM.ID = 3;
dataM.DataColumn = DateTime.Now.AddDays(100);
using (var db = await startDatabase.GetNewContext<SimpleTablesMultipleData>())
    await db.Tbl_DATETIMEModify(dataM);
using (var db = await startDatabase.GetNewContext<SimpleTablesMultipleData>())
    var verify = await db.Tbl_DATETIMEGetSingle(dataM.ID);


And the error is

Xunit.Sdk.XunitException : Expected verify!.DataColumn to be <2023-12-13 21:45:39.6089917>, but found <2023-12-13 21:45:39.608991>.

You can see the Ok tests at

And the problems at

Comparing EFCore Database Providers-part-1

1 Part 1
2 Part 2
3 Part 3

I wanted to see if there are any differences in EFCore database providers listed at

I want to test the capabilities for each one within a standard choice of tables , in order to know the capabilities

I choose only those that have a version for current STS / LTS , whatever it is current.

( I am particularly interested in SqlServer vs Sqlite )

Problem 1: Conflicting namespaces

For MySql – there are 2 providers , Pomelo.EntityFrameworkCore.MySql  and MySql.EntityFrameworkCore  . Both have the same namespace and class for .UseMySql  ( and other stuff)

So how to do it ? Fortunately, nuget supports alias .

So the code in csproj is

<PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="7.0.0" Aliases="PomeloEFMySql"  />
<PackageReference Include="MySqlConnector" Version="2.2.5" Aliases="MySqlConnect" />

<PackageReference Include="MySql.EntityFrameworkCore" Version="7.0.5" Aliases="MySqlEFOracle" />
<PackageReference Include="MySql.Data" Version="8.1.0" Aliases="OracleMySql"/>

And the code in global.cs

extern alias OracleMySql;

extern alias PomeloEFMySql;
extern alias MySqlConnect;

global using MySqlCNBOracle = MySqlEFOracle.Microsoft.EntityFrameworkCore.MySQLDbContextOptionsExtensions;
global using MySqlOracle = OracleMySql.MySql.Data.MySqlClient;
global using MySqlEF = MySqlEFOracle::Microsoft.EntityFrameworkCore;
global using PomeloCN= MySqlConnect::MySqlConnector;
global using PomeloEF = PomeloEFMySql::Microsoft.EntityFrameworkCore;
global using PomeloMySqlCNB =PomeloEFMySql::Microsoft.EntityFrameworkCore.MySqlDbContextOptionsBuilderExtensions;

And the code to use it

case EFCoreProvider.Pomelo_EntityFrameworkCore_MySql:

    var serverVersion = PomeloEF.ServerVersion.AutoDetect(con);
    StepExecution.Current.Comment("version " + serverVersion.ToString());
    PomeloMySqlCNB.UseMySql(builder,con, serverVersion)
case EFCoreProvider.MySql_EntityFrameworkCore:

You can find the results at and

Problem 2 : Conflict on container ports

When a container is started for a test it works on a port ( 1433 for SqlServer). When a new test arrives ( with new tables ) , it cannot be on the same port . So the docker containers must be disposed when the test finishes. Also , the tests must be done in serial, not in paralel.

For parallelism, it is simple ( LightBDD + XUnit)

[assembly: CollectionBehavior(DisableTestParallelization = true)]
[assembly: ClassCollectionBehavior(AllowTestParallelization = false)]

For disposing, can use IScenarioTearDown (LightBDD) or IAsyncLifetime (XUnit)

Db2Code–part 1–idea

From the second year that I have started programming, I find tedious to replicate the tables structure to code and to generate classes and SQL . I started asking questions  – and no one has ever envisaged near me an ORM ( it was not a definition then ). I have made my own ORM  – and it was paying . However, I did not know how to market  – so oblivion was his fate.

Moving to this year , there are many ORM . One of those is EFCore – that has also a revers engineer /  scaffolding ready : . But how generate all classes , controllers and others ? I was into – but this does not allow programmers to modify what it generates. But , fortunately, this year EFCore7 has templates, based on t4 templates : . That means that now we could generate whatever we need!

So this is the project : A DB2Code Visual Studio Template. You can download from  . Also, the source code is at . Download, modify the connectionDetails.txt and it will generate from you everything to WebAPI from SqlServer or Sqlite ( more providers soon). Also, it generates  different projects ( models, context, controllers) – and you can also modify the templates. It works ( for the moment) just with tables with 0 or 1 PK.

The next parts will be only about the challenges to develop such a solution.

Passing a WebAPI application from .NET Core 2 to .NET Core 3 in 5 +1 (EF) steps

Simple steps:

1. Modiffy in the .csproj  <TargetFramework>netcoreapp2.0</TargetFramework> to <TargetFramework>netcoreapp3.1</TargetFramework> 

2.Delete <PackageReference Include=”Microsoft.AspNetCore.All” Version=”2.0.8″ />

3. Modify

public static IWebHost BuildWebHost(string[] args) =>
public static IHostBuilder CreateHostBuilder(string[] args) =>


.ConfigureWebHostDefaults(webBuilder =>



public static IHostBuilder CreateHostBuilder(string[] args) =>


.ConfigureWebHostDefaults(webBuilder =>




3.  Modify BuildWebHost(args).Run();  to CreateHostBuilder(args).Build().Run();

4. Modify services.AddMvc();   to



5. Modify  app.UseMvc();  to


app.UseEndpoints(endpoints =>




6. Optional : modify EF from

<PackageReference Include=”Microsoft.EntityFrameworkCore.SqlServer” Version=”2.1.1″ />
<PackageReference Include=”Microsoft.EntityFrameworkCore.SqlServer” Version=”3.1.3″ />

<PackageReference Include=”Microsoft.EntityFrameworkCore.Tools” Version=”2.1.1″ />
<PackageReference Include=”Microsoft.EntityFrameworkCore.Tools” Version=”3.1.3″>

<PackageReference Include=”Microsoft.VisualStudio.Web.CodeGeneration.Design” Version=”2.1.1″ />


<PackageReference Include=”Microsoft.EntityFrameworkCore.SqlServer” Version=”3.1.3″ />

<PackageReference Include=”Microsoft.EntityFrameworkCore.Tools” Version=”2.1.1″ />
<PackageReference Include=”Microsoft.EntityFrameworkCore.Tools” Version=”3.1.3″>

<PackageReference Include=”Microsoft.VisualStudio.Web.CodeGeneration.Design” Version=”2.1.1″ />

<IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>


<PackageReference Include=”Microsoft.VisualStudio.Web.CodeGeneration.Design” Version=”3.1.2″

EF Core identical tables and partial–part 30

Let’s suppose that you have 2 identical tables and you scaffold from the context the tables. It will give you 2 classes  – with the same properties. How can you make to work for the programmer as the same class, without modifying the models scaffolded by EF ?

Example – class Ecb and class Nbr from below are the same

public partial class Ecb
         public string ExchangeFrom { get; set; }
         public string ExchangeTo { get; set; }
         public DateTime Date { get; set; }
         public decimal ExchangeValue { get; set; }

public partial class Nbr
        public string ExchangeFrom { get; set; }
        public string ExchangeTo { get; set; }
        public DateTime Date { get; set; }
        public decimal ExchangeValue { get; set; }

public partial class InfoValutarContext : DbContext

       public InfoValutarContext(DbContextOptions<InfoValutarContext> options)
            : base(options)

       public virtual DbSet<Ecb> Ecb { get; set; }
        public virtual DbSet<Nbr> Nbr { get; set; }



Create an interface, add partials for the classes, add partial for context ( attention to namespaces)

public interface IExchangeRate
         DateTime Date { get; set; }
         string ExchangeFrom { get; set; }
         string ExchangeTo { get; set; }
         decimal ExchangeValue { get; set; }

public partial class Ecb: IExchangeRate
     public partial class Nbr : IExchangeRate

public partial class InfoValutarContext
         public IQueryable<IExchangeRate> RateQ(string bank)
             switch (bank?.ToLower())
                 case “ecb”:
                     return this.Ecb.AsNoTracking();
                 case “bnr”:
                     return this.Nbr.AsNoTracking();
                     throw new ArgumentException($”cannot find bank {bank}”);


And one hour passes...
(This is the result of 1 hour per day auto-challenge as a full cycle developer for an exchange rates application)
( You can see the sources at )
2Reading NBR from internet
3Source control and build
4Badge and test
5CI and action
6Artifacts and dotnet try
7Docker with .NET Try
9Intermezzo - Various implementations for programmers
10Intermezzo - similar code - options
11Plugin implementation
12GUI for console
14Plugin in .NET Core 3
15Build and Versioning
16Add swagger
17Docker - first part
18Docker - second part
19Docker - build Azure
20Pipeline send to Docker Hub
21Play with Docker - online
22Run VSCode and Docker
23Deploy Azure
24VSCode see tests and powershell
25Code Coverage
26Database in Azure
27Sql In Memory or Azure
28Azure ConString, RSS
29Middleware for backward compatibility
30Identical Tables in EFCore
31Multiple Data in EFCore
32Dot net try again
33Start Azure Function
34Azure function - deploy
35Solving my problems
36IAsyncEnumerable transformed to IEnumerable and making Azure Functions works
37Azure functions - final
38Review of 37 hours
39Last Commit in AzureDevOps
40Create Angular WebSite
41Add static Angular to WebAPI .NET Core
42Docker for Angular
43Angular and CORS
44SSL , VSCode, Docker
45Routing in Angular
46RxJS for Routing
47RxJs Unsubscribe

String interpolation for EFCore and Like

I have had a problem with EF Core and String Interpolation – until I realized that was a problem with sp_executesql

But let’s see the whole problem. ta

EFCore supports string interpolation –  that means , if you give this code

<context>.<table>.FromSql($”select blabla from table where id={id}”)

it will transform into

exec sp_executesql N’select blabla from table where id= @p0 ”
‘,N’@p0 int’,@p0=N’200′

And it is very good , because it avoids sql injection . ( Just a note, do not put

$”select blabla from table where id={id}”

into a variable  – you WILL have SqlInjection )

Ok. Now I want to do a like –  plain simple, right ?

<context>.<table>.FromSql($”select blabla from table where CountryName like ‘%{name}%’ ”)

And I try  –  and NO RESULT comes. I will into the generated Sql ( by using SqlProfiler from SSMS ) and it gives

exec sp_executesql N’select blabla from table where CountryName like ”%p0%”’,N’@p0 nvarchar(4000)’,@p0=N’france’

Pretty harmless , right ?

And seems ok – until I have executed directly and see that no results come.

So StackOverflow to the rescue :

And so my query in C# have been modified to

<context>.<table>.FromSql($”select blabla from table where where CountryName like ‘%’ + {name} + ‘%'” )

And now it works!

Moral of the  story : always investigate the sql generated .

Andrei Ignat weekly software news(mostly .NET)

* indicates required

Please select all the ways you would like to hear from me:

You can unsubscribe at any time by clicking the link in the footer of our emails. For information about our privacy practices, please visit our website.

We use Mailchimp as our marketing platform. By clicking below to subscribe, you acknowledge that your information will be transferred to Mailchimp for processing. Learn more about Mailchimp's privacy practices here.