Entity Framework Error "Invalid Column Name" when fetching from specific table

Question

I have the following line of code in my EF6 (code-first) app:

var priceDetail = context.PriceLists.Include(pl => pl.PriceDetails).FirstOrDefault();

My goal is to retrieve the first PriceList object, which contains a collection of PriceDetails.

When that line executes, I receive this error:

-- Failed in 12 ms with error: Invalid column name 'Order_OrderNumber'.

This is very strange to me because neither the PriceList nor the PriceDetail objects reference the Order object. Here is the PriceList object:

[Table( "dbo.PriceList" )]
public class PriceList
{
    public int PriceListId { get; set; }

    [StringLength( 150 )]
    [Display( Name = "PriceList name" )]
    public string PriceListDesc { get; set; }

    public virtual ICollection<PriceListDetail> PriceDetails { get; set; }
}

And here is the PriceDetail object:

[Table( "dbo.PriceDetail" )]
public class PriceDetail
{
    [ColumnDesc( "Primary key" )]
    public int PriceDetailId { get; set; }

    [ForeignKey( "PriceList" )]
    public int PriceListId { get; set; }
    public PriceList PriceList { get; set; }

    [ForeignKey( "Item" )]
    public int ItemNumber { get; set; }
    public Item Item { get; set; }

    public Decimal Price { get; set; }

    public Decimal? CostMultiplier { get; set; }
}

The full SQL query that EF generates is as follows:

SELECT 
    [Project1].[PriceListId] AS [PriceListId], 
    [Project1].[PriceListDesc] AS [PriceListDesc], 
    [Project1].[C1] AS [C1], 
    [Project1].[PriceDetailId] AS [PriceDetailId], 
    [Project1].[PriceListId1] AS [PriceListId1], 
    [Project1].[ItemNumber] AS [ItemNumber], 
    [Project1].[PriceList] AS [PriceList], 
    [Project1].[Order_OrderNumber] AS [Order_OrderNumber]
    FROM ( SELECT 
        [Limit1].[PriceListId] AS [PriceListId], 
        [Limit1].[PriceListDesc] AS [PriceListDesc], 
        [Extent2].[PriceDetailId] AS [PriceDetailId], 
        [Extent2].[PriceListId] AS [PriceListId1], 
        [Extent2].[ItemNumber] AS [ItemNumber], 
        [Extent2].[PriceList] AS [PriceList], 
        [Extent2].[Order_OrderNumber] AS [Order_OrderNumber], 
        CASE WHEN ([Extent2].[PriceDetailId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM   (SELECT TOP (1) [c].[PriceListId] AS [PriceListId], [c].[PriceListDesc] AS [PriceListDesc]
            FROM [Admin].[PriceList] AS [c] ) AS [Limit1]
        LEFT OUTER JOIN [Admin].[PriceDetail] AS [Extent2] ON [Limit1].[PriceListId] = [Extent2].[PriceListId]
    )  AS [Project1]
    ORDER BY [Project1].[PriceListId] ASC, [Project1].[C1] ASC

Why am I getting this error?

Did the property used to exist, you have changed it and forgot to migrate the database? — Old fart
– Old fart, Commented Apr 5, 2016 at 22:09
The item doesn't reference the Order at all; I've included all the code above. — Daniel
– Daniel, Commented Apr 5, 2016 at 22:32
No, the property didn't used to exist; it is the same setup. However, what if it did? Is there a configuration file somewhere I would need to update? If so I'd still want to check it. However I did a full-solution search for Order_OrderNumber and got no PriceList-related results. — Daniel
– Daniel, Commented Apr 5, 2016 at 22:33
Post the full stack trace. Obviously you are doing something else that you are not aware of. — E-Bat
– E-Bat, Commented Apr 6, 2016 at 2:13

Daniel · Accepted Answer · 2016-04-07 23:03:32Z

1

I finally figured this out. In my Order object, I had the following line of code:

public ICollection<PriceDetail> PriceDetails => AssignedLocation?.LocationDetail?.PriceList?.PriceDetails;

This was purely an accessor for convenience so the order object could get to the sub-sub-sub-class more easily.

For some reason I do not fully understand, Entity Framework interpreted this to mean that there was some relationship between the PriceDetail and Order tables, resulting in the query above.

The resolution was embarrassingly simple:

[NotMapped]
public ICollection<PriceDetail> PriceDetails => AssignedLocation?.LocationDetail?.PriceList?.PriceDetails;

Adding this attribute was sufficient to un-confuse Entity Framework.

answered Apr 7, 2016 at 23:03

Daniel

1,8571 gold badge17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Entity Framework Error "Invalid Column Name" when fetching from specific table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related