Include not working with Entity Framework query

Question

I'm not sure what changed, but, after coming back to an application I was working on a few weeks ago, my .Include() call is no longer working for for one of my related tables. The weird part is that it works for a different table. Here is some code with comments showing what my results are:

//Get the order and nothing else.
using (OrderEntity orderContext = new OrderEntity(OrdersConnectionString)) {
    var query = from order in orderContext.ShippingOrders
                where order.ShipperId == shippingId
                select order;

    //I got a value!
    shippingOrder = query.ToList().FirstOrDefault();
}

//Get the line item and nothing else.
using (OrderEntity orderContext = new OrderEntity(OrdersConnectionString)) {
    var query = from orderItem in orderContext.ShippingOrderItems
                where orderItem.ShipperId == shippingId
                select orderItem;

    //I got a value!
    shippingOrderItems = query.ToList();
}

Here is where I am confused:

//Get the order *AND* the line item
using (OrderEntity orderContext = new OrderEntity(OrdersConnectionString)) {
    var query = from order in orderContext.ShippingOrders.Include("ShippingOrderItems")
                where order.ShipperId == shippingId
                select order;

    //I get a ShippingOrder result, but no items are returned.  I used the SQL Server Profiler and saw the SQL that got executed; it contains the item, EF just isn't loading the object.
    shippingOrder = query.ToList().FirstOrDefault();
}

I am able to get back results for a different related table. This makes me think that the EF is missing the relationship between my order and line item table, but, I'm not sure how I can fix that.

Edit: Here is the Order Entity

/// <summary>
/// No Metadata Documentation available.
/// </summary>
[EdmEntityTypeAttribute(NamespaceName="OrderModel", Name="ShippingOrder")]
[Serializable()]
[DataContractAttribute(IsReference=true)]
public partial class ShippingOrder : EntityObject
{
    #region Factory Method

    /// <summary>
    /// Create a new ShippingOrder object.
    /// </summary>
    /// <param name="shipperId">Initial value of the ShipperId property.</param>
    public static ShippingOrder CreateShippingOrder(global::System.String shipperId)
    {
        ShippingOrder shippingOrder = new ShippingOrder();
        shippingOrder.ShipperId = shipperId;
        return shippingOrder;
    }

    #endregion
    #region Primitive Properties

    /// <summary>
    /// No Metadata Documentation available.
    /// </summary>
    [EdmScalarPropertyAttribute(EntityKeyProperty=true, IsNullable=false)]
    [DataMemberAttribute()]
    public global::System.String ShipperId
    {
        get
        {
            return _ShipperId;
        }
        set
        {
            if (_ShipperId != value)
            {
                OnShipperIdChanging(value);
                ReportPropertyChanging("ShipperId");
                _ShipperId = StructuralObject.SetValidValue(value, false);
                ReportPropertyChanged("ShipperId");
                OnShipperIdChanged();
            }
        }
    }
    private global::System.String _ShipperId;
    partial void OnShipperIdChanging(global::System.String value);
    partial void OnShipperIdChanged();

    /// <summary>
    /// No Metadata Documentation available.
    /// </summary>
    [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
    [DataMemberAttribute()]
    public global::System.String OrderNumber
    {
        get
        {
            return _OrderNumber;
        }
        set
        {
            OnOrderNumberChanging(value);
            ReportPropertyChanging("OrderNumber");
            _OrderNumber = StructuralObject.SetValidValue(value, true);
            ReportPropertyChanged("OrderNumber");
            OnOrderNumberChanged();
        }
    }
    private global::System.String _OrderNumber;
    partial void OnOrderNumberChanging(global::System.String value);
    partial void OnOrderNumberChanged();

    /// <summary>
    /// No Metadata Documentation available.
    /// </summary>
    [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
    [DataMemberAttribute()]
    public Nullable<global::System.DateTime> OrderDate
    {
        get
        {
            return _OrderDate;
        }
        set
        {
            OnOrderDateChanging(value);
            ReportPropertyChanging("OrderDate");
            _OrderDate = StructuralObject.SetValidValue(value);
            ReportPropertyChanged("OrderDate");
            OnOrderDateChanged();
        }
    }
    private Nullable<global::System.DateTime> _OrderDate;
    partial void OnOrderDateChanging(Nullable<global::System.DateTime> value);
    partial void OnOrderDateChanged();

    /// <summary>
    /// No Metadata Documentation available.
    /// </summary>
    [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
    [DataMemberAttribute()]
    public global::System.String CustomsComment
    {
        get
        {
            return _CustomsComment;
        }
        set
        {
            OnCustomsCommentChanging(value);
            ReportPropertyChanging("CustomsComment");
            _CustomsComment = StructuralObject.SetValidValue(value, true);
            ReportPropertyChanged("CustomsComment");
            OnCustomsCommentChanged();
        }
    }
    private global::System.String _CustomsComment;
    partial void OnCustomsCommentChanging(global::System.String value);
    partial void OnCustomsCommentChanged();

    #endregion

    #region Navigation Properties

    /// <summary>
    /// No Metadata Documentation available.
    /// </summary>
    [XmlIgnoreAttribute()]
    [SoapIgnoreAttribute()]
    [DataMemberAttribute()]
    [EdmRelationshipNavigationPropertyAttribute("OrderModel", "FK_ShippingOrderItem_ShippingOrder", "ShippingOrderItem")]
    public EntityCollection<ShippingOrderItem> ShippingOrderItems
    {
        get
        {
            return ((IEntityWithRelationships)this).RelationshipManager.GetRelatedCollection<ShippingOrderItem>("OrderModel.FK_ShippingOrderItem_ShippingOrder", "ShippingOrderItem");
        }
        set
        {
            if ((value != null))
            {
                ((IEntityWithRelationships)this).RelationshipManager.InitializeRelatedCollection<ShippingOrderItem>("OrderModel.FK_ShippingOrderItem_ShippingOrder", "ShippingOrderItem", value);
            }
        }
    }

    /// <summary>
    /// No Metadata Documentation available.
    /// </summary>
    [XmlIgnoreAttribute()]
    [SoapIgnoreAttribute()]
    [DataMemberAttribute()]
    [EdmRelationshipNavigationPropertyAttribute("OrderModel", "FK_ShippingOrderItemTracking_ShippingOrder", "ShippingOrderTracking")]
    public EntityCollection<ShippingOrderTracking> ShippingOrderTrackings
    {
        get
        {
            return ((IEntityWithRelationships)this).RelationshipManager.GetRelatedCollection<ShippingOrderTracking>("OrderModel.FK_ShippingOrderItemTracking_ShippingOrder", "ShippingOrderTracking");
        }
        set
        {
            if ((value != null))
            {
                ((IEntityWithRelationships)this).RelationshipManager.InitializeRelatedCollection<ShippingOrderTracking>("OrderModel.FK_ShippingOrderItemTracking_ShippingOrder", "ShippingOrderTracking", value);
            }
        }
    }

    #endregion
}

Long shot... have you got an empty constructor on your ShippingOrderItem(s) class? — Phil, Jul 16 '12 at 14:23
Can you post the ShippingOrder entity? What is the name of the property (and configuration) of the ShippingOrderItem? — Davin Tryon, Jul 16 '12 at 14:24
@PhilCartmell - Just an FYI: I am using the auto-generated code (database-first). I looked at the class and there is no constructor specified, so, C# magic is leaving me with an empty constructor. — Justin Helgerson, Jul 16 '12 at 14:27
@dtryon - I posted the class for the order entity (I did removed some properties for brevity; the navigational properties are all there). — Justin Helgerson, Jul 16 '12 at 14:34
If you put a try{} catch (Exception e) {} around shippingOrder = query.ToList().FirstOrDefault(); maybe its throwing some sort of exception? — Phil, Jul 16 '12 at 14:34
Have you used something like http://efprof.com to inspect the actual SQL that is being generated? Wild guess, something changed on the database side so your queries aren't executing the way you think they should be. — CodingGorilla, Jul 16 '12 at 14:36
@CodingGorilla - I have used SQL Server Profiler to look at the SQL that is being executed. While it looks ugly, it does execute without any errors. The data for my line-item is within the resultset. — Justin Helgerson, Jul 16 '12 at 14:49
So then are you saying that your `Order.LineItems` collection is empty? — CodingGorilla, Jul 16 '12 at 14:55
@CodingGorilla - Yes. I get an order returned, it's the line items that are not. Sorry if I was not clear enough with that. — Justin Helgerson, Jul 16 '12 at 14:59
Have you tried just refreshing the model from the database to make sure something on the database side hasn't changed? — CodingGorilla, Jul 16 '12 at 15:07
@CodingGorilla - I did refresh the model. I was hoping that would fix it, but, no dice. I'm really confused why the actual SQL that EF is executing shows the results I need for the line-items, but, it's just not populating the navigational property. — Justin Helgerson, Jul 16 '12 at 15:09
Yea, I'm out of ideas. You might have to contact MS support on this one. — CodingGorilla, Jul 16 '12 at 15:09

score 1 · Accepted Answer · answered Jul 16 '12 at 19:44

1

I'm still not sure what the cause of the issue was. I decided to just drop my database and re-create my tables (along with the primary and foreign keys) and migrate over all the data again. It actually fixed it, but, I'm not sure what ended up being different. I was not getting any exceptions logged and based on the SQL Server Profiler it looked like the correct query was being executed.

answered Jul 16 '12 at 19:44

Justin Helgerson

24,900
17
97
124

@ShadowWizard - I meant to come back and accept this as an answer. Thanks. – Justin Helgerson Aug 28 '12 at 13:38
Lol.. well, better late than never! :) – Shadow The GPT Wizard Aug 28 '12 at 13:45

score 1 · Answer 2 · edited Aug 27 '12 at 06:52

1

You can also try this:

if (Order.shippingId != null && Order.shippingId > 0)
    orderContext.LoadProperty(Orders, Order => Order.ShippingOrderItems);

This will fetch the matching order id in ShippingOrderItems.

edited Aug 27 '12 at 06:52

Shadow The GPT Wizard

66,030
26
140
208

answered Aug 27 '12 at 06:50

Subbu

46
4

Include not working with Entity Framework query

2 Answers2