Category: ef

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″

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

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 .