Entity Framework not creating tables with RDS DB

0

I am trying to use Entity Framework code first to automatically create my tables using the dbcontext. As far as I am aware code first should initialise the database the first time EF needs to access it. Although when I use swagger to create a User object I get an error:

500 Error: Response body
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.

Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid object name 'Users'

My database is up and running and available to view in Server Object Explorer:

enter image description here

UserContext:

namespace FitnessTracker.Models
{
    public class UserContext : DbContext
    {
        public UserContext(DbContextOptions<UserContext> options)
                   : base(options)
        {
        }

        public DbSet<UserData> Users { get; set; }
        public DbSet<WorkoutData> Workouts { get; set; }
    }
}

Model:

namespace FitnessTracker
{
    //holds all user information
    public class UserData
    {
        //Upper bounds for BMI categories
        const double SeverelyUnderweightUpper = 15.9;
        const double UnderweightUpper = 18.4; 
        const double NormalWeightUpper = 24.9;
        const double OverweightUpper = 29.9;
        const double ModeratelyObeseUpper = 34.9;

        public int ID { get; set; }
        [Required(ErrorMessage = "First Name is required")]
        [Display(Name = "First Name")]
        [StringLength(20, ErrorMessage = "First name cannot exceed 20 characters. ")]
        public string FirstName { get; set; }
        [Required(ErrorMessage = "Second Name is required")]
        [Display(Name = "Second Name")]
        [StringLength(20, ErrorMessage = "Second name cannot exceed 20 characters. ")]
        public string SecondName { get; set; }
        public string Gender { get; set; }
        [Range(5, 110, ErrorMessage = "Please enter a valid age. ")]
        public int Age { get; set; }
        [Display(Name = "Weight (KG)")]
        [Range(5, 150, ErrorMessage = "KG must be between 5 and 150")]
        public double WeightKG { get; set; }
        [Display(Name = "Height (CM)")]
        [Range(5, 220, ErrorMessage = "Height must be between 5 and 220 CM")]
        public int HeightCM { get; set; }

        //Users list of personal workouts
        public List<WorkoutData> Workouts { get; set; }
    }

    //holds all workout information such as start/end times, workout details, calories burned etc.
    public class WorkoutData
    {
        public int ID { get; set; }
        [Required(ErrorMessage = "Date of workout is required")]
        public DateTime Date { get; set; }
        [Display(Name = "Workout Length")]
        public double WorkoutDuration { get; set; }
        [Display(Name = "Workout Details")]
        public string WorkoutDetails { get; set; }
        [Display(Name = "Calories Burned")]
        public int CaloriesBurned { get; set; }
        public UserData User { get; set; }
        //Foreign Key for Entity Framework
        public int UserID { get; set; }
    }
}

Connection string in appsettings.json:

"ConnectionStrings": {
    "UserContext": "Data Source=fitnessapi.clxnbxbuoxx1.eu-west-1.rds.amazonaws.com,1433;User ID=XXXXXX;Password=XXXXXX; Initial Catalog=fitnessapi; Database=fitnessapi; Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
}

I have also added the following to Startup.cs:

services.AddDbContext<UserContext>(options =>
      options.UseSqlServer(Configuration.GetConnectionString("UserContext")));

Can anyone see what is wrong?

c#
asp.net
amazon-web-services
asp.net-web-api
asked on Stack Overflow Apr 3, 2020 by craig2020 • edited Apr 3, 2020 by marc_s

1 Answer

0

First, you need to create migration file with your data context

Visual StudioConsole: add-migration migration_name

More about migrations here

Then, you need to create a class DatabaseInitializer, which will initialize your db when server is started (this will check for new migrations and will apply it)

    public class DatabaseInitializer
    {
        private DataContext context;

        public DatabaseInitializer(DataContext context)
        {
            this.context = context;
        }

        public async Task Initialize()
        {
            context.Database.Migrate();

            await context.SaveChangesAsync();
        }
    }

and the last, in your Program.cs (Main method) call the method to initialize db

    using (var scope = host.Services.CreateScope())
    {
         var services = scope.ServiceProvider;

         try
         {
              var dbInitializer = services.GetService<DatabaseInitializer>();
              dbInitializer.Initialize().Wait();
        }
        catch (Exception ex)
        {
            // log(ex, "An error occurred seeding the DB.");
        }
   }
answered on Stack Overflow Apr 3, 2020 by AlleXyS • edited Apr 3, 2020 by AlleXyS

User contributions licensed under CC BY-SA 3.0