locked
Tables will not be generated but database does. What am I do wrong? Plus get this error: SqlException: Invalid object name 'Products' RRS feed

  • Question

  • User-1880744392 posted

    Here are the classes I think are related to this issue.

    There error is: SqlException: Invalid object name 'Products'

    and it shows the product controller line: return View(await _context.Products.ToListAsync());

    But yeah basically there is no table being generated and i am pretty sure this is causing the problem, I used alot of the code from the new microsoft docs from ASP Pages

    so I think it is possibly causing the error cus it may suit ASP pages only and not views?

    Prodocts

        public class Product
        {
            // Primary Key
            public int ProductID { get; set; }
    
            // Foreign Key
            public int PersonalTrainingId { get; set; }
    
            // Navigation Properties
            public PersonalTraining PersonalTrainingSessions { get; set; }
            public  ICollection<Supplement> Supplements { get; set; }
            public ICollection<CustomizedRoutine> CustomizedRoutines { get; set; }
            public ICollection<Diet> Diets { get; set; }
            public ICollection<TrainingEquipment> TrainingEquipments { get; set; }
    
    
            // Product Details
            public string Title { get; set; }
            public string Description { get; set; }
            public decimal Price { get; set; }
            public decimal Quantity { get; set; }
            public Availability HasStock { get; set; }
    
            public enum Availability
            {
                Available,
                Unavailable,
                ComingSoon
            }
    
            // TO use later for Identity User
            public string Person { get; set; }
            public string Administrator { get; set; }
    
    
        }

    ProductContext

    public class ProductDbContext : DbContext
        {
            public ProductDbContext(DbContextOptions<ProductDbContext> options)
            : base(options)
            {
                //Database.EnsureCreated();
            }
    
            public DbSet<Product> Products { get; set; }
            public DbSet<CustomizedRoutine> CustomizedRoutines { get; set; }
            public DbSet<Diet> Diets { get; set; }
            public DbSet<Supplement> Supplements { get; set; }
            public DbSet<TrainingEquipment> TrainingEquipments { get; set; }
           // public DbSet<SupplementRoutine> SupplementRoutines { get; set; }
           
            protected override void OnModelCreating(ModelBuilder builder)
            {
                base.OnModelCreating(builder);
    
                // Define composite key.
                //builder.Entity<Products>()
                //    .HasKey(p => new { p.ProductID});
    
                builder.Entity<Product>().ToTable("Product");
                builder.Entity<CustomizedRoutine>().ToTable("CustomizedRoutine");
                builder.Entity<Supplement>().ToTable("Supplement");
                builder.Entity<Diet>().ToTable("Diet");
                builder.Entity<PersonalTraining>().ToTable("PersonalTraiing");
                builder.Entity<TrainingEquipment>().ToTable("TrainingEquipment");
    
                // Fluent API to define Entity Relationships one-to-many
                builder.Entity<Product>()
                    .HasMany<Supplement>(s => s.Supplements)
                    .WithOne(p => p.Product)
                    .HasForeignKey(s => s.ProductID)
                    .OnDelete(DeleteBehavior.SetNull);
    
                builder.Entity<Product>()
                    .HasMany<CustomizedRoutine>(c => c.CustomizedRoutines)
                    .WithOne(p => p.Product)
                    .HasForeignKey(c => c.ProductID)
                    .OnDelete(DeleteBehavior.SetNull);
    
    
                builder.Entity<Product>()
                    .HasMany<Diet>(d => d.Diets)
                    .WithOne(p => p.Product)
                    .HasForeignKey(d => d.ProductID)
                    .OnDelete(DeleteBehavior.SetNull);
    
    
                builder.Entity<Product>()
                    .HasMany<TrainingEquipment>(e => e.TrainingEquipments)
                    .WithOne(p => p.Product)
                    .HasForeignKey(s => s.ProductID)
                    .OnDelete(DeleteBehavior.SetNull);
    
                builder.Entity<PersonalTraining>()
                    .HasMany<Product>(p => p.Products)
                    .WithOne(pt => pt.PersonalTrainingSessions)
                    .HasForeignKey(p => p.PersonalTrainingId)
                    .OnDelete(DeleteBehavior.SetNull);
    
                // Many-to-Many Relationships definition
                builder.Entity<SupplementRoutine>()
                    .HasKey(sr => new { sr.SupplementId, sr.CustomizedRoutineId });
                builder.Entity<SupplementRoutine>()
                    .HasOne(sr => sr.Supplement)
                    .WithMany(s => s.CustomizedRoutines)
                    .HasForeignKey(sr => sr.SupplementId);
                builder.Entity<SupplementRoutine>()
                    .HasOne(sr => sr.CustomizedRoutine)
                    .WithMany(c => c.Supplements)
                    .HasForeignKey(sr => sr.CustomizedRoutineId);
            }
        }

    appsettings

    {
      "Logging": {
        "LogLevel": {
          "Default": "Debug",
          "System": "Information",
          "Microsoft": "Information"
        }
      },
      "ConnectionStrings": {
        "ProductDbContext": "Server=(localdb)\\mssqllocaldb;Database=TroydonFitness;Trusted_Connection=True;MultipleActiveResultSets=true"
      }
    }
    

    program

        public class Program
        {
            public static void Main(string[] args)
            {
                var host = CreateHostBuilder(args).Build();
    
                CreateDbIfNotExists(host);
    
                host.Run();
            }
    
            private static void CreateDbIfNotExists(IHost host)
            {
                using (var scope = host.Services.CreateScope())
                {
                    var services = scope.ServiceProvider;
    
                    try
                    {
                        var context = services.GetRequiredService<ProductDbContext>();
                        context.Database.EnsureCreated();
                    }
                    catch (Exception ex)
                    {
                        var logger = services.GetRequiredService<ILogger<Program>>();
                        logger.LogError(ex, "An error occurred creating the DB.");
                    }
                }
            }
    
            public static IHostBuilder CreateHostBuilder(string[] args) =>
                Host.CreateDefaultBuilder(args)
                    .ConfigureWebHostDefaults(webBuilder =>
                    {
                        webBuilder.UseStartup<Startup>();
                    });
        }

    Startup.cs

    public class Startup
        {
            public Startup(IConfiguration configuration)
            {
                Configuration = configuration;
    
            }
    
            public IConfiguration Configuration { get; }
            // This method gets called by the runtime. Use this method to add services to the container.
            // For more information on how to configure your application, visit https://go.microsoft.com/fwlink/?LinkID=398940
            public void ConfigureServices(IServiceCollection services)
            {
    
                services.AddDbContext<ProductDbContext>(options =>
                {
                    var connectionString = Configuration.GetConnectionString("ProductDbContext");
                    options.UseSqlServer(connectionString);
                });
    
                services.AddRazorPages();
                services.AddControllersWithViews();
            }
    
            // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
            public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
            {
                if (env.IsDevelopment())
                {
                    app.UseDeveloperExceptionPage();
                }
                else
                {
                    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.UseCors();
    
                var cookiePolicyOptions = new CookiePolicyOptions
                {
                    MinimumSameSitePolicy = SameSiteMode.Strict,
                };
    
                app.UseCookiePolicy(cookiePolicyOptions);
    
                app.UseAuthentication();
                app.UseAuthorization();
    
                app.UseEndpoints(endpoints =>
                {
                    endpoints.MapRazorPages();
                    endpoints.MapControllerRoute("default", "{controller=Home}/{action=Index}/{id?}");
                });
            }
        }

    Sorry about probably adding too much code, I have also attached my Entity Relationship Diagram.EF-Troydon

    https://photos.app.goo.gl/1FdTZVyU8BzBQekc8

    Sunday, October 20, 2019 2:13 AM

Answers

  • User-719153870 posted

    Hi TroydonAnabolic,

    The reason for no table generated is that your try-catch block throw exception. So I suggest that you could set breakpoint to check the error.

    In EF Core,you could define the relationship like below:

    public class Product
        {
            public int ProductID { get; set; }
    
            // Foreign Key
            //public int PersonalTrainingId { get; set; }
            // Navigation Properties
            public PersonalTraining PersonalTrainingSessions { get; set; }
            public ICollection<Supplement> Supplements { get; set; }
            public ICollection<CustomizedRoutine> CustomizedRoutines { get; set; }
            public ICollection<Diet> Diets { get; set; }
            public ICollection<TrainingEquipment> TrainingEquipments { get; set; }
            // Product Details
            public string Title { get; set; }
            public string Description { get; set; }
            public decimal Price { get; set; }
            public decimal Quantity { get; set; }
            public Availability HasStock { get; set; }
            public enum Availability
            {
                Available,
                Unavailable,
                ComingSoon
            }
    
            // TO use later for Identity User
            public string Person { get; set; }
            public string Administrator { get; set; }
        }
    public class Supplement
        {
            public int Id { get; set; }
            public Product Product { get; set; }
            public List<SupplementRoutine> SupplementRoutine { get; set; }
             // other Details
    }
    public class CustomizedRoutine
        {
            public int Id { get; set; }
            public Product Product { get; set; }
            public List<SupplementRoutine> SupplementRoutine { get; set; }
            // other Details
    }
    public class Diet
        {
            public int Id { get; set; }
            public Product Product { get; set; }
    // other Details
    }
    public class PersonalTraining
        {
            public int Id { get; set; }
            public List<Product> Products { get; set; }
           // other Details
    }
    public class SupplementRoutine
        {
            public int SupplementId { get; set; }
            public int CustomizedRoutineId { get; set; }
            public Supplement Supplement { get; set; }
            public CustomizedRoutine CustomizedRoutine { get; set; }
    // other Details
    }
    public class TrainingEquipment
        {
            public int Id { get; set; }
            public Product Product { get; set; }
    // other Details
    }
    

    Your DbContext should be like below:

    public class ProductDbContext: DbContext
        {
            public ProductDbContext(DbContextOptions<ProductDbContext> options)
            : base(options)
            {}
            public DbSet<Product> Products { get; set; }
            public DbSet<CustomizedRoutine> CustomizedRoutines { get; set; }
            public DbSet<Diet> Diets { get; set; }
            public DbSet<Supplement> Supplements { get; set; }
            public DbSet<TrainingEquipment> TrainingEquipments { get; set; }
            // public DbSet<SupplementRoutine> SupplementRoutines { get; set; }
    
            protected override void OnModelCreating(ModelBuilder builder)
            {
                builder.Entity<Product>().ToTable("Product");
                builder.Entity<CustomizedRoutine>().ToTable("CustomizedRoutine");
                builder.Entity<Supplement>().ToTable("Supplement");
                builder.Entity<Diet>().ToTable("Diet");
                builder.Entity<PersonalTraining>().ToTable("PersonalTraiing");
                builder.Entity<TrainingEquipment>().ToTable("TrainingEquipment");
    
                builder.Entity<SupplementRoutine>()
                    .HasKey(sr => new { sr.SupplementId, sr.CustomizedRoutineId });
                builder.Entity<SupplementRoutine>()
                    .HasOne(sr => sr.Supplement)
                    .WithMany(s => s.SupplementRoutine)
                    .HasForeignKey(sr => sr.SupplementId);
                builder.Entity<SupplementRoutine>()
                    .HasOne(sr => sr.CustomizedRoutine)
                    .WithMany(c => c.SupplementRoutine)
                    .HasForeignKey(sr => sr.CustomizedRoutineId);
            }
    }
    

    Refenrence: Many-to-many.

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 21, 2019 7:58 AM

All replies

  • User-719153870 posted

    Hi TroydonAnabolic,

    The reason for no table generated is that your try-catch block throw exception. So I suggest that you could set breakpoint to check the error.

    In EF Core,you could define the relationship like below:

    public class Product
        {
            public int ProductID { get; set; }
    
            // Foreign Key
            //public int PersonalTrainingId { get; set; }
            // Navigation Properties
            public PersonalTraining PersonalTrainingSessions { get; set; }
            public ICollection<Supplement> Supplements { get; set; }
            public ICollection<CustomizedRoutine> CustomizedRoutines { get; set; }
            public ICollection<Diet> Diets { get; set; }
            public ICollection<TrainingEquipment> TrainingEquipments { get; set; }
            // Product Details
            public string Title { get; set; }
            public string Description { get; set; }
            public decimal Price { get; set; }
            public decimal Quantity { get; set; }
            public Availability HasStock { get; set; }
            public enum Availability
            {
                Available,
                Unavailable,
                ComingSoon
            }
    
            // TO use later for Identity User
            public string Person { get; set; }
            public string Administrator { get; set; }
        }
    public class Supplement
        {
            public int Id { get; set; }
            public Product Product { get; set; }
            public List<SupplementRoutine> SupplementRoutine { get; set; }
             // other Details
    }
    public class CustomizedRoutine
        {
            public int Id { get; set; }
            public Product Product { get; set; }
            public List<SupplementRoutine> SupplementRoutine { get; set; }
            // other Details
    }
    public class Diet
        {
            public int Id { get; set; }
            public Product Product { get; set; }
    // other Details
    }
    public class PersonalTraining
        {
            public int Id { get; set; }
            public List<Product> Products { get; set; }
           // other Details
    }
    public class SupplementRoutine
        {
            public int SupplementId { get; set; }
            public int CustomizedRoutineId { get; set; }
            public Supplement Supplement { get; set; }
            public CustomizedRoutine CustomizedRoutine { get; set; }
    // other Details
    }
    public class TrainingEquipment
        {
            public int Id { get; set; }
            public Product Product { get; set; }
    // other Details
    }
    

    Your DbContext should be like below:

    public class ProductDbContext: DbContext
        {
            public ProductDbContext(DbContextOptions<ProductDbContext> options)
            : base(options)
            {}
            public DbSet<Product> Products { get; set; }
            public DbSet<CustomizedRoutine> CustomizedRoutines { get; set; }
            public DbSet<Diet> Diets { get; set; }
            public DbSet<Supplement> Supplements { get; set; }
            public DbSet<TrainingEquipment> TrainingEquipments { get; set; }
            // public DbSet<SupplementRoutine> SupplementRoutines { get; set; }
    
            protected override void OnModelCreating(ModelBuilder builder)
            {
                builder.Entity<Product>().ToTable("Product");
                builder.Entity<CustomizedRoutine>().ToTable("CustomizedRoutine");
                builder.Entity<Supplement>().ToTable("Supplement");
                builder.Entity<Diet>().ToTable("Diet");
                builder.Entity<PersonalTraining>().ToTable("PersonalTraiing");
                builder.Entity<TrainingEquipment>().ToTable("TrainingEquipment");
    
                builder.Entity<SupplementRoutine>()
                    .HasKey(sr => new { sr.SupplementId, sr.CustomizedRoutineId });
                builder.Entity<SupplementRoutine>()
                    .HasOne(sr => sr.Supplement)
                    .WithMany(s => s.SupplementRoutine)
                    .HasForeignKey(sr => sr.SupplementId);
                builder.Entity<SupplementRoutine>()
                    .HasOne(sr => sr.CustomizedRoutine)
                    .WithMany(c => c.SupplementRoutine)
                    .HasForeignKey(sr => sr.CustomizedRoutineId);
            }
    }
    

    Refenrence: Many-to-many.

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 21, 2019 7:58 AM
  • User-1880744392 posted

    Thanks Yang! It looked like it solved my problem, are you referring to the try catch in my Program.cs ?

    It seems the main changes were the removal of the IDs and removal which has fixed the issue.

    Monday, October 21, 2019 10:25 PM
  • User-719153870 posted

    Hi TroydonAnabolic,

    Due to your DbContext is not correct, it would throw exception when you debug to `context.Database.EnsureCreated();` and your table could not create successfully.

    If your issue has been resolved,could you please mark my answer?It would also be helpful to others.

    Best Regard,

    Yang Shen

    Tuesday, October 22, 2019 1:29 AM