实体框架与过滤器关联

发布于 2024-12-09 08:48:28 字数 1385 浏览 0 评论 0原文

我的模型中有以下模型:

Patient

Vendor

Organization

每个实体都需要地址。

地址基本上如下所示,

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 AddressTypes are available to create for new addresses for Patients, Vendors, and Organizations
  //...that is Patients support AddressType X, Vendors support AddressType Y, etc.

我想在地址的 EntityKey 属性上创建患者、供应商和组织的关联 - 每个关联都有一个过滤器约束,即地址的 AddressType.EntityId 是该实体的匹配 EntityId(1 表示患者, 2 代表供应商,3 代表地址)。

这样做的最佳方法是什么?市场上的大多数 ORM 都支持这种情况……而且这肯定是一种非常常见的情况。

注意:我不想创建 PatientAddress/PatientAddressType、VendorAddress/VendorAddressType 和 OrganizationAddress/OrganizationAddress 类型派生实体。它严重扰乱了模型并使其基本上难以理解。

现在,我正在通过在 LINQ 查询中执行显式联接来解决这个问题:

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 }

但我不想继续这样做。

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 AddressTypes 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.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

情栀口红 2024-12-16 08:48:28

如果我理解正确的话,您希望在您的 PatientVendor 等中拥有一个地址集合......

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; }
}

并以某种方式告诉 EF Patient.Addresses< /code> 仅填充地址类型为“Patient”的地址。

我认为这是不可能的,原因如下:

  • 如果您没有在 Address 中公开外键(那里没有 EntityKey 属性),您必须告诉 EF在映射中键入密钥(否则它将创建/假设两个不同的 FK 列):

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

由于两个不同关系的重复“EntityKey”列,这会引发异常。

  • 接下来我们可以尝试的是将外键公开为 Address 中的属性(EntityKey 属性在那里),然后使用此映射:

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

: (令人惊讶的是)不会抛出异常,而是在数据库中的 Patient-AddressVendor-Address< 之间创建两个 FK 约束/code> 具有相同的 FK 列EntityKey。对于您的模型,我认为这没有意义,因为它要求始终存在具有相同 PK 的 Patient Vendor如果您的地址包含一些 EntityKey。因此,您必须手动删除数据库中的这些 FK 约束(这对我来说感觉非常 hacky)。

  • 最后一件事是,您无法为导航属性的延迟加载和急切加载指定过滤器。 Addresses 集合将始终填充与 PatientVendor 的 PK 具有相同 EntityKey 的地址分别。您可以通过显式加载来应用过滤器:

    var Patient = context.Patients.Single(p => p.Id == 1);
    context.Entry(患者).Collection(p => p.Addresses).Query()
        .Where(a => a.Addresstype.EntityId == PatientTypeEntityId)
        。加载();
    

但是您必须确保永远不会对 Addresses 集合使用延迟加载或急切加载。所以,这并不是真正的解决方案,我们应该立即忘记它。

对我来说最丑陋的一点是你不能对 EntityKey 进行 FK 约束。换句话说:数据库允许 EntityKey = 1,而没有引用该 PK 的 PatientVendor(因为不知何故,患者 1 和供应商 1 已被删除,对于例子)。

仅出于这个原因,我更喜欢 @Akash 所示的解决方案 - 除了它可能是 EF 唯一有效且干净的解决方案这一事实之外。

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.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文