Why EF generate invalid column when i have same column name as Foreign key in child table

Question

I got these 2 models, i use "include" to get all related entities and my LINQ is look like this, when i execute it will complain -- Invalid column Cust_ProfileTbl_bintAccountNo

LINQ

DB context = new DB();
List<Cust_ProfileTbl> profile = context.profile.ToList();
var r = from ord in context.profile.Include("balance") 
        select ord;

Models

public class Cust_ProfileTbl   
{
    [Key]
    public long bintAccountNo { get; set; } 
    public virtual  BP_BalanceTbl balance { get; set; } 
} 
public class BP_BalanceTbl
{
    [Key] 
    public long bintAccountNo { get; set; } 
}

Generated SQL

SELECT  
    [Project1].[bintAccountNo] AS [bintAccountNo], 
    [Project1].[Cust_ProfileTbl_bintAccountNo] AS [Cust_ProfileTbl_bintAccountNo]-- Invalid column
    FROM ( 
        SELECT 
            [Extent1].[bintAccountNo] AS [bintAccountNo], 
            [Extent2].[Cust_ProfileTbl_bintAccountNo] AS [Cust_ProfileTbl_bintAccountNo], 
        CASE WHEN ([Extent2].[intPartner] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM  [dbo].[Cust_ProfileTbl] AS [Extent1]
        LEFT OUTER JOIN [dbo].[BP_BalanceTbl] AS [Extent2] ON [Extent1].[bintAccountNo] = [Extent2].[Cust_ProfileTbl_bintAccountNo]

    )  AS [Project1] 
ORDER BY [Project1].[bintAccountNo] ASC, [Project1].[C1] ASC

Things i tried to get rid this bug

Add 1 Identity PK column into BP_BalanceTbl, (Which i don't feel like to do)
Change to use Join method. ( but i want to know why this happened not just run away from error, and how to avoid this?)

Yes, bintAccountNo is foreign key. We separate the table (Cust_Profiletbl) into 2 tables because of too many column. So the bintAccountNo in BP_BalanceTbl we create it as PK also. — tsohtan, Jun 20 '14 at 09:32
so it's both PK and FK? is that not the cause of the problem? — markpsmith, Jun 20 '14 at 09:34
Sorry, nothing wrong - I mean I just wondered if EF having an problem creating the FK because of it. — markpsmith, Jun 20 '14 at 10:06
Yes it is obviously is cause by PK and FK column. But is there a way to solve it or i got no choice to remove the PK by adding another identity PK Column. — tsohtan, Jun 20 '14 at 10:21
There will be a solution, a quick search found this: [http://stackoverflow.com/questions/5388077/primary-foreign-key-in-entity-framework](http://stackoverflow.com/questions/5388077/primary-foreign-key-in-entity-framework) — markpsmith, Jun 20 '14 at 10:26
@markpsmith thanks, your link point me to solution. Obviously by finding solution base on error message is not ideal. ;). +1. I'll mark it as answer if you not mind put it as an answer. — tsohtan, Jun 25 '14 at 02:12

score 1 · Accepted Answer · answered Jun 25 '14 at 08:45

1

At the request of the OP, I'm posting my comment as an answer:

So the problem is based on the fact that a model property is assigned both Primary and Foreign Keys. To tell EF to create this you need to use DataAnnotations on the property:

[Key, ForeignKey("Cust_ProfileTbl")]
public long bintAccountNo { get; set; }

and also you'll probably need to reference the related entity as a virtual property:

public virtual Cust_ProfileTbl{get; set;}

answered Jun 25 '14 at 08:45

markpsmith

4,860
2
33
62

hi, should be look like this on 2nd line public virtual Cust_ProfileTbl profile { get; set; } and also this is needed for foreignkey. :) thanks. – tsohtan Jun 25 '14 at 09:11

Why EF generate invalid column when i have same column name as Foreign key in child table

1 Answers1