c# – EF LINQ query cannot be translated

I have an app where I have run into an exception that I cannot figure out. Using EF Core 3.1 and Postgres 12.

Have the following relationships:

User -> UserNetworks -> Network -> Resource

A user belongs to many networks. A network can contain many resources. I want to query all the resources that a user has access to in all the networks they are a part of.

I have a test app built that does this and works fine. The issue is that I have a real world app that is much more complicated where the same query doesn’t work and throws the following exception:

The LINQ expression ‘DbSet.Join(outer: DbSet, inner: r => EF.Property(r, “network_id”), outerKeySelector: n => EF.Property(n, “Id”), innerKeySelector: (o, i) => new TransparentIdentifier<Resource, Network>(Outer = o, Inner = i)).Where(r => r.Inner.UserNetworks.Any(y => y.User.Id == 2))’ could not be translated.

I understand the exception and know what it means, but I haven’t been able to figure out why I am getting it in this case as it seems like it should work fine. In troubleshooting all day, I finally noticed something in the original exception that I had overlooked that seemed wrong. However, I am not sure it’s the issue or, if it is, how to fix it?

Quick backstory…since we decided to use Postgres for this project and we didn’t want to have to write all our queries with quotes everywhere, we decided to lowercase all the tables and columns. Every table and every column in the entire db is lowercase, with words separated by underscores. This is done via EF Type Configurations as seen below.

Below is a snippet of the resource type configuration class. “Builder” is just and instance of EntityTypeBuilder. The calls to HasForeignKey are simply passing string values via the GetName method call. For instance, in the example below, HasForeignKey would get passed the value of “network_id”.

Resource Type Congfiguration

   Builder
      .HasOne(x => x.Network)
      .WithMany(x => x.Resources)
      .IsRequired()
      .HasForeignKey(ResourceEnums.Columns.Network_Id.GetName());

If you look back at the exception, specifically at the “inner” and “outerKeySelector” values, you will notice that in the “inner”, the EF Property uses “network_id” (lowercase) to point at the foreign key column (like i configured it to). However, the outerKeySelector uses “Id” (uppercase) as its property name. Even though the property name in the C# entity is called Id (uppercase), I’m wondering if this outerKeySelector shouldn’t actually be the id column from the principal table and therefore, should be “id” (lowercase).

So, my question:

  1. If this is possibly the issue and since EF is generating this value (ie…not my code directly), how do I tell EF via the Type Configuration that “Id” should be “id” when used in this relationship?

HasPrincialKey seemed like a promising option, like this:

Builder
   .HasMany(x => x.Resources)
   .WithOne(x => x.Network)
   .HasPrincipalKey("id");

But when I tried it this way, the code will compile, but the EF migration tool fails when generating the migration scripts. This exception is:

“The property ‘id’ cannot be added to the type ‘Network’ because there
was no property type specified and there is no corresponding CLR
property or field. To add a shadow state property the property type
must be specified.”.

My guess on this is because the property on the model is uppercase and this is defining it as lowercase so they can’t be mapped together.

If I change the type configuration to this:

   Builder
      .HasOne(x => x.Network)
      .WithMany(x => x.Resources)
      .IsRequired()
      .HasPrincipalKey(x => x.Id)
      .HasForeignKey(ResourceEnums.Columns.Network_Id.GetName());

EF will generate the migration scripts again, but I go back to the original exception.

I am not sure what else to try at this point. Any help is greatly appreciated, and I can provide any information needed if there is anything unclear or missing.