One Entity for multiple DBSets

Question

I'm looking for a solution or good practice to use a class for example -> "Account" in several DBSets, so that

public class Account
{
    public string ID {get; set;}
    public string Name {get; set;}
}

for multiple DBSets would be something like that:

public class ModelContext : DBContext
{
    public DBSet<Account> AAccount {get;set;}
    public DBSet<Account> BAccount {get;set;}
    public DBSet<Account> CAccount {get;set;}

    protected override void OnModelCreating(Modelbuilder modelbuilder)
    {
        modelbuilder.Entity<Account>(entity => entity.ToTable("AACOUNTS") ... some property definition);
        modelbuilder.Entity<Account>(entity => entity.ToTable("BACOUNTS") ... some property definition);
        modelbuilder.Entity<Account>(entity => entity.ToTable("CACOUNTS") ... some property definition);
    }
}

So that I can utilize the above in some kind of factory, such as:

public List<Account> GetAccounts(string type)
{
    List<Accounts> accounts = new();
    using{var db = new ModelContext())
    {
        switch(type)
        {
            case "A":
                accounts = db.AAccounts.ToList();
                break;
            case "B":
                accounts = db.BAccounts.ToList();
                break;
            case "C":
                accounts = db.CAccounts.ToList();
                break;
            default:
                throw new NotImplementedException();
         }
     }
     return accounts;
}

I tried to use an interface IAccount and implement that interface for AAccount, BAccount and CAccount. That works until I want to get a List accounts = db.AAccounts.ToList(); How can I solve this problem? Thank you!

Answer 1

That's a rather odd example... DbSets are wrappers for tables, how do you see distinguishing this data in the database? what you describe sounds more like an inheretence scenario where you'd have:

public class AAccount : Account
{}

public class BAccount : Account
{}

public class BAccount : Account
{}

In the database these can all be stored in an Account table with a discriminator (A vs. B vs. C) using table-per-hierarchy or separate tables using table-per-concreteType.

Otherwise if you just want to divide accounts and access them as different sets for convenience:

public DbSet<Account> Accounts { get; set; }

public IQueryable<Account> AAccounts => Accounts.Where(a => /* Insert here how you determine why accounts fit the "A"Accounts list */);
public IQueryable<Account> BAccounts => Accounts.Where(a => /* Insert here how you determine why accounts fit the "B"Accounts list */);
public IQueryable<Account> CAccounts => Accounts.Where(a => /* Insert here how you determine why accounts fit the "C"Accounts list */);

Of course any code that goes to add an "account" would need to do so through the Accounts DbSet, the IQueryable sets are provided simply for convenience. An example I might think of is if you wanted to regularly group accounts by date:

private DateTime _today = DateTime.Today;
private DateTime _recentCutOffDate = DateTime.Today.AddMonths(-3);

public IQueryable<Account> TodayAccounts => Accounts
    .Where(a => a.Orders.Any(o => o.OrderDate == _today));
public IQueryable<Account> RecentAccounts => Accounts
    .Where(a => a.Orders.Any(o => o.OrderDate >= _recentCutOffDate);
public IQueryable<Account> OlderAccounts => Accounts
    .Where(a => !a.Orders.Any(o => o.OrderDate >= _recentCutOffDate);