1

I'm using Entity Framework 6 with database first. In a current project I have some rows in the database that never should be fetched by Entity Framework. Let's say that I have this simplified model:

    public partial class Customer
    {
        public Customer()
        {

        }

        public int Id { get; set; }
        public string Name { get; set; }
        public DateTime? Deleted { get; set; }
    }

    public partial class MyEntities: DbContext
    {
        public MyEntities()
            : base("name=MyEntities")
        {
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }

        public virtual DbSet<Customer> Customers { get; set; }
    }

Every time I make an operation on the Customer DBSet I really just want to fetch items that have Deleted == null. This is because the system sometimes will mark Customers as deleted, and then the application should not be aware of these items (as if they didn't exist).

The model is way more complex in reality, which is why I want to control this at the DbContext-level, rather than adding a Where()-clause in every query.

Is there any way to always exclude items based upon a column in the table?

Something like so:

public partial class MyEntities: DbContext
{
    public MyEntities()
        : base("name=MyEntities")
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        throw new UnintentionalCodeFirstException();
        Customers = Customers.Where(x => x.Deleted == null); // Obviously not working...
    }

    public virtual DbSet<Customer> Customers { get; set; }
}

Edit: This will be applied to approx 20-30 different entities in a complex model. So I would like to avoid views and manage it in code instead.

Edit 2: Thanks for the tip about EntityFramework.DynamicFilters. However, it doesn't seem to support database first.

Sorry, but when using Database First, we don't have any access to the model configuration so this is not supported.

https://github.com/jcachat/EntityFramework.DynamicFilters/issues/41

smoksnes
  • 10,509
  • 4
  • 49
  • 74
  • In EF6, at least, you could achieve this with interceptors. It might be easier to use a package that implements filters, though, like NHibernate. – sschimmel Feb 01 '16 at 14:36
  • Or [EntityFramework.DynamicFilters](https://github.com/jcachat/EntityFramework.DynamicFilters). See also: http://stackoverflow.com/a/34343982/861716 – Gert Arnold Feb 01 '16 at 14:40
  • I'd call the linked answer from @GertArnold an effective duplicate of this question despite the fact that the questions are not related. But that answer is perfect. – krillgar Feb 01 '16 at 18:21
  • Great. I'll check it out. Thank you. – smoksnes Feb 01 '16 at 18:25
  • EntityFramework.DynamicFilter seems to be a great package, but it currently does not support database first. – smoksnes Feb 02 '16 at 06:42

1 Answers1

1

How about making a custom view

dbo.AvailableCustomers

like this:

CREATE VIEW dbo.AvailableCustomers
AS SELECT * FROM Customers
Where Deleted IS NULL;

and then the Context's Customers DBSet will point to the new view.

Tamas Ionut
  • 4,240
  • 5
  • 36
  • 59