In Linq, how do you query based on a child record?

Question

I have a simple parent/child structure which stores a history of messages sent users. I have generated a dbContext and linked all entites (using EF Core Power Tools).

So how can I actually get all notifications for a certain UserID when the UserIDs are in a child table (called PushNotificationReceipients)?

I thought I could do something like this but it fails with an error:

    var recipient = new PushNotificationRecipient();
    recipient.UserId = userId;
    var results = _dbContext.PushNotifications
        .Include(t => t.PushNotificationContents)
        .Where(t => t.PushNotificationRecipients.Contains(recipient))
        .ToList();

..but this gives the error

The LINQ expression could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable()

..and client evaluation will be too slow.

Is there any way to do this in Linq or do I need to pass in the SQL directly?

Answer 1

There are two main problems with your the expression in your Where:

1. As the exception message tells you, that expression cannot be translated to SQL.

2. Even if it could be translated to SQL, .Contains() would return false nonetheless. That's because the new PushNotificationRecipient object that you created is not the same one in the collection. Just because it has the same id doesn't make the two objects equal.

Try something like the following:

var results = _dbContext.PushNotifications
    .Include(t => t.PushNotificationContents)
    .Where(t => t.PushNotificationRecipients.Any(r => r.UserId == userId))
    .ToList();