Entity Framework Association with Filter

I have the following model in my model:

Patient

Vendor

Organization

each of these entities needs Addresses.

The Address basically looks like the following

Address
  AddressTypeId // with Navigation Property/Association to AddressType
  EntityKey // indicates the PK Id of the entity this address is for

AddressType
  EntityId // indicates the entity type this address type corresponds to (Patient or Vendor)
  // This should be on the AddressType, not the Address, since we need a way of knowing what kind of A开发者_开发问答ddressTypes are available to create for new addresses for Patients, Vendors, and Organizations
  //...that is Patients support AddressType X, Vendors support AddressType Y, etc.

I want to create an association for Patient, Vendor, and Organization on the EntityKey property on Address - each with a filter constraint that the Address's AddressType.EntityId is the matching EntityId for that entity (1 for Patient, 2 for Vendor, 3 for Address).

What is the best way of doing this? Most ORM's on the market support this kind of scenario....and it's certainly a very common one.

NOTE: I don't want to create PatientAddress/PatientAddressType, VendorAddress/VendorAddressType, and OrganizationAddress/OrganizationAddress type derived entities. It severely clutters the model and makes it basically incomprehensible.

Right now I'm solving this by doing explicit joins in my LINQ queries:

const int patientTypeEntityId = 1;
var query = from p in repository.Patients
              let addresses = repository.Addresses.Where(a => 
                  a.EntityKey == p.Id & a.AddressType.EntityId == patientTypeEntityId)
              select new { Patient = p, Addresses = a }

but I don't want to continue having to do this.

If I understand correctly you want to have an address collection in your Patient, Vendor, etc...

public class Patient
{
    public int Id { get; set; }
    public ICollection<Address> Addresses { get; set; }
}

public class Vendor
{
    public int Id { get; set; }
    public ICollection<Address> Addresses { get; set; }
}

public class Address
{
    public int Id { get; set; }
    //public int EntityKey { get; set; }
    public AddressType AddressType { get; set; }
}

... and somehow tell EF that Patient.Addresses only gets populated with addresses of address type "Patient".

I think that is not possible for several reasons:

• If you don't expose the foreign key in Address (no EntityKey property there) you have to tell EF the key in the mapping (otherwise it would create/assume two different FK columns):

  modelBuilder.Entity<Patient>()
      .HasMany(p => p.PVAddresses)
      .WithRequired()
      .Map(a => a.MapKey("EntityKey"));
  
  modelBuilder.Entity<Vendor>()
      .HasMany(p => p.PVAddresses)
      .WithRequired()
      .Map(a => a.MapKey("EntityKey"));
  

This throws an exception due to the duplicate "EntityKey" column for two different relationships.

• Next thing we could try is to expose the foreign key as property in Address (EntityKey property is there) and then use this mapping:

  modelBuilder.Entity<Patient>()
      .HasMany(p => p.PVAddresses)
      .WithRequired()
      .HasForeignKey(a => a.EntityKey);
  
  modelBuilder.Entity<Vendor>()
      .HasMany(p => p.PVAddresses)
      .WithRequired()
      .HasForeignKey(a => a.EntityKey);
  

This (surprisingly) doesn't throw an exception but creates two FK constraints in the database between Patient-Address and Vendor-Address with the same FK column EntityKey. For your model, I think, this doesn't make sense because it would require that always a Patient and a Vendor with the same PK exists if you have an address with some EntityKey. So, you would have to remove these FK constraints in the DB manually (which feels very hacky to me).

• And the last thing is that you cannot specify a filter for lazy and eager loading of navigation properties. The Addresses collection would always get populated with the addresses which have the same EntityKey as the PK of Patient or Vendor respectively. You can apply a filter though with explicite loading:

  var patient = context.Patients.Single(p => p.Id == 1);
  context.Entry(patient).Collection(p => p.Addresses).Query()
      .Where(a => a.Addresstype.EntityId == patientTypeEntityId)
      .Load();
  

But you would have to ensure that you never use lazy or eager loading for the Addresses collection. So, this is not really a solution and we should forget it immediately.

The ugliest point for me is that you cannot have FK constraints on the EntityKey. In other words: The DB allows to have an EntityKey = 1 with no referenced Patient or Vendor with that PK (because somehow the patient 1 and vendor 1 have been deleted, for example).

For this reason alone I would prefer the solution shown by @Akash - aside from the fact that it is probably the only working and clean solution with EF at all.