CODEWITHSUNDEEP
c#sql-serverentity-framework
Daniel
Daniel

Reputation: 1837

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

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?

Upvotes: 0

Views: 526

Answers (1)

Daniel
Daniel

Reputation: 1837

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.

Upvotes: 1

Related Questions