Entity Framework Core

Entity Framework Core topics

  • ASP.NET Core

    EF Migration tasks in the Program.cs – The good and the Ugly

    Program.cs of any core project (especially ASP.Net Core) is our first starting point to get some necessary work out of the wrong. In this case EF DB Migrations. You know that EF thing where you keep your attached DB tables in sync with your data models? (or in some cases… mess up your existing database).

    Life in program.cs normally starts here:

    public class Program
    {
        public static void Main(string[] args)
        {
            CreateWebHostBuilder(args).Build().Run();
        }
    
        public static IWebHostBuilder CreateWebHostBuilder(string[] args) =>
            WebHost.CreateDefaultBuilder(args)
                .UseStartup<Startup>();
    }

    But let’s consider this:

     

    public class Program
    {
        public static async Task Main(string[] args)
        {
            IWebHost webHost = CreateWebHostBuilder(args).Build();
    
            // Create a new scope
            using (var scope = webHost.Services.CreateScope())
            {
                // Get the DbContext instance
                var myDbContext = scope.ServiceProvider.GetRequiredService<MyDbContext>();
    
                //Do the migration asynchronously
                await myDbContext.Database.MigrateAsync();
            }
    
            // Run the WebHost, and start accepting requests
            // There's an async overload, so we may as well use it
            await webHost.RunAsync();
        }
    
        public static IWebHostBuilder CreateWebHostBuilder(string[] args) =>
            WebHost.CreateDefaultBuilder(args)
                .UseStartup<Startup>();
    }

     

    What the? So what is going on here… Well, basically we are injecting our EF DB Migration process between the creating of our web app (Build) and the actual Running of the site… You know that period of time when processes are defined and setup and actually begin listening on the wire.

     Have a look at this article….

    https://andrewlock.net/running-async-tasks-on-app-startup-in-asp-net-core-part-1/

     

     

      

  • Microsoft

    EF Core adds DB Seeding back with some gotchas

    Looking back down the latest with EF core (in my case 2.1.4), I notice some gotchas when it comes to Data Seeding….

    So basically when you involve your migrations with using .HasData it’s understood that you must provide a initial value for all columns that are keys.
      **Side note: this comes from the EF Core team that they want to maintain consistency across multiple migrations. Well, I can understand their reasoning, but I think it’s unnecessary, because we are talking about in most cases initial testing data. I get it.

    With this requirement in mind, this brings up some problems with seeding. In particular with data classes that have constructors and entities with private setters. Consider the example (taken from a PluralSight course on this very topic):

    public class Location
    {
        public int LocationId { get; private set; }
        public string StreetAddress { get; set; }
        public string OpenTime { get; set; }
        public string CloseTime { get; set; }
        public List<Unit> BrewingUnits { get; set; }
        public Location(string streetAddress, string openTime, string closeTime)
        {
            StreetAddress = streetAddress;
            OpenTime = openTime;
            CloseTime = closeTime;
        }
    }

    For locationId we have a private setter, because this will be handled on the DB as Id type key. Well when it comes to doing a DataSeed:

    modelBuilder.Entity<Location>().HasData(
     new Location
      {
            LocationId = 1,
            StreetAddress = "1 Main Street",
            OpenTime = "6AM",
           CloseTime = "4PM"
        });

    This code is going to have a problem, because for New Location, the private setter will throw a compiler error, yeap. But EF Core HasData must be able to set ALL key values. Yeah, how to paint oneself into a corner.

    Well, anonymous types to the rescue!

    modelBuilder.Entity<Location>().HasData(
     new {
          LocationId = 1,
          StreetAddress = "1 Main Street",
          OpenTime = "6AM",
          CloseTime = "4PM"
         });

    It becomes anonymous because Location type is removed from the new statement- the Location type is already known because of .Entity of Location type- so EF Core already knows how to build the migration.

    If we look at the actual migration code:

    migrationBuilder.InsertData(
     table: "Locations",
      columns: new[] { "LocationId", "CloseTime", "OpenTime", "StreetAddress" },
               values: new object[] { 1, "4PM", "6AM", "1 Main Street" });

    We see the key is getting set- that will make EF Core happy.

    Is this a good or bad way to deal with this? It depends on your take on anonymous types. It does feel a little kluge like. But again, this comes from how the EF Core team decided to handle HasData seeding when it comes to keys. Btw, foreign keys are not excluded from this requirement. 

  • Blazor,  Microsoft

    Blazor and EF Core to Nowhere land.

    In the long decent into “Experimental” frameworks- this one being Blazor, I’ve been finding some challenges with getting Blazor to work with the latest EF Core. There’s a few examples of Blazor being used with EF Core (i.e. the CRUD example that’s floating around the inter-Tubes), but nothing that seems to work with the latest Blazor Lang template in the latest VS release.

    One can go to a lower EF Core version, well mostly… I’ve yet to get my Blazor Hosted Core test app to compile…. let alone the DB-Context to Scaffold… it just spews errors no matter which direction I go in… Oh Bother….

    I have to remember that Blazor is “Experimental” – But it would be nice if would keep up with the forward moving components like EF Core and the latest VS releases. I wonder what all of the Blazor Template releases are doing- seems like each time I run up VS there’s a new Blazor Lang Update. Curious…   

  • .Net Core,  Microsoft

    .net Core 2.1 and SQL via EF Core 2.1 -The revisit

    It’s time to revisit this topic but for the latest up dates for .Net Core (v 2.1.1) and EF Core (v 2.1)

    Let’s start with the common project using, in my case, VS 2017 15.7.4.  Start a .Net Project based on v 2.1.x (I currently have installed .Net Core SDK v 2.1.301
      (Gee think we have enough versions of things  😯 )

    I’m doing a .Net Core Class so I can get my DBContext and Table models in place….
    We start with some Packages:

    Microsoft.EntityFrameworkCore.SqlServer (2.1.1)
    Microsoft.EntityFrameworkCore.SqlServer.Design (1.1.5)
    Microsoft.EntityFrameworkCore.Tools (2.1.1)

    Next, time for a database and let’s Scaffold a DB Context and our models… On the .Net Core project start up the Package Manager Console.

    dotnet ef dbcontext scaffold 
      "Server={ServerName};DataBase={DataBaseName};Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
       Microsoft.EntityFrameworkCore.SqlServer
       --output-dir Models
       --table {table1Name} --table {table1Name} --table {table1Name} .....
    For Connection Strings to SQL with a user/pass, an example would be
    "Server={ServerName;DataBase={DataBaseName}g;Integrated Security=False; User ID={sql user};Password={sql user Pass};
    Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;
    ApplicationIntent=ReadWrite;MultiSubnetFailover=False"

     

    The Walk-Through…

    Line 1 – dotnet ef dbcontext scaffold This is the start of the command. As we see dotnet is the actual start of the SDK utility. It is worth looking this up. “ef dbcontext scaffold” is how we start  the EF scaffold.
    Line 2 – The Connection String **1
    “Server={ServerName};DataBase={DataBaseName};Integrated Security=True;Connect   Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False”
    Line 3 – This is the provider used for MS SQL server. Yes it would be different if another database was being used.
    Line 4 – The –output param is giving us a way to make sure we put all models/dbcontext in a folder in the project.
    Line 5 – The –table allows one to generate models (and DbContext) for only certain tables in the DB.
     

    **1    This is the standard SQL connection string. This will take on various forms depending on what you are connecting to. And yes, one can use the localdb.

     

     

     

     

     

  • .Net Core,  Microsoft

    .net Core 2.0 and SQL via EF Core 2.0

    So I can never remember the order that these goes in so Here we go:

     A console App with .net Core/SQL Server/EF Core 2.0

    – In VS 2017 – Create a in .Net Core a Console App (.NET CORE)
    -Package Manager Console: 

    Install-Package Microsoft.EntityFrameworkCore.SqlServer  [Current Version was 2.0.1]
    Install-Package Microsoft.EntityFrameworkCore.Tools  [Current Version was 2.0.1]
    Install-Package Microsoft.EntityFrameworkCore.SqlServer.Design  [Not sure this one is required]

    Now let’s say we have a database and table…. Let’s reverse engineer it!

    Scaffold-DbContext “<that magic connect string>” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models
      This is command that will use the <that magic connect string> and the provider Microsoft.EntityFrameworkCore.SqlServer with the code generation going into the Models sub-directory. 

    Examples <that magic connect string>

    For local DB and table: “Server=(localdb)\mssqllocaldb;Database=Blogging;Trusted_Connection=True;” 

    For a real SQL Server connection instance: 

     Scaffold-DbContext "Server=Orion;DataBase=Blogging;Integrated Security=False;
    User ID=sa;Password=****;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;
    ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
    Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

    Also add the table parameter to get only certain tables   -table Blogs, Post
     

    So that was the Hard part… now some sample code:

    Make sure you add using Microsoft.EntityFrameworkCore; And if you put the dbContext into it’s own folder, make sure you include the class namespace.

    
    using System;
    using Microsoft.EntityFrameworkCore;
    using conAppSqlCore.Models;
    
    namespace conAppSqlCore
    {
        class Program
        {
            static void Main(string[] args)
            {
                Console.WriteLine("Hello World!");
                using (var db = new BloggingContext())
                {
                    foreach (var blog in db.Blog)
                    {
                        Console.WriteLine($"Name URL - {blog.Url}");
                    }
                }
                Console.ReadLine();
            }
        }
    }