Using EF Include on TPH

Question

I have implemented code first db architecture with simple inheritance using THP:

And I need to query all notifications of all type. TargetUser property in NotificationUser table is a association. I'm trying to execute next code:

var notifications = _context.Notifications;
foreach (var notification in notifications)
{
    Debug.WriteLine((notification is NotificationUser)? ((NotificationUser) notification).TargetUser?.Name : "-");
}

In database propety TargetUser is set to correct foreign key, but in code I don't get any result. Lazy loading is enabled.

Is it possible to user eager loading? I had already tried to write _context.Notifications.Include('TargetUser') byt it throws an exception.

---

Upd. The exception is:

A specified Include path is not valid. The EntityType 'Core.Concrete.NotificationBase' does not declare a navigation property with the name 'TargetUser'.

---

Tried to modify this answer to:

var notifications = _context.Notifications.OfType<NotificationUser>()
                .Include(n => n.TargetUser)
                .Cast<NotificationBase>()
                .Union(_context.Notifications.OfType<NotificationPlace>()

but still the same exception is thrown.

Answer 1

I know this is an old thread, but I'd still like to post some improvements for somebody looking for the same solution.

1. Network Redundancy

Selecting Ids and then running a query, that loads items with the Ids is redundant and the same effect can be achieved by simply running this

Solution:

var userNotifications = _context.Notifications
    .OrderByDescending(n => n.DateTime)
    .Skip(offset)
    .Take(limit)
    .OfType<NotificationUser>()
    .Include(n => n.TargetUser)
    .Include(n => n.TargetUser.Images)
    .ToList();

That way, you aren't waiting for 2 DB connections, but just one. Also you save some traffic.

2. Paging on ignored entities?

One would assume, that this specific method is used for only viewing Entities of an inherited type so I would expect Skip and Take to work directly on only entities of said type. e.g. I want to skip 10 NotificationUsers, not 10 Users (of which only 4 are NotificationUsers for example).

Solution: Move ofType higher up the query

var userNotifications = _context.Notifications
    .OfType<NotificationUser>()
    .OrderByDescending(n => n.DateTime)
    .Skip(offset)
    .Take(limit)
    .Include(n => n.TargetUser)
    .Include(n => n.TargetUser.Images)
    .ToList();

3. Async/Await

When writing an API, you should think about using async/await as that doesn't block the thread thus wastes less resources (this will probably require you to rewrite a lot of your existing code if you don't use it already though).

Please study the advantages of async/await and use them in scenarios like waiting for a result.

Solution: Change this

private List<NotificationUser> GetNotificationUsers(int offset, int limit)
    {
        return _context.Notifications
                .OfType<NotificationUser>()
                .OrderByDescending(n => n.DateTime)
                .Skip(offset)
                .Take(limit)
                .Include(n => n.TargetUser)
                .Include(n => n.TargetUser.Images)
                .ToList();
    }

into this

private async Task<List<NotificationUser>> GetNotificationUsersAsync(int offset, int limit)
    {
        return await _context.Notifications
                .OfType<NotificationUser>()
                .OrderByDescending(n => n.DateTime)
                .Skip(offset)
                .Take(limit)
                .Include(n => n.TargetUser)
                .Include(n => n.TargetUser.Images)
                .ToListAsync();
    }

NOTE: You also then have to change any place that uses this method from

var x = GetNotificationUsers(skip, take);

to

var x = await GetNotificationUsersAsync(skip, take);

And make that method async and return a task as well

Answer 2

I don't know about the amount of entities you will work with. If possible, I would try to do the union not on the DB server:

var userNotifications = _context.Notifications.OfType<NotificationUser>()
                                .Include(n => n.TargetUser).ToList();
var placeNotifications = _context.Notifications.OfType<NotificationPlace>().ToList();
var notifications = userNotifications.Union(placeNotifications);

See https://stackoverflow.com/a/27643393/2342504

Answer 3

Already tried a lot of different solutions, and none fit my requirements, as I'm working on an API, and the query must support pagination and make constant requests to the database (and not loading all entities in memory).

Finally found out a solution, perhaps not the best, but enough for now. Firstly, I request a portion of ordered data (pagination logic):

var notifications = _context.Notifications
            .OrderByDescending(n => n.DateTime)
            .Skip(offset)
            .Take(limit);

(At this point I'm not interested in any properties) Next, I'm getting the Id's of loaded items for each entity type:

var ids = notifications.OfType<NotificationUser>().Select(n => n.Id).ToList();

and lastly load specific entities including all properties:

var userNotifications = _context.Notifications.OfType<NotificationUser>()
             .Include(n => n.TargetUser)
             .Include(n => n.TargetUser.Images)
             .Where(n => ids.Contains(n.Id))
             .ToList();

all entities goes to list and sorted one more time.

A lot of bad stuff here, hope someone can provide better solution.

Answer 4

If I understood well, you want to get all the entities from the table + the property TargetUser when relevant (for entities of type NotificationUser). You said that "Lazy loading is enabled", which is a good thing.

You tried something like (updated to latest version of C#):

var notifications = _context.Notifications;
foreach (var notification in notifications)
{
    Debug.WriteLine((notification is NotificationUser notificationUser) 
        ? notificationUser.TargetUser?.Name 
        : "-");
}

If you don't get any result, it probably means that your entities are badly configured to work with lazy loading:

• Are your classes public?
• Are your navigation properties defined as public, virtual?

See: https://learn.microsoft.com/en-us/ef/ef6/querying/related-data#lazy-loading