Exclude derived entities from requests to the base class

user_777472 0
Viewed 669

I have this DbContext:

public class DataContext : DbContext
{
    public DbSet<Base> Bases {get;set}
    public DbSet<Sub> Subs {get;set}
}

Sub is a subclass of Base.

When I'm querying the list of Base entities like so:

Context.Bases.ToListAsync()

It returns me every entities, either Base or Sub.

How can I configure my model context to get only the entities that are of Base type and not the ones that derives from it.

ef-core-2.0 asp.net-core entity-framework
3 Answers

The best (or least worst) solution I found is to directly use the shadow property:

Context.Bases.Where(b => EF.Property<string>(b, "Discriminator") == "Base")).ToListAsync();

It works but needs to be repeted now and then, each time I need to query Bases. I'd have prefered a solution in the OnModelCreating method.

I'll accept this answer unless someone else find a better solution.

user_7774720

How can I configure my model context to get only the entities that are of Base type and not the ones that derives from it.

You cannot. Every Sub is a Base. So querying all Bases includes all Subs. Eg code like the following must succeed:

Base b = db.Bases.Where(i => i.Id == 1).Single();
if (b is Sub)
begin
  Sub s = (Sub)b;
  . . .
end
else //other Sub
begin
  Sub2 s = (Sub2)b;
  . . .

end

You can fetch an anonymous type with the just the base class properties.

And asking this question suggests that inheritance might is not the right modeling technique for your scenario.

If what you want it to fetch the Entities of type Base, but not the subtype Sub, then you can do that with a query like:

var q = from b in db.Bases
        where !(b is Sub)
        select b;

Which translates to :

SELECT [b].[Id], [b].[Discriminator], [b].[Name], [b].[Size]
FROM [Bases] AS [b]
WHERE [b].[Discriminator] IN (N'Sub', N'Base') 
AND NOT ([b].[Discriminator] = N'Sub')

But you can't (currently) exclude all subtypes without enumerating them. Eg this query:

var q2 = from b in db.Bases
         where b.GetType() == typeof(Base)
         select b;

Will not be completely translated to SQL, and will filter out the subtypes on the client.

user_72977000

You'd have to use OfType<T>:

var basesOnly = await _context.Bases.OfType<Base>().ToListAsync();

UPDATE

Sorry, then. I could have sworn the above works, but it doesn't. The next best method I can think of is to simply filter out the types you don't want. It's not ideal, because it requires specifying all subtypes in your query, which then means you need to remember to update it if you add more subtypes.

var basesOnly = await _context.Bases.Where(x => !(x is Sub)).ToListAsync();
user_6540310
Related