Unable to Determine Principal End - Entity Framework Code First Relationship

Question

PLEASE CHECK POSSIBLE SOLUTION SECTION BELOW

I have an issue with a foreign key relationship - here are my tables:

public class Lead
{
    [Key]
    public int LeadId { get; set; }
    public int? CustomerId { get; set; }

    [ForeignKey("CustomerId")]
    public virtual Customer Customer { get; set; }
 }

public class Customer
{
    [Key]
    [Column("customer_id")]
    public int CustomerId { get; set; }

    [ForeignKey("CustomerId")]
    public virtual Lead Lead { get; set; }

}

I'm having an issue where I receive this error:

Unable to determine the principal end of an association between the types 'Sales.Customers.Customer' and 'Sales.Leads.Lead'. The principal end of this association must be explicitly configured using either the relationship fluent API or data annotations.

I've tried adding the relationship to the modelbuilder but it appears that its not working properly. When I do get the error message to go away its actually using the Lead.LeadId -> Customer.CustomerId as the relationship instead of Lead.CustomerId -> Customer.CustomerId relationship.

I've checked similar questions on Stackoverflow but they don't seem to match my DB structure and when I try to implement their suggestions the relationship still doesn't work properly.

Its really weird - would greatly appreciate help on this!

UPDATE

So in my attempt to get this relationship to work I've switched the keys around in the following way:

public class Lead
{
    [Key]
    public int LeadId { get; set; }

    [ForeignKey("LeadId")]
    public virtual Customer Customer { get; set; }
}

public class Customer
{
    [Key]
    [Column("customer_id")]
    public int CustomerId { get; set; }
    public int? LeadId { get; set; }

    [ForeignKey("LeadId")]
    public virtual Lead Lead { get; set; }
}

However, same error, still no luck - I'm really at a loss why this relationship won't work. To me it seems pretty straight forward.

UPDATE 2

Ok - after a TON of wasted time messing with this I've tried a slightly different approach:

Here are my new classes....

    public class Lead
    {
        [Key, ForeignKey("Customer")]
        public int LeadId { get; set; }
        public virtual Customer Customer { get; set; }
    }

    public class Customer
    {
        [Key]
        [Column("customer_id")]
        public int CustomerId { get; set; }
        public int? LeadId { get; set; }
        public virtual Lead Lead { get; set; }
     }

No more error messages with the code above! The only problem is that the relationship entity framework is creating is between the Customer.CustomerId and Lead.LeadId instead of the Customer.LeadId and Lead.LeadId - I feel like i'm SO CLOSE!

POSSIBLE SOLUTION

Ok - so after some more research I came across this post here: EF Code First - 1-to-1 Optional Relationship

I modified my classes to this:

public class Customer
{
    [Key]
    [Column("customer_id")]
    public int CustomerId { get; set; }

    public virtual Lead Lead { get; set; }
}

public class Lead
{
    [Key]
    public int LeadId { get; set; }
    public virtual Customer Customer { get; set; }
 }

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Customer>().HasOptional<Lead>(l => l.Lead).WithOptionalDependent(c => c.Customer).Map(p => p.MapKey("LeadId"));
    base.OnModelCreating(modelBuilder);
}

Everything works GREAT! But one BIG problem...

I had to remove the LeadId property from the Customer Table.... so now I'm not sure how I can assign a LeadId when creating a new Customer (when appropriate) if there is no LeadId property to assign to?

Just to be sure before I dig deeper, are you intending that this be a one-to-one relationship between Customer and Lead? — Tim, Jul 15 '13 at 16:04
Well... yea - that is correct - however, there isn't a Lead required for a Customer - a customer can be entered into the system without being a lead first - know what i mean? also - there can be a lead without a customer, as in a lead that doesn't convert into a customer — 99823, Jul 15 '13 at 16:09
I gotcha. Just wanted to make sure you weren't going for a one-to-many. Because that's a different beast. Lemme look... — Tim, Jul 15 '13 at 16:13
I've tried so many variations on Fluent API but none seem to work. When the error does go away the relationship is associating the LeadId to the CustomerId and not the LeadId to the LeadId - its very frustrating. — 99823, Jul 16 '13 at 12:22
Also - I don't see whats wrong w/ my code above? Is using the ForeignKey attributes the equivalent of the fluent API anyways? Its very frustrating, I've never had such a hard time w/ something like this before in Code First. — 99823, Jul 16 '13 at 12:23
Also @SOfanatic - if its fairly straight forward - do you mind providing some sample code? — 99823, Jul 16 '13 at 12:36
@Loren yes I added it to the answer in case you want to use it, I prefer fluent API to annotations, just saying this cause I have no idea how this could be accomplished using annotations. — SOfanatic, Jul 16 '13 at 12:37
Yes - there is, however, if I add the Property manually into the Customer class it errors out because its using the LeadId in the modelbuilder when calling the MapKey method - SOfanatic.. thanks again man, I owe you a beer or something.... — 99823, Jul 16 '13 at 19:26
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/33587/discussion-between-sofanatic-and-loren) — SOfanatic, Jul 16 '13 at 19:44
possible duplicate of [What does principal end of an association means in 1:1 relationship in Entity framework](http://stackoverflow.com/questions/6531671/what-does-principal-end-of-an-association-means-in-11-relationship-in-entity-fr) — Chris Moschini, Oct 01 '13 at 01:58

SOfanatic · Answer 1 · 2013-07-16T14:23:33.157

2

Posting this in fluent API, it should work.

public class Lead
{
    [Key]
    public int LeadId { get; set; }

    public virtual Customer Customer { get; set; }
 }

public class Customer
{
    [Key]
    [Column("customer_id")]
    public int CustomerId { get; set; }

    public virtual Lead Lead { get; set; }
}

builder.Entity<Lead>()
.HasOptional(l => l.Customer)
.WithOptionalPrincipal()
.Map(k => k.MapKey("LeadId"));

builder.Entity<Customer>()
.HasOptional(c => c.Lead)
.WithOptionalPrincipal()
.Map(k => k.MapKey("CustomerId"));

EDIT

Tables

edited Jul 16 '13 at 14:23

answered Jul 16 '13 at 12:37

SOfanatic

5,523
5
36
57

Ok - so I'm getting a new error: "Unknown column 'Extent10.LeadId' in 'field list" - it appears to be creating a new property based upon the modelbuilder code on the Customer Table... its trying to select LeadId from the customer table when lead_id is the actual column name in that table.. its weird.. – 99823 Jul 16 '13 at 13:09
`Extent10`.`customer_id`, `Extent10`.`lead_id`, `Extent10`.`company_name`, `Extent10`.`primary_customer_account_id`, `Extent10`.`website_address`, `Extent10`.`Status`, `Extent10`.`investigation_criteria`, `Extent10`.`administrative_criteria`, `Extent10`.`credit_hold`, `Extent10`.`paperwork_on_file`, `Extent10`.`gets_partial`, `Extent10`.`create_datetime`, `Extent10`.`edit_datetime`, `Extent10`.`LeadId` <- this column does not exist – 99823 Jul 16 '13 at 13:10
@Loren did you change your models to look like the ones in the answer? – SOfanatic Jul 16 '13 at 13:13
Yea - i'm changing some things in the code running into some issues - i modified the original to be this: – 99823 Jul 16 '13 at 13:39
modelBuilder.Entity() .HasOptional(l => l.Customer) .WithOptionalPrincipal() .Map(k => k.MapKey("lead_id")); modelBuilder.Entity() .HasOptional(c => c.Lead) .WithOptionalPrincipal() .Map(k => k.MapKey("LeadId")); – 99823 Jul 16 '13 at 13:40
I've been tweaking the code to get it to work because it didn't quite like the code listed above - I seem to be getting this error: (46,6) : error 0019: Each property name in a type must be unique. Property name 'LeadId' was already defined. I'm still messing with it now trying to get the right fit – 99823 Jul 16 '13 at 13:43
Hey 1 quick question - when you call k.MapKey - should that be referencing the matching keys - for example LeadId -> LeadId on each table? – 99823 Jul 16 '13 at 13:58
@Loren yes, so for example the answer I posted creates what's in my edit. – SOfanatic Jul 16 '13 at 14:22
Hi SOfanatic, I really appreciate your time with this, unfortunately I cannot get this to work no matter how I try it. I've used the original code, modified it, etc etc but I keep running into errors. – 99823 Jul 16 '13 at 14:50

Unable to Determine Principal End - Entity Framework Code First Relationship

1 Answers1

Linked