locked
MultiContext DB with EntityFramework Core 3 Two Connection string RRS feed

  • Question

  • User433211055 posted
    I'm creating a solution with multiple projects ...

    In a project I manage users with identity and a database that I would like to be isolated.

    All other projects share the identity db only to log in and verify the user role.

    The individual projects have two connection strings, one to db-identity and one for specific db.

    In my model there are relations between the two db ... for example: I assign a badge to an user the badge information is in a db-badge and the user info are in the db-identity.

    I need related in model for specific project table in the two db.

    My model

    public class Assignment
    {
    public int Id { get; set; }
    public int IdUser { get; set; }
    public User User { get; set; }
    public int IdBadge { get; set; }
    public Badge Badge { get; set; }
    public DateTime DataStart { get; set; }
    public DateTime DataEnd { get; set; }
    public string Note { get; set; }
    }

    public class User : AppUser
    {
    public ICollection<Assignment> Assignments { get; set; }
    }



    public class Badge
    {
    public int Id { get; set; }
    public int Number { get; set; }
    public string Description { get; set; }
    public bool IsActive { get; set; }
    public ICollection<Assignment> Assignments { get; set; }
    }
    Context for identity

    public class ApplicationDbContext : IdentityDbContext<AppUser, AppRole, int>
    {
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
    : base(options)
    {
    }

    public DbSet<User> users { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
    base.OnModelCreating(modelBuilder);
    // Customize the ASP.NET Identity model and override the defaults if needed.
    // For example, you can rename the ASP.NET Identity table names and more.
    // Add your customizations after calling base.OnModelCreating(builder);

    modelBuilder.Entity<User>().ToTable("AspNetUsers");
    }

    // public DbSet<User> users { get; set; }

    //protected override void OnModelCreating(ModelBuilder modelBuilder)
    //{
    // modelBuilder.Entity<User>().ToTable("AspNetUsers");
    //}
    }
    Context for project

    public class ScaDbContext : DbContext
    {
    // private readonly IConfiguration config;

    public ScaDbContext(DbContextOptions<ScaDbContext> options) : base(options)
    {

    }

    // public DbSet<User> users { get; set; }
    public DbSet<Badge> badges { get; set; }
    public DbSet<Assignment> assignments { get; set; }


    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {

    // base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<Badge>().ToTable("Badge", "Yogi");

    modelBuilder.Entity<Assignment>()
    .HasKey(b => new { b.IdBadge, b.IdUser });

    modelBuilder.Entity<Assignment>()
    .HasOne(bd => bd.Badge)
    .WithMany(a => a.Assignments)
    .HasForeignKey(bd => bd.IdBadge);

    modelBuilder.Entity<Assignment>()
    .HasOne(u => u.User)
    .WithMany(a => a.Assignments)
    .HasForeignKey(u => u.IdUser);

    modelBuilder.Entity<Assignment>().ToTable("Assignment", "Yogi");


    }

    }
    Configuration in startup.cs

    public void ConfigureServices(IServiceCollection services)
    {

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

    services.AddIdentity<AppUser, AppRole>()
    .AddDefaultUI()
    .AddEntityFrameworkStores<ApplicationDbContext>()
    .AddDefaultTokenProviders();

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

    }
    Controller assignment

    public async Task<IActionResult> Index()
    {
    var scaDbContext = _context.assignments.Include(a => a.Badge).Include(a => a.User);

    return View(await scaDbContext.ToListAsync());
    }
    I obtain this error when call a controller

    An unhandled exception occurred while processing the request. SqlException: Invalid object name 'User'. Microsoft.Data.SqlClient.SqlCommand+<>c.b__164_0(Task result)
    Tuesday, October 29, 2019 7:33 PM

All replies

  • User-474980206 posted

    EF does not support cross database (or dbcontext) joins. you can move the code to a stored proc, and call the proc, or create views in the one database that reference the tables in the other database. if not on the same server, you will need to linked server to create the views.

    Tuesday, October 29, 2019 7:51 PM
  • User475983607 posted

    Another options are moving the two table from the Identity store to the isolated database or adding claims to the user that identifies the db-badge and user info.  Use the claims to filter the isolated table and insert user data.

    Tuesday, October 29, 2019 10:51 PM
  • User433211055 posted

    Thanks for help.. I will try it

    Wednesday, October 30, 2019 9:57 AM