Getting records of specific type

0 votes
asked Apr 29, 2014 by wim-ten-brink

I have two classes:

public class BaseItem
{ public Guid Id { get; set; } public string Name { get; set; } public virtual List<ChildItem> Children { get; set; }
public class ChildItem : BaseItem
{ public virtual BaseItem Parent { get; set; } public ChildItem() { }

And I created a DBContext plus two DBSets for these two classes. Basically, BaseItem is the root class with a tree of child items below it. The DB will contain dozens of such trees.
The Entity Framework will create just one table for both classes, which I don't like but for now it's good enough. (Can I tell EF to split it up in two tables in an easy way?)

My problem is selecting just the root items, thus anything of class BaseItem but not of class ChildItem. Something like


Which will not work because EF can't convert the GetType method to something that makes sense to the database. So I either put a .ToList() before the Where-clause or I find a better solution.
Since I don't want to retrieve all 1,000,000 records before filtering out the 500 root items, I need the better solution. What would be the better solution?

This is how the DBContext (db variable) and DBSet (Items property of db) is defined.

 public class Datamodel : DbContext { public Datamodel() : base("name=Datamodel") { } public virtual DbSet<BaseItem> Items { get; set; } public virtual DbSet<ChildItem> Children { get; set; } }

1 Answer

+1 vote
answered Nov 29, 2018 by brumscouse

As inheritance is involved, there is probably a type descriminator against the entities you have stored you can filter against and it appears as you have just one table that you have an arrangement called Table Per Hierachy (TPH)

Check this link out:

It refers to an OfType<T>() method which might do your bidding.

EDIT(Corrected to working example)

With a usage something like :

var rootItems = context.Items.OfType<BaseItem>().Except(context.Items.OfType<ChildItem>());

Just the root items are pulled back in a TPH scenario.


1 AS [C1],
[Except1].[Discriminator] AS [C2],
[Except1].[Id] AS [C3],
[Except1].[Name] AS [C4],
[Except1].[Parent_Id] AS [C5]
FROM (SELECT [Extent1].[Discriminator] AS [Discriminator], [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent1].[Parent_Id] AS [Parent_Id] FROM [dbo].[BaseItems] AS [Extent1]
WHERE [Extent1].[Discriminator] IN (N'ChildItem',N'BaseItem')
EXCEPT SELECT N'ChildItem' AS [C1], [Extent2].[Id] AS [Id], [Extent2].[Name] AS [Name], [Extent2].[Parent_Id] AS [Parent_Id] FROM [dbo].[BaseItems] AS [Extent2] WHERE [Extent2].[Discriminator] = N'ChildItem') AS [Except1]}
Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter