Category: ef

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 : https://learn.microsoft.com/en-us/ef/core/managing-schemas/scaffolding/ . But how generate all classes , controllers and others ? I was into https://learn.microsoft.com/en-us/dotnet/csharp/roslyn-sdk/source-generators-overview – but this does not allow programmers to modify what it generates. But , fortunately, this year EFCore7 has templates, based on t4 templates : https://learn.microsoft.com/en-us/ef/core/managing-schemas/scaffolding/templates?tabs=dotnet-core-cli . That means that now we could generate whatever we need!

So this is the project : A DB2Code Visual Studio Template. You can download from https://marketplace.visualstudio.com/items?itemName=ignatandrei.databasetocode  . Also, the source code is at https://github.com/ignatandrei/queryViewer/ . 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) =>

WebHost.CreateDefaultBuilder(args)
Host.CreateDefaultBuilder(args)

.UseStartup<Startup>()
.ConfigureWebHostDefaults(webBuilder =>

.Build();

to

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

WebHost.CreateDefaultBuilder(args)
Host.CreateDefaultBuilder(args)

.UseStartup<Startup>()
.ConfigureWebHostDefaults(webBuilder =>

.Build();
{

webBuilder.UseStartup<Startup>();

});

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

4. Modify services.AddMvc();   to

services.AddControllers();

services.AddRouting();

5. Modify  app.UseMvc();  to

app.UseRouting();

app.UseEndpoints(endpoints =>

{

endpoints.MapControllers();

});

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″ />

to

<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″ />
<PrivateAssets>all</PrivateAssets>

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

</PackageReference>

<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; }

}

Answer:

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();
                 default:
                     throw new ArgumentException($”cannot find bank {bank}”);
             }
         }
     }

Infovalutar

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 https://github.com/ignatandrei/InfoValutar/ )
NrPost 
1Start
2Reading NBR from internet
3Source control and build
4Badge and test
5CI and action
6Artifacts and dotnet try
7Docker with .NET Try
8ECB
9Intermezzo - Various implementations for programmers
10Intermezzo - similar code - options
11Plugin implementation
12GUI for console
13WebAPI
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 : https://stackoverflow.com/questions/3556506/using-like-in-sp-executesql

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.