I'm hoping someone can help me with mapping a legacy database. The problem I'm describing here has plagued others, yet I was unable to find a real good solution around the web.

DISCLAIMER: this is a legacy DB. I have no control over the composite keys. They suck and can't be changed no matter much you tell me they suck.

I have 2 tables, both with composite keys. One of the keys from one table is used as part of the key to get a collection from the other table. In short, the keys don't fully match between the table. ClassB is used everywhere I would like to avoid adding properties for the sake of this mapping if possible.

public class ClassA
{
    //[PK]
    public string SsoUid;

    //[PK]
    public string PolicyNumber;

    public IList<ClassB> Others;

    //more properties....

}

public class ClassB
{
    //[PK]
    public string PolicyNumber;

    //[PK]
    public string PolicyDateTime;

    //more properties
}

I want to get an instance of ClassA and get all ClassB rows that match PolicyNumber. I am trying to get something going with a one-to-many, but I realize that this may technically be a many-to-many that I am just treating as one-to-many.

I've tried using an association class but didn't get far enough to see if it works. I'm new to these more complex mappings and am looking for advice. I'm open to pretty much any ideas.

Thanks, Corey