locked
.NET Core 2.2 SqlException: Invalid object name 'ApplicationUsers' RRS feed

  • Question

  • User297458589 posted

    I have 2 applications called ManagementStudio and DocumentStudio. I have compiled ManagementStudio into several assemblies that DocumentStudio references.

    I use the same Database for both applications but they are separated by different schemas. E.g. ManagementStudio.ApplicationUsers and DocumentStudio.Documents

    In DocumentStudio, this is my Startup.cs:

    services.AddDbContext<DocumentStudioDbContext>(options => options.UseSqlServer(Environment.GetEnvironmentVariable(DSCASGlobals.DS_ConnectionString)));
    
    string assemblyName = typeof(ManagementStudioDbContext).Namespace;
    services.AddDbContext<ManagementStudioDbContext>(options =>
        options.UseSqlServer(Environment.GetEnvironmentVariable(DSCASGlobals.DS_ConnectionString),
            optionsBuilder =>
                optionsBuilder.MigrationsAssembly(assemblyName)
        )
    );
    services.AddMvc().AddJsonOptions(options =>
    {
        options.SerializerSettings.ContractResolver
            = new Newtonsoft.Json.Serialization.DefaultContractResolver();
    
    });
    
    var lockoutOptions = new LockoutOptions()
    {
        DefaultLockoutTimeSpan = TimeSpan.FromMinutes(5),
        MaxFailedAccessAttempts = 5,
    };
    
    services.AddDefaultIdentity<ApplicationUsers>(options =>
    {
        options.Lockout = lockoutOptions;
    })
      .AddEntityFrameworkStores<ManagementStudioDbContext>();

    DocumentStudio relies on ManagementStudio to login as ManagementStudio contains all the user data and IdentityUser Model.

    This is the login on DocumentStudio which is similar to the ManagementStudio one:

    private readonly SignInManager<ApplicationUsers> _signInManager;
    private readonly UserManager<ApplicationUsers> _userManager;
    private readonly ApplicationUsersData applicationUsersData;
    
    public LoginModel(SignInManager<ApplicationUsers> signInManager, UserManager<ApplicationUsers> userManager, ApplicationUsersData applicationUsersData)
    {
        _signInManager = signInManager;
        _userManager = userManager;
        this.applicationUsersData = applicationUsersData;
    }
    
    public async Task<IActionResult> OnPostAsync(string returnUrl = null){
        var result = await _signInManager.PasswordSignInAsync(Input.UserName, Input.Password, Input.RememberMe, lockoutOnFailure: true);
    }

    However, for some reason, this is the error I get when I try to login:

    enter image description here

    How can I handle this issue?

    Added my ApplicationUsers

    public class ApplicationUsers : IdentityUser
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
        [DataType(DataType.Date)]
        [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
        public DateTime? DateOfBirth { get; set; }
        private DateTime createdOn = DateTime.Now;
        public DateTime CreatedOn
        {
            get
            {
                return (createdOn == DateTime.MinValue) ? DateTime.Now : createdOn;
            }
            set
            {
                createdOn = value;
            }
        }
        private DateTime updatedOn = DateTime.Now;
        public DateTime UpdatedOn
        {
            get
            {
                return (updatedOn == DateTime.MinValue) ? DateTime.Now : updatedOn;
            }
            set
            {
                updatedOn = value;
            }
        }
    }

    Added my DbContexts

    Document Studio

    public DocumentStudioDbContext(DbContextOptions<DocumentStudioDbContext> options) : base(options) { }
    public DbSet<Documents> Documents { get; set; }
    public DbSet<DocumentCategories> DocumentCategories { get; set; }

    ManagementStudio

    public ManagementStudioDbContext(DbContextOptions<ManagementStudioDbContext> options) : base(options){}
    public DbSet<ApplicationUsers> ApplicationUsers { get; set; }
    public DbSet<UserRoles> UserRoles { get; set; }
    Tuesday, June 25, 2019 2:59 PM

Answers

  • User-1764593085 posted

    Hi StardocsSVC,

    If you use asp.net core Identity(individual user account), although you extend the IdentityUser to your ApplicationUsers, it will still map to the AspNetUser Table and add your custom fields into the table.

    If you would like to specify a schema that the table belongs to, try to use below code in dbcontext

    protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                base.OnModelCreating(modelBuilder);
                modelBuilder.Entity<ApplicationUsers>()
                    .ToTable("ApplicationUsers", "ManagementStudio");
            }

    Xing

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 26, 2019 3:12 AM

All replies

  • User753101303 posted

    Hi,

    You are using https://docs.microsoft.com/en-us/ef/core/modeling/relational/tables somewhere to tell which schema is used ?

    Tuesday, June 25, 2019 3:13 PM
  • User475983607 posted

    You can implement multiple DbContexts but one DbContext might be easier in the long run.

    Use table mapping to map the Entity to the schema as illustrated in the follow EF Core doc.

    https://docs.microsoft.com/en-us/ef/core/modeling/relational/tables

    To configure Identity to use a schema, see the following Core Identity doc.

    https://docs.microsoft.com/en-us/aspnet/core/security/authentication/customize-identity-model?view=aspnetcore-2.2#map-to-a-different-schema

    Tuesday, June 25, 2019 3:15 PM
  • User297458589 posted

    But if I reference the ManagementStudio assembly, won't I need to reference its dbcontext if I want to login? 

    Tuesday, June 25, 2019 3:21 PM
  • User297458589 posted

    My schema is set in the onModelBuilding

    Tuesday, June 25, 2019 3:22 PM
  • User475983607 posted

    My schema is set in the onModelBuilding

    Please post all the relevant bits of configuration and code so we are not going over troubleshooting steps that have already been checked off the list.   I assume you also scaffolded Identity?

    Tuesday, June 25, 2019 3:46 PM
  • User753101303 posted

    The error message should include this schema name then so it's likely this part that doesn't work. You inherit directly from DbContext or from another DbContext based type? In this later case you are using HasDefaultSchema BEFORE calling base.OnModelBuilding (and the base context itself doesn't call HasDefaultSchema("dbo") ?

    Tuesday, June 25, 2019 4:27 PM
  • User297458589 posted

    This is my ApplicationUsers that is located in ManagementStudio

    public class ApplicationUsers : IdentityUser
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
        [DataType(DataType.Date)]
        [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
        public DateTime? DateOfBirth { get; set; }
        private DateTime createdOn = DateTime.Now;
        public DateTime CreatedOn
        {
            get
            {
                return (createdOn == DateTime.MinValue) ? DateTime.Now : createdOn;
            }
            set
            {
                createdOn = value;
            }
        }
        private DateTime updatedOn = DateTime.Now;
        public DateTime UpdatedOn
        {
            get
            {
                return (updatedOn == DateTime.MinValue) ? DateTime.Now : updatedOn;
            }
            set
            {
                updatedOn = value;
            }
        }
    }

    These are my DbContexts

    Document Studio

    public DocumentStudioDbContext(DbContextOptions<DocumentStudioDbContext> options) : base(options) { }
    public DbSet<Documents> Documents { get; set; }
    public DbSet<DocumentCategories> DocumentCategories { get; set; }

    ManagementStudio

    public ManagementStudioDbContext(DbContextOptions<ManagementStudioDbContext> options) : base(options){}
    public DbSet<ApplicationUsers> ApplicationUsers { get; set; }
    public DbSet<UserRoles> UserRoles { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
    modelBuilder.HasDefaultSchema(schema: Environment.GetEnvironmentVariable(DSCASGlobals.DS_DatabaseSchema));
    base.OnModelCreating(modelBuilder);
    }

    I also suspect its because DocumentStudio is looking for a its own ApplicationUser. ApplicationUser only makes sense to ManagementStudio. Maybe the solution is to override the passwordsigninasync and have one that calls ManagementStudios data.

    Its strange that its not asking for ManagementStudio.ApplicationUser. Just ApplicationUser.

    Wednesday, June 26, 2019 12:59 AM
  • User-1764593085 posted

    Hi StardocsSVC,

    If you use asp.net core Identity(individual user account), although you extend the IdentityUser to your ApplicationUsers, it will still map to the AspNetUser Table and add your custom fields into the table.

    If you would like to specify a schema that the table belongs to, try to use below code in dbcontext

    protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                base.OnModelCreating(modelBuilder);
                modelBuilder.Entity<ApplicationUsers>()
                    .ToTable("ApplicationUsers", "ManagementStudio");
            }

    Xing

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 26, 2019 3:12 AM
  • User297458589 posted

    Hi Xing,

    Thank you, I had to manually set the schema for each table for it to work. However, I'm getting a new error when I'm trying to login. I have posted a new question as its quite different from this. 

    Thank you for your help.

    Wednesday, June 26, 2019 4:48 AM