How connect to an existing Azure SQL database with ASP.NET CORE MVC using Entity Framework Core with migrations?

Question

I have an app using Azure App Services with SQL server and SQL database that are connect to my web app on asp MVC. I've used Distributed Sql Server Cache as a table on my database and so far everything is working well and connected to each other. Now I want to do two things:

• Add entity framework to my app (I already have the database and connection string)
• Run migration – after I've published my app (If I've added for a example new line or new

table, now I have new version) I'm not sure how to do those things , I've looked up on many guides and couldn't find an answer. I found a post similar to mine – but using azure functions - here . I would appreciate it if someone can help me with the steps that I need to follow (like they did in that post) to get entity framework and the migration.

Here is my code:

Program.cs-

using Microsoft.Extensions.Azure;
using Azure.Identity;
var builder = WebApplication.CreateBuilder(args);

if(!builder.Environment.IsDevelopment())
    builder.Configuration.AddAzureKeyVault(new Uri(Environment.GetEnvironmentVariable("VaultUri")), new DefaultAzureCredential());

builder.Services.AddControllersWithViews();
builder.Services.AddAzureClients(clientBuilder =>
{
    clientBuilder.AddBlobServiceClient(builder.Configuration["storage:blob"], preferMsi: true);
    clientBuilder.AddQueueServiceClient(builder.Configuration["storage:queue"], preferMsi: true);
});

builder.Services.AddDistributedSqlServerCache(options =>
{
    options.ConnectionString = builder.Configuration.GetConnectionString("db");
    options.SchemaName = "dbo";
    options.TableName = "_Cache";
});

var app = builder.Build();

// Configure the HTTP request pipeline.
if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/Home/Error");
    // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
    app.UseHsts();

Home Controller:

namespace WebAppAzure.Controllers
{
    public class HomeController : Controller
    {
        private readonly BlobServiceClient storage;
        private readonly ILogger<HomeController> logger;
        private readonly IDistributedCache cache;

        public HomeController(BlobServiceClient storage, ILogger<HomeController> logger,
            IDistributedCache cache)
        {
            this.storage = storage;
            this.logger = logger;
            this.cache = cache;
        }

        public IActionResult Index()
        {
            var containerClient = storage.GetBlobContainerClient("public");
            var blob = containerClient.GetBlobClient("image.jpeg");
            var model = blob.Uri.ToString();

            return View(model: model);
        }

        public IActionResult Privacy()
        {
            var stringModel = DateTime.Now.ToString();
            cache.SetString("name", stringModel);
            return View(model: $"SET: {stringModel}");
        }

        public IActionResult About()
        {
            var stringModel = cache.GetString("name");
            return View(model: $"GET: {stringModel}");
        }


        [ResponseCache(Duration = 0, Location = ResponseCacheLocation.None, NoStore = true)]
        public IActionResult Error()
        {
            return View(new ErrorViewModel { RequestId = Activity.Current?.Id ?? HttpContext.TraceIdentifier });
        }
    }
}

Answer 1

• Add entity framework to my app (I already have the database and connection string)

Use below code for add Entity framework and upload to azure app service and run migration command to migrate database.

DBcontext file in project.

using Microsoft.EntityFrameworkCore;
using WebApplication_72783922.Entity;

namespace WebApplication_72783922
{
    public class DbConnectionEntity : DbContext
    {
        public DbConnectionEntity()
        {

        }
        //string connectionString = Environment.GetEnvironmentVariable("ConnectionStrings:dbcon").ToString();
        public DbConnectionEntity(DbContextOptions<DbConnectionEntity> options) 
            : base(options)
        {

        }
        public virtual DbSet<Users> users { get; set; }
        public virtual DbSet<department> Departments { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer("Server=xxxx;Initial Catalog=database;Persist Security Info=False;User ID=adminserver72783922;Password=xxxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;");
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
        }
    }
}

Program.cs File code.

using Microsoft.Extensions.Azure;
using Azure.Identity;
using Microsoft.Extensions.Configuration;

var builder = WebApplication.CreateBuilder(args);

if (!builder.Environment.IsDevelopment())
     
// Add services to the container.
builder.Services.AddControllersWithViews();

builder.Services.AddDistributedSqlServerCache(options =>
{
    options.ConnectionString = "Server=xxxx;Initial Catalog=database;Persist Security Info=False;User ID=adminserver72783922;Password=xxxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
    options.SchemaName = "dbo";
    options.TableName = "_Cache";
});

var app = builder.Build();

// Configure the HTTP request pipeline.
if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/Home/Error");
    // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
    app.UseHsts();
}

app.UseHttpsRedirection();
app.UseStaticFiles();

app.UseRouting();

app.UseAuthorization();

app.MapControllerRoute(
    name: "default",
    pattern: "{controller=Home}/{action=Index}/{id?}");

app.Run();

• Run migration – after I’ve published my app

Enable Migration using this command on Package Manager Console enable-migrations
Then add-migration InitialCreate
Then create migrationadd-migration test-v1
update database update-database -verbose