c# - Entity Framework Navigation Property with Composite Foreign Key -

ef 6.1.3.

i have domain contains many instances of "header/ item" type pattern, header can have many items (1 many), , has "current" or "latest" item.

this represented follows:

header     guid id     guid currentitemid     item currentitem      icollection<item> allitems  item     headerid     id 

the pk of items headerid + itemid. reason being that, far, common access pattern items list items related given header, , having headerid first part of pk/clustered index means data clustered index seeks.

our problem when use currentitem navigation property, ever uses itemid lookup, results in not great query plans.

i assume because conventions ef use currentitemid currentitem. question is, there way tell ef perform joins currentitem mapping header.id,header.currentitemid -> item.headerid,item.id?

i believe slight different scenario 1 described here: composite key foreign key

in case, have 1 one mapping not 1 top many, , there doesn't seem withforeignkey method available scenario.

we ended not being able ef generate sql way wanted - wrote db command interceptor dynamically find instances of join , re-write join match our designed composite key.

we configure dbcontext level so:

    this.modifyjoin<item, header>(         (i) => new header() { currentitemid = i.id }, //what find         (i) => new header() { curerntitemid = i.id, id = i.headerid }); //what replace 

this information attached context instance itself, when command interceptor sees overrides, uses them re-write sql.

this ends working scenarios, there - such when additional filtering doing on item table part of linq statement, aliasing rules used ef become complex follow without writing full sql parser.

for our use, results in ideal join 90% of time, enough us.

the code isn't difficult, it's big put here. add comment if want copy , i'll put on github.