How do I configure EF 4.1 to correctly model a non standard relationship

I have the following model which is unchangeable:

Person 
-------
int Id (PK)
int CompanyId
bool Deleted

Company
-------
int Id (PK)
int DefaultIdentifierId

Identifier
-------
int PersonId (PK)
int DefaultIdentifierId (PK)
string Identifier

I have created classes for each table but I'm struggling to specify the mappings to the database correctly. I need to be able to return the Id of a non del开发者_如何学JAVAeted person given the company id and Identifer. In sql this would be a join between Company and Identifer via DefaultIdentifierId and a join between Person and Identifier via PersonId but I can't seem to get this specified correctly.

The problem is that you can't have navigation properties between Company and Identifier because neither DefaultIdentifierId in Company nor DefaultIdentifierId in Identifier are primary keys. (In Identifier it's only part of the key.) You can model a one-to-many relationship between Company and Person and between Person and Identifier though. So you could try these model classes:

public class Person
{
    public int Id { get; set; }
    public int CompanyId { get; set; }
    public Company Company { get; set; }
    public bool Deleted { get; set; }
}

public class Company
{
    public int Id { get; set; }
    public int DefaultIdentifierId { get; set; }
}

public class Identifier
{
    [Key, Column(Order = 1)]
    public int PersonId { get; set; }
    [Key, Column(Order = 2)]
    public int DefaultIdentifierId { get; set; }
    public string IdentifierString { get; set; }

    public Person Person { get; set; }
}

public class MyContext : DbContext
{
    public DbSet<Person> People { get; set; }
    public DbSet<Company> Companies { get; set; }
    public DbSet<Identifier> Identifiers { get; set; }
}

The Id in the name DefaultIdentifierId of Company is misleading. In this model this property is a simple scalar property and not a Foreign Key property which belongs to any navigation property.

To run the query you want you will have to use a Join in LINQ since you cannot navigate from Company to Identifier through navigation properties:

int companyId = 1;
string identifierString = "ABC";

List<int> personIds = context.Identifiers
    .Join(context.Companies,
          i => i.DefaultIdentifierId,
          c => c.DefaultIdentifierId,
          (i, c) => new { i, c })
    .Where(a => a.c.Id == companyId 
             && a.i.IdentifierString == identifierString
             && !a.i.Person.Deleted)
    .Select(a => a.i.Person.Id) // or a.i.PersonId should work as well
    .ToList();

The result is not necessarily unique but a list of Person Ids.