Multiple DbSet<> for same entity but with different filters

user_2098652 0
Viewed 333

To make life easier for some of our developers, I want to create two DbSet<> for one database entity/table. Here's an example:

public class User
{
    public int Id { get; set; }
    public DateTime Deleted { get; set; }
}

public class UserContext : DbContext
{
    public DbSet<User> DeletedUsers { get; set; }
    public DbSet<User> ActiveUsers { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        //for ActiveUsers
        modelBuilder.Entity<User>().HasQueryFilter(e => e.Deleted == null);

        //for DeletedUsers
        modelBuilder.Entity<User>().HasQueryFilter(e => e.Deleted != null);
    }
}

How can I make my two DbSet<> differentiate between these two? I want to be able to say dbContext.DeletedUsers.Where(x => x.Id == .....) instead of having to say dbContext.Users.Where(x => x.Deleted != null && x.Id == ......) all the same, as people might not remember that they're dealing with deleted users (or counterwise).

entity-framework-core entity-framework c#
2 Answers

You don't use a DbSet for that since it's the same Entity, just expose an IQueryable property, eg

public class UserContext : DbContext
{
    public DbSet<User> Users { get; set; }

    public IQueryable<User> ActiveUsers => Users.Where(e => e.Deleted == null);

    public IQueryable<User> DeletedUsers => Users.Where(e => e.Deleted != null);

}

And you also can omit the Users property if you like, and configure User as an entity on OnModelCreating, then accessing all users only through DbContext.Set<User>() if necessary.

user_72977000

You can't do that since EF has no way to distinguish between the two things. You could apply the query filter to only show active users and if deleted users are really needed, then the user can call IgnoreQueryFilters(), for example:

var allusers = context
    .Set<User>()
    .IgnoreQueryFilters()
    .ToList();

Alternatively, you coud use SQL views. For example, something like this (untested code off the top of my head):

Create a view for ActiveUsers:

CREATE VIEW ActiveUsers
SELECT * FROM Users WHERE Deleted IS NULL -- Obviously don't use SELECT *...

Add a new class for active users:

public class ActiveUser
{
    // all the properties
}

And configure your DbContext:

modelBuilder
    .Entity<ActiveUser>()
    .ToView("ActiveUsers")
    .HasKey(t => t.Id);

Then do the same for DeletedUsers.

user_16630010
Related