Issue with scaffolding asp.net razor pages using an entity framework core dbcontext inside DAL class library

[yuvarajvelmurugan]

Steps to reproduce

1. Create a class library for DAL

2. Create an asp.net razor application.

3. Reference the dal project in ui project.

4. Include below references in DAL project file.

DAL csproj file:

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>netstandard2.1</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="3.0.0">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="3.0.0">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="2.2.6" />    
  </ItemGroup>
</Project>

5. Run below command to scaffold entities using database first approach:

dotnet ef dbcontext scaffold "Server=localhost;Port=3306;User ID=root;Password=secure_password;Database=Food;Pooling=true;" "Pomelo.EntityFrameworkCore.MySql" --startup-project ../FoodV2.UI/ -c FoodDbContext --output-dir Models

6. Auto generated entity class file:

using System;
using System.Collections.Generic;

namespace FoodV2.Dal.Models
{
    public partial class TblUnitOfMeasurement
    {
        public sbyte Id { get; set; }
        public string Title { get; set; }
        public string Description { get; set; }
        public string Tag { get; set; }
        public string Unit { get; set; }
        public string Symbol { get; set; }
        public string SystemOfMeasurement { get; set; }
        public bool? IsActive { get; set; }
        public DateTime CreatedOn { get; set; }
        public DateTime? ModifiedOn { get; set; }
    }
}

7. Auto generated dbContext class file:

using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;

namespace FoodV2.Dal.Models
{
    public partial class FoodDbContext : DbContext
    {
        public FoodDbContext()
        {
        }

        public FoodDbContext(DbContextOptions<FoodDbContext> options)
            : base(options)
        {
        }

        public virtual DbSet<TblFood> TblFood { get; set; }
        public virtual DbSet<TblFoodType> TblFoodType { get; set; }
        public virtual DbSet<TblHealthBenefit> TblHealthBenefit { get; set; }
        public virtual DbSet<TblIllness> TblIllness { get; set; }
        public virtual DbSet<TblNutrients> TblNutrients { get; set; }
        public virtual DbSet<TblNutrientsType> TblNutrientsType { get; set; }
        public virtual DbSet<TblSideEffect> TblSideEffect { get; set; }
        public virtual DbSet<TblUnitOfMeasurement> TblUnitOfMeasurement { get; set; }

        // Unable to generate entity type for table 'tblFood2Nutrients'. Please see the warning messages.
        // Unable to generate entity type for table 'tblFoodHealthBenefit'. Please see the warning messages.
        // Unable to generate entity type for table 'tblFoodSideEffect'. Please see the warning messages.
        // Unable to generate entity type for table 'tblFoodToCure'. Please see the warning messages.
        // Unable to generate entity type for table 'tblNutrientsHealthBenefit'. Please see the warning messages.
        // Unable to generate entity type for table 'tblNutrientsSideEffect'. Please see the warning messages.
        // Unable to generate entity type for table 'tblNutrientsToCure'. Please see the warning messages.

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {

            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<TblFood>(entity =>
            {
                entity.ToTable("tblFood");

                entity.HasIndex(e => e.FoodTypeId)
                    .HasName("tblFood_FK");

                entity.Property(e => e.Id)
                    .HasColumnName("ID")
                    .HasColumnType("int(11)");

                entity.Property(e => e.CreatedOn)
                    .HasColumnType("timestamp")
                    .HasDefaultValueSql("'current_timestamp()'")
                    .ValueGeneratedOnAddOrUpdate();

                entity.Property(e => e.Description).HasColumnType("varchar(300)");

                entity.Property(e => e.FoodTypeId)
                    .HasColumnName("FoodTypeID")
                    .HasColumnType("smallint(6)");

                entity.Property(e => e.ImageUrl).HasColumnType("varchar(500)");

                entity.Property(e => e.IsActive)
                    .IsRequired()
                    .HasColumnType("bit(1)")
                    .HasDefaultValueSql("'b\\'1\\''");

                entity.Property(e => e.ModifiedOn).HasColumnType("timestamp");

                entity.Property(e => e.Title)
                    .IsRequired()
                    .HasColumnType("varchar(100)");

                entity.HasOne(d => d.FoodType)
                    .WithMany(p => p.TblFood)
                    .HasForeignKey(d => d.FoodTypeId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("tblFood_FK");
            });

            modelBuilder.Entity<TblFoodType>(entity =>
            {
                entity.ToTable("tblFoodType");

                entity.Property(e => e.Id)
                    .HasColumnName("ID")
                    .HasColumnType("smallint(6)");

                entity.Property(e => e.CreatedOn)
                    .HasColumnType("timestamp")
                    .HasDefaultValueSql("'current_timestamp()'")
                    .ValueGeneratedOnAddOrUpdate();

                entity.Property(e => e.Description).HasColumnType("varchar(300)");

                entity.Property(e => e.ImageUrl).HasColumnType("varchar(150)");

                entity.Property(e => e.IsActive)
                    .IsRequired()
                    .HasColumnType("bit(1)")
                    .HasDefaultValueSql("'b\\'1\\''");

                entity.Property(e => e.ModifiedOn).HasColumnType("timestamp");

                entity.Property(e => e.Title)
                    .IsRequired()
                    .HasColumnType("varchar(50)");
            });

            modelBuilder.Entity<TblHealthBenefit>(entity =>
            {
                entity.ToTable("tblHealthBenefit");

                entity.Property(e => e.Id)
                    .HasColumnName("ID")
                    .HasColumnType("int(11)");

                entity.Property(e => e.CreatedOn)
                    .HasColumnType("timestamp")
                    .HasDefaultValueSql("'current_timestamp()'")
                    .ValueGeneratedOnAddOrUpdate();

                entity.Property(e => e.ImageUrl).HasColumnType("varchar(150)");

                entity.Property(e => e.IsActive)
                    .IsRequired()
                    .HasColumnType("bit(1)")
                    .HasDefaultValueSql("'b\\'1\\''");

                entity.Property(e => e.ModifiedOn).HasColumnType("timestamp");

                entity.Property(e => e.Title)
                    .IsRequired()
                    .HasColumnType("varchar(150)");
            });

            modelBuilder.Entity<TblIllness>(entity =>
            {
                entity.ToTable("tblIllness");

                entity.Property(e => e.Id)
                    .HasColumnName("ID")
                    .HasColumnType("int(11)");

                entity.Property(e => e.CreatedOn)
                    .HasColumnType("timestamp")
                    .HasDefaultValueSql("'current_timestamp()'")
                    .ValueGeneratedOnAddOrUpdate();

                entity.Property(e => e.ImageUrl).HasColumnType("varchar(150)");

                entity.Property(e => e.IsActive)
                    .HasColumnType("bit(1)")
                    .HasDefaultValueSql("'b\\'1\\''");

                entity.Property(e => e.ModifiedOn).HasColumnType("timestamp");

                entity.Property(e => e.Title)
                    .IsRequired()
                    .HasColumnType("varchar(150)");
            });

            modelBuilder.Entity<TblNutrients>(entity =>
            {
                entity.ToTable("tblNutrients");

                entity.HasIndex(e => e.NutrientsTypeId)
                    .HasName("tblNutrients_FK");

                entity.Property(e => e.Id)
                    .HasColumnName("ID")
                    .HasColumnType("smallint(6)");

                entity.Property(e => e.CreatedOn)
                    .HasColumnType("timestamp")
                    .HasDefaultValueSql("'current_timestamp()'")
                    .ValueGeneratedOnAddOrUpdate();

                entity.Property(e => e.Description).HasColumnType("varchar(300)");

                entity.Property(e => e.ImageUrl).HasColumnType("varchar(150)");

                entity.Property(e => e.IsActive)
                    .IsRequired()
                    .HasColumnType("bit(1)")
                    .HasDefaultValueSql("'b\\'1\\''");

                entity.Property(e => e.ModifiedOn).HasColumnType("timestamp");

                entity.Property(e => e.NutrientsTypeId)
                    .HasColumnName("NutrientsTypeID")
                    .HasColumnType("smallint(6)");

                entity.Property(e => e.Title)
                    .IsRequired()
                    .HasColumnType("varchar(50)");

                entity.HasOne(d => d.NutrientsType)
                    .WithMany(p => p.TblNutrients)
                    .HasForeignKey(d => d.NutrientsTypeId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("tblNutrients_FK");
            });

            modelBuilder.Entity<TblNutrientsType>(entity =>
            {
                entity.ToTable("tblNutrientsType");

                entity.Property(e => e.Id)
                    .HasColumnName("ID")
                    .HasColumnType("smallint(6)");

                entity.Property(e => e.CreatedOn)
                    .HasColumnType("timestamp")
                    .HasDefaultValueSql("'current_timestamp()'")
                    .ValueGeneratedOnAddOrUpdate();

                entity.Property(e => e.Description).HasColumnType("varchar(300)");

                entity.Property(e => e.ImageUrl).HasColumnType("varchar(150)");

                entity.Property(e => e.IsActive)
                    .IsRequired()
                    .HasColumnType("bit(1)")
                    .HasDefaultValueSql("'b\\'1\\''");

                entity.Property(e => e.ModifiedOn).HasColumnType("timestamp");

                entity.Property(e => e.Title)
                    .IsRequired()
                    .HasColumnType("varchar(50)");
            });

            modelBuilder.Entity<TblSideEffect>(entity =>
            {
                entity.ToTable("tblSideEffect");

                entity.Property(e => e.Id)
                    .HasColumnName("ID")
                    .HasColumnType("int(11)");

                entity.Property(e => e.CreatedOn)
                    .HasColumnType("timestamp")
                    .HasDefaultValueSql("'current_timestamp()'")
                    .ValueGeneratedOnAddOrUpdate();

                entity.Property(e => e.ImageUrl).HasColumnType("varchar(150)");

                entity.Property(e => e.IsActive)
                    .IsRequired()
                    .HasColumnType("bit(1)")
                    .HasDefaultValueSql("'b\\'1\\''");

                entity.Property(e => e.ModifiedOn).HasColumnType("timestamp");

                entity.Property(e => e.Title)
                    .IsRequired()
                    .HasColumnType("varchar(150)");
            });

            modelBuilder.Entity<TblUnitOfMeasurement>(entity =>
            {
                entity.ToTable("tblUnitOfMeasurement");

                entity.Property(e => e.Id)
                    .HasColumnName("ID")
                    .HasColumnType("tinyint(4)");

                entity.Property(e => e.CreatedOn)
                    .HasColumnType("timestamp")
                    .HasDefaultValueSql("'current_timestamp()'")
                    .ValueGeneratedOnAddOrUpdate();

                entity.Property(e => e.Description)
                    .IsRequired()
                    .HasColumnType("varchar(500)");

                entity.Property(e => e.IsActive)
                    .IsRequired()
                    .HasColumnType("bit(1)")
                    .HasDefaultValueSql("'b\\'1\\''");

                entity.Property(e => e.ModifiedOn).HasColumnType("timestamp");

                entity.Property(e => e.Symbol)
                    .IsRequired()
                    .HasColumnType("varchar(10)");

                entity.Property(e => e.SystemOfMeasurement)
                    .IsRequired()
                    .HasColumnType("varchar(50)");

                entity.Property(e => e.Tag)
                    .IsRequired()
                    .HasColumnType("varchar(100)");

                entity.Property(e => e.Title)
                    .IsRequired()
                    .HasColumnType("varchar(50)");

                entity.Property(e => e.Unit)
                    .IsRequired()
                    .HasColumnType("varchar(50)");
            });
        }
    }
}

8. Include below references in UI project:

<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <TargetFramework>netcoreapp3.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <ProjectReference Include="..\FoodV2.Dal\FoodV2.Dal.csproj" />
  </ItemGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="3.0.0">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="3.0.0">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Design" Version="3.0.0" />
    <PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="2.2.6" />       
  </ItemGroup>

</Project>

9. Include below code in startup.cs file:

public void ConfigureServices(IServiceCollection services)
        {
            services.AddRazorPages();

            services.AddDbContext<Dal.Models.FoodDbContext>(options => options.UseMySql(Configuration.GetConnectionString("MariadbConnection")));
        }

10. Switch to UI project directory in terminal.

11. Run below command on temrinal:

dotnet aspnet-codegenerator razorpage -m TblUnitOfMeasurement -dc ../FoodV2.Dal.Models.FoodDbContext -udl -outDir Areas/Admin/Pages/UnitOfMeasurement -namespace FoodV2.UI.Areas.Admin.Pages.UnitOfMeasurement -scripts

The issue

Unable to scaffold asp.net razor pages with the dbContext.

Exception message:
Stack trace:

dotnet aspnet-codegenerator razorpage -m TblUnitOfMeasurement -dc ../FoodV2.Dal.Models.FoodDbContext -udl -outDir Areas/Admin/Pages/UnitOfMeasurement -namespace FoodV2.UI.Areas.Admin.Pages.UnitOfMeasurement -scripts
Building project ...
Finding the generator 'razorpage'...
Running the generator 'razorpage'...
Sequence contains more than one matching element
   at System.Linq.ThrowHelper.ThrowMoreThanOneMatchException()
   at System.Linq.Enumerable.SingleOrDefault[TSource](IEnumerable`1 source, Func`2 predicate)
   at Microsoft.Extensions.CommandLineUtils.CommandLineApplication.Execute(String[] args)
   at Microsoft.VisualStudio.Web.CodeGeneration.ActionInvoker.Execute(String[] args)
   at Microsoft.VisualStudio.Web.CodeGeneration.CodeGenCommand.Execute(String[] args)
RunTime 00:00:03.25

Further technical details

MySQL version: mariadb Ver 15.1 Distrib 10.4.10-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
Operating system: Ubuntu 18.04 LTS
Pomelo.EntityFrameworkCore.MySql version: 2.2.6 or 3.0.0
Microsoft.AspNetCore.App version: 3.0

Other details about my project setup:

[lauxjpn]

If haven't tested this yet (though I will later today for just the TblUnitOfMeasurement type; though I have to skip the scaffolding step because you did not post the CREATE TABLE script for the underlying table), but the 2.2.6 scaffolded code contains issues, that have been fixed in 3.0.0.

For example the following line in your FoodDbContext class was scaffolded in a faulty way:

.HasDefaultValueSql("'current_timestamp()'")

This was fixed in #896 and should not use single quotes:

.HasDefaultValueSql("current_timestamp()")

There also seems to be a problem with the default value of bit columns because the following code is being generate, which is wrong as well:

.HasDefaultValueSql("'b\\'1\\''");

The code should look like the following line instead:

.HasDefaultValueSql("b'1'");

This time this is likely still an issue in the current 3.0.0 release.

[yuvarajvelmurugan]

If haven't tested this yet (though I will later today for just the TblUnitOfMeasurement type; though I have to skip the scaffolding step because you did not post the CREATE TABLE script for the underlying table), but the 2.2.6 scaffolded code contains issues, that have been fixed in 3.0.0.

For example the following line in your FoodDbContext class was scaffolded in a faulty way:

.HasDefaultValueSql("'current_timestamp()'")

This was fixed in #896 and should not use single quotes:

.HasDefaultValueSql("current_timestamp()")

There also seems to be a problem with the default value of bit columns because the following code is being generate, which is wrong as well:

.HasDefaultValueSql("'b\\'1\\''");

The code should look like the following line instead:

.HasDefaultValueSql("b'1'");

This time this is likely still an issue in the current 3.0.0 release.

CREATE TABLE tblUnitOfMeasurement (
ID tinyint(4) NOT NULL AUTO_INCREMENT,
Title varchar(50) NOT NULL,
Description varchar(500) NOT NULL,
Tag varchar(100) NOT NULL,
Unit varchar(50) NOT NULL,
Symbol varchar(10) NOT NULL,
SystemOfMeasurement varchar(50) NOT NULL,
IsActive bit(1) NOT NULL DEFAULT b'1',
CreatedOn timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
ModifiedOn timestamp NULL DEFAULT NULL,
PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

[lauxjpn]

I could reproduce the issue while testing it yesterday, but the issue also appears in a much simpler scenario with only one project, one entity with one property and even when specifying wrong a wrong model name.

I will have to check this against a SQL Server table first, but this issue might be unrelated to Pomelo.

[yuvarajvelmurugan]

I could reproduce the issue while testing it yesterday, but the issue also appears in a much simpler scenario with only one project, one entity with one property and even when specifying wrong a wrong model name.

I will have to check this against a SQL Server table first, but this issue might be unrelated to Pomelo.

I was also facing issue even when ef core was in one project.

I have also posted this issue in Microsoft asp.net forums.

https://forums.asp.net/p/2161604/6284921.aspx?p=True&t=637098986858738711