Entity Framework Navigation Property with Composite Foreign Key

Question

EF 6.1.3.

I have a domain which contains many instances of a "Header/ Item" type pattern, where the a Header can have many Items (1 to many), and also has a "current" or "latest" item.

This is represented as follows:

Header
    Guid Id
    Guid CurrentItemId
    Item CurrentItem 
    ICollection<Item> AllItems

Item
    HeaderId
    Id

The PK of the Items is always the HeaderID + ItemID. The reason being that, by far, the most common access pattern for items is to list all items related to a given header, and having HeaderID be the first part of the PK/clustered index means we get that data with clustered index seeks.

Our problem is that when we use the CurrentItem navigation property, it only ever uses the ItemID to do the lookup, which results in not so great query plans.

I assume this is because the conventions for EF us to use the CurrentItemId to look up the CurrentItem. My question is, is there a way for my to tell EF to always perform its joins for CurrentItem by mapping the Header.Id,Header.CurrentItemId -> Item.HeaderId,Item.Id?

I believe this is a slight different scenario than the one described here: composite key as foreign key

In my case, I have a one to one mapping not one top many, and there doesn't seem to be a WithforeignKey method available for that scenario.

Possible duplicate of [composite key as foreign key](http://stackoverflow.com/questions/5436731/composite-key-as-foreign-key) — Nikhil Vartak, Apr 07 '17 at 02:53
I'm not sure it is a duplicate, as the post you reference doesn't have a column mismatching between the entities. In my case, the Header's ID field needs to map to the Item.HeaderId field in addition to the CurrentVersionID field mapping to the ID field in the Item entity. It is also a case of a one to one mapping, not a one to many mapping, and the Item entity has no nav property back to Header. This could be a minor difference, but it's tripping me up. — RMD, Apr 07 '17 at 03:02
We do something very similar where I work but using Integers and do not have this problem. I'm thinking EF might be handling guid differently or if it has a unique constraint on the table for it, it's trying opting to use that. Also the guid might cause some problems with fragmentation, I know we've had this issue in SQL Server. — Daniel Leach, Apr 07 '17 at 05:27

score 0 · Accepted Answer · answered May 04 '17 at 12:53

We ended up not being able to get EF to generate the SQL the way we wanted - so we wrote a db command interceptor to dynamically find instances of this join and re-write the join to match our designed composite key.

We configure this as the DbContext level like so:

    this.ModifyJoin<Item, Header>(
        (i) => new Header() { CurrentItemId = i.Id }, //What to find
        (i) => new Header() { CurerntItemId = i.Id, Id = i.HeaderId }); //What to replace with

This information is attached to the context instance itself, so when the command interceptor sees the overrides, it uses them to re-write the SQL.

This ends up working well for most scenarios, but there are some - such as when additional filtering is doing on the Item table as part of the LINQ statement, that the aliasing rules used by EF become too complex to follow without writing a full SQL parser.

For our use, this results in the ideal join about 90% of the time, which is good enough for us.

The code to do all this isn't difficult, but it's too big to put here. Add a comment if you want a copy and I'll put it up on GitHub.

Entity Framework Navigation Property with Composite Foreign Key

1 Answers1