Category: ef

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

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.