How to relate entities that are not directly mapped through a navigation property

Question

I have an object that has been populated with the contents of four different related entities. However i have another entity in which i cannot include as part of the query due to it not being related in the navigation properites directly to the IQueryable table i am pulling. The entity i am trying to include is related to one of the four different entities that have been included successfully.

Is there a way to include(during db hit or afterwards) this entity as part of the overall object i am creating?

Here is an example of what my calls look like to build the CARTITEM object:

    public List<CARTITEM> ListCartItem(Guid cartId)
    {
        //Create the Entity object
        List<CARTITEM> itemInfo = null;

        using (Entities webStoreContext = new Entities())
        {
                //Invoke the query
                itemInfo = WebStoreDelegates.selectCartItems.Invoke(webStoreContext).ByCartID(cartId).ToList();

        }

        //Return the result set
        return itemInfo;
    }

here is the selectCartItems filter(Where i would normally do the includes):

        public static Func<Entities, IQueryable<CARTITEM>> selectCartItems =
    CompiledQuery.Compile<Entities, IQueryable<CARTITEM>>(
        (cart) => from c in cart.CARTITEM.Include("ITEM").Include("SHIPPINGOPTION").Include("RELATEDITEM").Include("PROMOTION")
                  select c);

from this i have my CARTITEM object. Problem is i want to include the PROMOTIONTYPE table in this object, but since the CARTIEM entity doesn't have a navigation property directly to the PROMOTIONTYPE table i get an error.

Let me know if you need any more clarification. Thanks, Billy

Answer 1

You can use join and if it is the same database and server it should generate the join in SQL and do it all in one call...

LinqToEnties join example