ASP.Net WebAPI OData - Composite keys on entities

Question

I have some entities in my EF data model where the entity's key is a composite of two columns.

Made-up (but reasonably close) example... Three entities

Patients
  PatientID           (PK)
  PatientName, etc

Identifiers
  PatientID           (PK)
  IdentifierTypeID    (PK)
  Code
  StartDate, etc

IdentifierTypes
  IdentifierTypeID    (PK)
  Description

The Identifiers table has a composite key. Basically a many-to-many between Patients and IdentifierTypes but with data on the join table. It ought not to matter, but in case it does, the IDs are GUIDs (uniqueidentifier in SQL Server).

I can bend my database a bit to make a new column in Identifiers such as "IdentifierID" that could be the primary key, but we have a legacy code base I'd rather not modify if I can avoid it.

I can see several solutions that avoid modifying the tables although I'm not sure if all are actually possible in WebAPI

a) Create a view in the database and have this as the basis of my entity. The view is of the form

select 
  *,
  IdentifierID = cast( PatientID as varchar(50) ) + '_' + cast( IdentifierTypeID as varchar(50) )
from Identifiers

This has the problem that seeks on IdentifierID from Entity Framework will be slow. I can rectify this somewhat with an indexed view I suppose.

b) Same as (a), but in my controller's [Queryable] Get() method I instead use ODataQueryOptions and some expression parsing magic (not very experienced with it at all) to see if we're filtering on IdentifierID and, if so, split that up into filters on PatientID and IdentifierTypeID

c) I keep things as they are and discover some unknown support for Tuple as entity key in the WebAPI OData stack. That would be awesome :) I'm inheriting from EntitySetController<>, so it would look like I'm inheriting from EntitySetController>

d) Some blend of (c) that's not quite as clever but still achieves the same outcome

It seems possible to have composite keys in the OData spec as this other question on StackOverflow says how to address such an entity in OData (using WCF Data Services). How to address entity that uses composite identity key in OData Url?

I don't mind using nightly builds or even using the ASP.Net source - I've spent the weekend stepping through OData requests already to solve another issue (my fault) formatting GUIDs in query strings :P I'm hoping someone's already invested the hours understanding this and I can avoid another day hitting breakpoints in the framework source.

Thanks for your help, Ian

RaghuRam Nadiminti RaghuRam Nadiminti · Accepted Answer · 2013-04-02T01:11:33

It is possible to do composite keys with web API OData. You don't even need to use the nightly build to do that. RTM bits should be good. Refer to this great sample by Hongye for details.

ASP.Net WebAPI OData - Composite keys on entities

1 Answers