How to Connect existing database in Dot Net 7?

Question

I want to connect existing database in dot net 7 project using code first approach .The steps which i followed is mentioned below.

1)First i added some of the package files through NuGet

Then i written the connection string in appsettings.json file

      {
        "Logging": {
        "LogLevel": {
        "Default": "Information",
        "Microsoft.AspNetCore": "Warning"
      }
  },
    "AllowedHosts": "*",
    "ConnectionStrings": {
    "DefaultConnection": "Data Source=DESKTOP-TK34BGD\\SQLEXPRESS;Initial Catalog=BookMgmt;uid=sa;password=sql;Integrated security=true;Encrypt=true;TrustServerCertificate=true;"
  }
  }

2)Then i did the CORS and database configuration in program.cs file

  using AspNetCoreWebApi7.Data;
  using Microsoft.EntityFrameworkCore;
  var builder = WebApplication.CreateBuilder(args);

  #region Configuration CORS
  builder.Services.AddCors(options =>
  {
       options.AddPolicy("CustomPolicy", x => 
   x.AllowAnyOrigin().AllowAnyHeader().AllowAnyMethod());
  });
  #endregion

  #region Configure Database
  var ConnectionString = builder.Configuration.GetConnectionString("DefaultConnection");
  builder.Services.AddDbContext<ApplicationDbContext>(options => options.UseSqlServer(ConnectionString));
  #endregion

  builder.Services.AddControllers();
  builder.Services.AddEndpointsApiExplorer();
  builder.Services.AddSwaggerGen();

  var app = builder.Build();

  if (app.Environment.IsDevelopment())
   {
       app.UseSwagger();
       app.UseSwaggerUI();
   }

   app.UseHttpsRedirection();
   app.UseCors("CustomPolicy");
   app.UseAuthorization();
   app.MapControllers();
   app.Run();

3. Then I created Data folder and added one class file called ApplicationDbContext

Inside ApplicationDbContext file

  using Microsoft.EntityFrameworkCore;
  using AspNetCoreWebApi7.Models;

  namespace AspNetCoreWebApi7.Data
   {
      public class ApplicationDbContext: DbContext
      {
         public ApplicationDbContext(DbContextOptions<ApplicationDbContext>options) : base(options)
          { }
         public DbSet<AuthorModel> Authorities { get; set; }
     }
   }

These are the steps I followed to connect the existing database. But i am getting error while fetching author list from database.

Table

My Model

My Controller code

using AspNetCoreWebApi7.Data;
using AspNetCoreWebApi7.Models;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Mvc;

namespace AspNetCoreWebApi7.Controllers
 {
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
      {
         private readonly ApplicationDbContext _db;
         public AuthorController(ApplicationDbContext db)
          {
             this._db = db;
          }

        [HttpGet]
        public ActionResult<IEnumerable<AuthorModel>> GetAll()
        {
             return _db.Authorities.ToList();
        }
     }
   }

The error is invalid object name Authorities.Did i missed anything in database connectivity.Please help me to resolve this issue.Thanks

Answer 1

Decorate Model class with data Anotatio's Table as you see table having column Name UID but in class AuthorUID. change code as follows

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

[Table("Author")]
public class Author
{
    [Key]
    [Column("UID")]
    public string AuthorUID { get; set; }
    public string AuthorFirstName { get; set; }
    public string AuthorLastName { get; set; }
    public string PublishName { get; set; }
    public string PublishID { get; set; }
}

change in ApplicationDbContext

public DbSet<Author> Authorities { get; set; }