Storing IdentityDbContext in separate database, redudance?

Question

I've got some thoughts regarding ASP.NET Identity for my backend.

I've stored my IdentityDbContext as a separate database using SqlLite.

I therefore have my own dbcontext: "BaseDbContext", as well as "IdentityDbContext".

My own db-context is registered on a separate database, since I don't want to be tied up to identity's structure.

HOWEVER, I have one question regarding this: If I want to connect users to other tables and keep track of actions etc, should I have my own "tblUser" alongside Identity, or how would this work in practise?

So basically my structure looks like the following:

IdentityDb:

• AspNetUsers
• AspNetRoles
• etc ..

BaseDb:

• tblProject
• tblTask
• tblUser ????

I've previously used a custom datastore within Identity, but it's a lot to rewrite and wrap, and thought I'd go for the traditional setup this time around.

Any input regarding this is highly appreciated.

Answer 1

How would it know how to reference IdentityDb though since they are not coupled?

An Id or UserName value from AspNetUsers table would be referenced in some table in BaseDb. When you say "coupled", I'm assuming you're talking about a foreign key to enforce relationships between tables. You can use DataAnnotations or Fluent API to create a foreign key (which is preferable, but not necessary). But basically, anytime you get a UserId or UserName value through ASP.NET Identity, you would use that value to get/set row(s) from some table in BaseDb. See the code below.

Do you mean a column in BaseDb to represent these Asp.Net values without an actual relationship?

Yes a column such as UserId or UserName in a table (or tables) in the BaseDb database can reference the UserId or UserName column in the AspNetUsers database without a foreign key to enforce the relationship. However, you can use DataAnnotations or Fluent API to create a foreign key. I didn't add code for that btw.

Here is some sample code that I hope is helpful:

appsettings.json

{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=./IdentityDb.sqlite"
    "BaseConnection": "Data Source=./BaseDb.sqlite"
  },
  "Logging": {
    "IncludeScopes": false,
    "LogLevel": {
      "Default": "Warning"
    }
  }
}

Startup.cs snippet

public class Startup
{
    // ...

    public IConfigurationRoot Configuration { get; }

    public void ConfigureServices(IServiceCollection services)
    {
        // ...
        services.AddDbContext<ApplicationDbContext>(options =>
            options.UseSqlite(Configuration.GetConnectionString("DefaultConnection")));
        services.AddDbContext<BaseDbContext>(options =>
            options.UseSqlite(Configuration.GetConnectionString("BaseConnection")));

        services.AddIdentity<ApplicationUser, IdentityRole>()
            .AddEntityFrameworkStores<ApplicationDbContext>()
            .AddDefaultTokenProviders();

        services.AddMvc();

        services.AddScoped<ApplicationDbContext>();
        services.AddScoped<BaseDbContext>();
        // ...
    }

    // ...
}

ApplicationDbContext.cs

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }
}

BaseDbContext.cs

public class BaseDbContext : DbContext
{
    public BaseDbContext(DbContextOptions<BaseDbContext> options)
        : base(options)
    {
    }

    public DbSet<Member> Members { get; set; }
}

Member.cs

public class Member
{
    public int Id { get; set; }

    public string UserName { get; set; } // to link with Identity

    // some other properties of Member
    public int Age { get; set; }
    public virtual ICollection<Favorite> Favorites { get; set; }
}

Then in any of your Controller classes, you get the username of the currently logged in user from ASP.NET Identity, get username(s) in Member table of baseDb, save username in Member table of baseDb, etc.

HomeController.cs snippet

public class HomeController : Controller{
    private readonly ApplicationDbContext _identityContext;
    private readonly BaseDbContext _baseContext;

    public HomeController(ApplicationDbContext identityContext, BaseDbContext baseContext)
    {
        _identityContext = identityContext;
        _baseContext = baseContext;
    }

    public IActionResult Index()
    {
        // get currently logged in user's username from ASP.NET Identity
        string userName = User.Identity.Name;

        // get username in Member table of baseDb where Id column is 123
        string anotherUserName = _context.Member().FirstOrDefault(m => m.Id == 123).UserName;

        // add new row in basedb.Member table with identity username as foreign key
        var model = new Member
        {
            UserName = userName,
            Age = 1,
            Favorites = new List<Favorite>();
        };
        _baseContext.Add(model);
        _baseContext.SaveChanges();

        return View();
    }
}