CODEWITHSUNDEEP
c#entity-framework
Kaj Nelissen
Kaj Nelissen

Reputation: 925

Eager load related entity without explicit foreign key in entity framework

I'm working on a project involving a legacy database schema and I'm running into problems when trying to load a related entity with Entity Framework (v6). Here is a simplified example of the data model that is being used:

public partial class Product {
    public virtual Delivery Delivery { get; set; }
    public virtual string OrderNumber { get; set; }
    public virtual int? VersionNumber { get; set; }
    public virtual string SocialNumber { get; set; }
}

public partial class Delivery {
    public virtual string OrderNumber { get; set; }
    public virtual int? VersionNumber { get; set; }
    public virtual string SocialNumber { get; set; }
}

What I'm trying to achieve is to include the related Delivery when retrieving a list of Products with a given SocialNumber. A Delivery is related to a Product when the following condition is satisfied: product.OrderNumber == delivery.OrderNumber && product.VersionNumber == delivery.OrderNumber && product.SocialNumber == delivery.SocialNumber.

The easiest way to do this would be calling Include. However, that seems impossible since EF states that product.Delivery is not a valid navigation property. I've tried to get the wanted result by using a projection, but failed. I'd be thankful if anyone could explain the best EF/LINQ approach to get a list of Products (+ their related Delivery) with a given SocialNumber.

Update: I think I should elaborate some more.

The main issue is that I'm dealing with a legacy database that does not have explicit (composite) keys on every table. For example, the Delivery and Product classes are mapped to tables that don't have a primary key. Since EF needs a primary key, dummy keys are defined as follows:

dbModelBuilder.Entity<Product>().hasKey(pk => new { pk.OrderNumber });

The entity classes are generated by a code generator, so adding annotations there isn't the solution I'm looking for. I guess it should be possible to define these things via the DbModelBuilder, but I'm not sure how to go about it.

Update: Figured out a solution.

The following query functions as expected (only showing the LINQ-query that should provide pairs of Product and related Delivery):

from data in _dartz3Context.V_VOV_GBA_DATAs
join delivery in _dartz3Context.VOV_GBA_AANLEVERINGs
    on new { Bsn = data.MunicipalBasicAdministrationSocialSecurityNumber, Version = data.VersionNumber, PolicyNumber = data.InsurancePolicyNumber } 
    equals new { Bsn = delivery.MunicipalBasicAdministrationSocialSecurityNumber, Version = delivery.VersionNumber, PolicyNumber = delivery.InsurancePolicyNumber }
where data.MunicipalBasicAdministrationSocialSecurityNumber == socialSecurityNumber
select new { Data = data, Delivery = delivery }

Upvotes: 3

Views: 2650

Answers (1)

Steve Greene
Steve Greene

Reputation: 12304

You need to define a composite key on your entities using annotations or the fluent api:

public partial class Product {
    [Key, Column(Order = 0)]
    public virtual string OrderNumber { get; set; }
    [Key, Column(Order = 1)]
    public virtual int? VersionNumber { get; set; }
    [Key, Column(Order = 2)]
    public virtual string SocialNumber { get; set; }
}

public partial class Delivery {
    [Key, Column(Order = 0), ForeignKey("Product")]
    public virtual string OrderNumber { get; set; }
    [Key, Column(Order = 1), ForeignKey("Product")]
    public virtual int? VersionNumber { get; set; }
    [Key, Column(Order = 2), ForeignKey("Product")]
    public virtual string SocialNumber { get; set; }
}

Example http://www.codeproject.com/Articles/813912/Create-Primary-Key-using-Entity-Framework-Code-Fir

Fluent API Version:

// Composite primary key 
modelBuilder.Entity<Product>().HasKey(d => new { d.OrderNumber, d.VersionNumber, d.SocialNumber }); 

// Composite foreign key 
modelBuilder.Entity<Delivery>()  
    .HasRequired(c => c.Product)  
    .WithMany(d => d.Delivery) 
    .HasForeignKey(d => new { d.OrderNumber, d.VersionNumber, d.SocialNumber });

Upvotes: 1

Related Questions