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);
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)
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.
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.
Member
1 Points
42 Posts
MultiContext DB with EntityFramework Core 3 Two Connection string
Oct 29, 2019 07:33 PM|cicciuzzo|LINK
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)
All-Star
53314 Points
13186 Posts
Re: MultiContext DB with EntityFramework Core 3 Two Connection string
Oct 29, 2019 07:51 PM|bruce (sqlwork.com)|LINK
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.
All-Star
43321 Points
18471 Posts
Re: MultiContext DB with EntityFramework Core 3 Two Connection string
Oct 29, 2019 10:51 PM|mgebhard|LINK
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.
Member
1 Points
42 Posts
Re: MultiContext DB with EntityFramework Core 3 Two Connection string
Oct 30, 2019 09:57 AM|cicciuzzo|LINK
Thanks for help.. I will try it