Using Entity Framework with One to Many relationship defined with two Foreign keys

Question

I am working with an existing database, so changing the schema is not an option. Here are the tables aI am working with ...

Product
-------------------------------------
ID               - GUID         [PK]
Name             - varchar(100) 
ModelId          - GUID         [FK1, FK2]
SoftWareVersion  - varchar(10)  [FK2]

[FK1] Foreign Key to Model table
[FK2] Foreign Composite key to SoftwareVersion table


ProductModel
-------------------------------------
ID               - GUID         [PK]
ModelName        - varchar(100)


SoftwareVersion
-------------------------------------
ID               - GUID         [PK]
Version          - varchar(10)
ModelId          - GUID         [FK1]

[FK1] Foreign Key to Model table

Each Individual Product has software installed on it. We have several Product Models and each product is, obviously associated with a single model (Product[FK1]).

Over the life of each product model it may have had several software updates/versions. Likewise, the Software may be installed on many Models.

I need to be able to take a ProductID and return the Software Version installed on that specific product. Typically I would get the Software Version associated with the Model of the product I am looking up but the Model may have had several updates. I can't lookup on the Product.SoftwareVersion field alone as that would return ALL the different models on which that software was installed.

So ultimately I need to return the record from SoftwareVersion where the ModelId & Version match that for the product I am looking up.

I would like to return the SoftwareVersion as a property of the Product ...

Product.SoftwareVersion

In my ProductEntity definition I have the following ...

[Table("Product")]
public class ProductEntity
{
    [ForeignKey("Model")]
    public Guid ModelID { get; set; }
    public virtual ProductModelEntity Model { get; set; }


    [ForeignKey("SoftwareVersion")]
    public String SoftwareVersionNumber { get; set; }
    public virtual SoftwareVersionEntity SoftwareVersion { get; set; }
}

So the "ModelID" performs the role of Foreign Key for the Model and as part of a composite foreign key for SoftwareVersion. I cannot however assign multiple ForeignKey attributes to the same property...

[ForeignKey("Model"),ForeignKey("SoftwareVersion")]
public Guid ModelID { get; set; }
public virtual ProductModelEntity Model { get; set; }

I prefer to work with attributes if posible but am open to other techniques as well. (Attributes describe the class directly in the relevent code rather than spreading the definition around the library. It's nice to understand what you are looking at rather than spending time hunting down the defintions ... but I digress!)

Any assistance as always is greatly appreciated.

Thank You

-G

score 0 · Answer 1 · answered Jul 12 '12 at 18:47

I think the following annotations both will work:

[Table("Product")]
public class ProductEntity
{
    public Guid ModelID { get; set; }
    public String SoftwareVersionNumber { get; set; }

    [ForeignKey("ModelID, SoftwareVersionNumber")]
    public virtual ProductModelEntity Model { get; set; }

    [ForeignKey("SoftwareVersionNumber")]
    public virtual SoftwareVersionEntity SoftwareVersion { get; set; }
}

Or:

[Table("Product")]
public class ProductEntity
{
    [ForeignKey("Model"), Column(Order = 1)]
    public Guid ModelID { get; set; }

    [ForeignKey("Model"), ForeignKey("SoftwareVersion"), Column(Order = 2)]
    public String SoftwareVersionNumber { get; set; }

    public virtual ProductModelEntity Model { get; set; }
    public virtual SoftwareVersionEntity SoftwareVersion { get; set; }
}

Using Entity Framework with One to Many relationship defined with two Foreign keys

1 Answers1