CODEWITHSUNDEEP
c#sqlentity-frameworklinq-to-sql
tsohtan
tsohtan

Reputation: 850

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

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

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

Upvotes: 0

Views: 1084

Answers (1)

markpsmith
markpsmith

Reputation: 4918

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

Upvotes: 1

Related Questions