Pre-filtering results with entity framework database-first

Question

Using EF database-first, is it possible to create a duplicate of one of the classes, such that any query made comes back with an additional filter?

As an example: Given a class

 public partial class Person
 {
 public Person()
    {
        this.Job= new HashSet<Appointments>();
    }

    public int PersonID { get; set; }
    public int JobID { get; set; }
    public string Forename { get; set; }
    public string Surname { get; set; }

  public virtual ICollection<Appointments> Appointments { get; set; }
  }

Is it possible to construct a duplicate of the class in some way that functions like the existing class, but will only return results applied a "where Forename = 'David')

I can't overwrite the existing class (both cases need to be kept, and it'll be overwritten anyway)

My first thought was to simply create a seperate static class with methods that return an IQueryable< Persons>, but to then call that later, the context has been disposed - I don't think you can attach it to a new context?

Is this class a single person or multiple? The class name is plural, but it has a PersonId and person's first and last name. — Scott Hannen, Jan 08 '18 at 13:58
Might not be applicable to you, but EF Core 2.0 introduced [Global query filters](http://gunnarpeipman.com/2017/08/ef-core-global-query-filters/) which would support this usecase. — Georg Patscheider, Jan 08 '18 at 14:00
well, its not a real class - just one that I made up for an example, so it's my bad sorry. But the intent was a single person (with a one-to-many on appointments) — David, Jan 08 '18 at 14:01
@GeorgPatscheider - Thanks, i'm not on core at the moment, but that's great to know for future! — David, Jan 08 '18 at 14:04
If the class is a single person then what would an instance look like with the filter applied if the instance doesn't match the filter? IOW - `PersonWithFirstNameFilterApplied` when the first name is different - what is it, null? — Scott Hannen, Jan 08 '18 at 14:07
@ScottHannen For clarity: it's row-per-person, which means its the result should be the same as persons.Where(x=>x.Forename=='David') at all times — David, Jan 08 '18 at 14:11
`persons.Where` is an `IEnumerable`. You can't have a duplicate of your `Person` class which is also an `IEnumerable`. — Scott Hannen, Jan 08 '18 at 14:15
@ScottHannen Well, to be technical, it's an `IQueryable` not `IEnumerable`. The distinction is important when dealing with Entity Framework. — Bradley Uffner, Jan 08 '18 at 14:18

Bradley Uffner · Accepted Answer · 2018-01-08T14:23:31.203

The best you could do would be to add a function to your DbContext, in a partial class, that returns an IQueryable<Persons> with the filter already applied.

The partial class should have the same name as your actual context class. Any code in the partial class will be merged with the Database-First generated class, as if they were in the same file. It also won't get touched or overwritten by the code-generator if you regenerate the context. You can use this same concept to extend all kinds of code-generated classes (this is exactly the kind of use-case that partial classes were designed for).

public partial class MyDbContext
{
    public IQueryable<Persons> FilteredPersons()
    {
        return this.Persons.Where(p => p.Forename =="David");
    }
}

Then you can call it like this:

using (var myContext = new MyDbContext())
{
    var query = myContext.FilteredPersons().Where(...some additional filter...);
    var results = query.ToList();
}

You could probably also rig something up with an IDBCommandInterceptor, but that would be huge, hacky, ugly, and beyond the scope of a simple answer like this.

That's good, but won't it potentially get lost if/when the context gets regenerated? — David, Jan 08 '18 at 14:07
You can put it in a separate file, and make it a `partial class`, that way it will get merged with the class from the generated code, and not overwritten if you update the context. You can use this same concept to extend a lot of the generated code safely. — Bradley Uffner, Jan 08 '18 at 14:09

Pre-filtering results with entity framework database-first

1 Answers1