Entity framework - Conditionally query related entities with derived types methods

Question

I have three class:

public abstract class Conversation
{
    public int Id { get; set; }

    public abstract bool HasUser(string userId);
}

public class PublicConversation : Conversation
{
    public override bool HasUser(string userId)
    {
        return true;
    }
}

public class PrivateConversation : Conversation
{
    public ICollection<User> Users { get; set; }

    public override bool HasUser(string userId)
    {
        return Users.Any(t => t.UserId == userId);
    }
}

In DbContext, there are DbSets:

public DbSet<Conversation> Conversations { get; set; }
public DbSet<PrivateConversation> PrivateConversations { get; set; }
public DbSet<PublicConversation> PublicConversations { get; set; }

public DbSet<User> Users { get; set; }

Which generates two tables: Conversations and Users.

PrivateConversations and PublicConversations are saved in table Conversations

Now an error occurred with query:

// For some reason, I can only query `db.Conversations`, can not access `db.PrivateConversations`
var conversations = db.Conversations
  .Include(t => (t as PrivateConversation).Users)
  .Where(t => t.HasUser("something"))
  .ToList();

For all PublicConversation, everything works fine.

Told me that in PrivateConversation method: HasUser(), Users.Any(), Users is null.

I am very confused. How can I query all private conversations with user in it?

Additional info:

packages I usesd:

<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="2.2.6" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SQLServer" Version="2.2.6" />

runtime: .NET Core 2.2

<OutputType>Exe</OutputType>
<TargetFramework>netcoreapp2.2</TargetFramework>

Answer 1

The expression

t.HasUser("something")

cannot be translated to SQL, because it requires Conversation object and invoking the HasUser method. Only the well known methods which does not require materialized objects can be translated to SQL.

Query translators process non translatable expressions differently. EF6 throws exception. The same will be in EF Core 3.0+. But EF Core 1.x / 2.x which you seem to be using tries to evaluate such expressions client side.

The problem is that it works if the condition uses entity primitive properties, but fails with navigation properties because at the time client evaluation, they are not loaded yet, even though they will be loaded later with Include. You can avoid the NRE by initializing the collection navigation property or adding null check, but in either case the result won't be correct.

This, plus the hidden inefficiencies is one of the reason of removing implicit client evaluation in 3.0.

There are 2 options for solving it:

(1) Keep the encapsulation and use explicit client evaluation. Explicit client evaluation means that you insert explicitly AsEnumerable() at some point in the query. Everything before that will be executed by EF Core, and everything after that will be executed by LINQ to Objects on fully materialized results of the LINQ to Entities (EF Core) query.

var conversations = db.Conversations
      .Include(t => (t as PrivateConversation).Users)
      .AsEnumerable() // <--
      .Where(t => t.HasUser("something"))
      .ToList(); 

(2) Break the encapsulation and recreate the expression inplace using translatable constructs. This way the filtering will happen server side:

var conversations = db.Conversations
      .Include(t => (t as PrivateConversation).Users)
      .Where(t => t is PrivateConversation ?
          ((PrivateConversation)t).Users.Any(u => u.Name == "something") :
          true)
      .ToList(); 

i.e. instead of

t.HasUser("something")

you'd use

t is PrivateConversation ?
((PrivateConversation)t).Users.Any(u => u.Name == "something") :
true

or the equivalent

!(t is PrivateConversation)
|| ((PrivateConversation)t).Users.Any(u => u.Name == "something")

which extracts inline the logic from derived class overrides of HasUser method.

Note that EF Core (or any other library) cannot "see" the implementation of a method similar to C# compiler, because it has no source code (except if it tries to decompile the compiled code, which is not trivial task).

Both solutions have pros and cons. (1) is better from OOP point of view, but inefficient because loads potentially much more data (and related data) than it needs. (2) is the opposite - better for performance / memory usage, worse from OOP (breaks encapsulation, needs updating in case new derived class implementation of the method in question etc.) So use the one which better fits your needs.

Answer 2

Shouldn't you add the derived type in the where clause?

    var conversations = db.Conversations
      .Include(t => (t as PrivateConversation).Users)
      .Where(t => t is PrivateConversation && ((PrivateConversation) t).HasUser("something"))
      .ToList();

Answer 3

I copied your set up and got the same error. If you give the PrivateConversation class a constructor like this

public PrivateConversation()
{
    Users = new Collection<User>();
}

then the null exception goes away. But the querying still doesn't work. So it seems as though its trying to do the search in memory rather than sending the sql you would want it to send. I confirmed this by checking the query log.

So the best I could come up with is this

 var conversations = db.Conversations                             
                    .Where(a =>(a as PrivateConversation) != null)
                    .Include(a => (a as PrivateConversation).Users)     
                    .ToList()
                    .Where(a => a.HasUser("something"))
                    .ToList();

As you can see this is not ideal because the final bit of querying is done in memory rather than in the database.

Note I checked to see if the query would work if there was no inheritance involved. In this instance the query did not work with the HasUser method. It only worked when I made the query like this

var conversations = db.Conversations
                     .Include(a => a.Users)
                     .Where(a => a.Users.Any(t => t.UserId == "something"))
                     .ToList();