1

The title of this post suggests this may be a simple scenario like this answer but it's actually a bit more complicated.

I'm working on a Blazor site that will serve as a new frontend to an existing set of tables. There is a table of company Clients with a primary key called ClientId.

There are also Users, which represent company employees, with a primary key of UserId.

All of the columns in Client are stored in this table with the exception of the client's relationship to the user (good relationship, don't know them well, etc.). In the existing EmployeeRelationship table, the ClientId and UserId both serve as the primary key, as the EmployeeRelationship is dependent on both the Client and User. This is fine but I have been unable to make EF Core accept this relationship.

What I'd like to do is add the EmployeeRelationship property to my Client object such that I can reference it like Client.EmployeeRelationship.Description. The problem then is the User object, as EF has no idea what to do with the secondary PK on EmployeeRelationships if I call .Include, as there is no FK for User in the Client object.

Would it be possible to provide this foreign key when I call Include on Client?

For example, something like:

Context.Clients.Select(x => x).Include(x => EmployeeRelationship, UserId);

Where {UserId} can be used to provide the missing foreign key.

If not, how can I get around this issue? I also find it odd that whoever created this table used the UserId and ClientId as the primary key because it means the User/Client tables share the same name for the PK, but I'm not sure if this is a problem. It seems like the UserId and ClientId should be foreign keys but this still wouldn't solve my problem.

Mr.Technician
  • 301
  • 5
  • 15
  • 1
    What is the type of the `Client` -> `EmployeeRelationship` relationship? The way you explain the `EmployeeRelationship` table it looks like one-to-**many**, so there is no single `EmployeeRelationship` per `Client`, but multiple, which is represented with `ICollection EmployeeRelationships` property rather than `EmployeeRelationship EmployeeRelationship` property you are asking for. – Ivan Stoev May 07 '21 at 06:25
  • It's technically a one to many relationship, yes. I was hoping to access it as a one to one relationship as there is one employee relationship per Client/User _pairing_. I don't care about the other relationships. You may have given me an idea, though. I could define EmployeeRelationship as an ICollection as you described and when I used `.Include`, [filter the included results](https://stackoverflow.com/a/61147681/14024210)? That way I would only get the relationship I need instead of every other relationship. – Mr.Technician May 07 '21 at 13:14
  • 1
    Sure you can. An then use `FirstOrDefault()` to access the single item. Actually there is no other way so far since EFC does not support "logical" navigations. Or "skip" navigations other than collection to collection for M2M. – Ivan Stoev May 07 '21 at 14:20
  • Seems to be working as I need it to, thanks! – Mr.Technician May 07 '21 at 14:23

0 Answers0