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