Linq: the linked objects are null, why?

Question

Hello, I have several linked tables (entities). I'm trying to get the entities using the following linq:

ObjectQuery<Location> locations = context.Location;
ObjectQuery<ProductPrice> productPrice = context.ProductPrice;
ObjectQuery<Product> products = context.Product;
IQueryable<ProductPrice> res1 = from pp in productPrice
                    join loc in locations
                    on pp.Location equals loc
                    join prod in products
                    on pp.Product equals prod
                    where prod.Title.ToLower().IndexOf(Word.ToLower()) > -1
                    select pp;

This query returns 2 records, ProductPrice objects that have linked object Location and Product but they are null and I cannot understand why. If I try to fill them in the linq as below:

res =
                from pp in productPrice
                join loc in locations
                on pp.Location equals loc
                join prod in products
                on pp.Product equals prod
                where prod.Title.ToLower().IndexOf(Word.ToLower()) > -1
                select new ProductPrice
                {
                    ProductPriceId = pp.ProductPriceId,
                    Product = prod
                };

I have the exception "The entity or complex type 'PBExplorerData.ProductPrice' cannot be constructed in a LINQ to Entities query" Could someone please explain me what happens and what I need to do? Thanks

Answer 1

The answer to your first question the Product and Location are null because you need to add an Include("") to your query.

  IQueryable<ProductPrice> res1 = from pp in 
                productPrice.Include("Location").Include("Product")
                  join loc in locations
                    on pp.Location equals loc
                  join prod in products
                    on pp.Product equals prod
                  where prod.Title.ToLower().IndexOf(Word.ToLower()) > -1
                select pp;

The second issue is EF is trying to push down your query and ProductPrice (is not an entity) so it can not. If you want to do this convert it to an anonymous type so just do

            select new 
            {
                ProductPriceId = pp.ProductPriceId,
                Product = prod
            };

And then do

          res.ToList().ConvertAll(x=new ProductPrice () {
               ProductPriceId  = x.ProductPriceId ,
               Product  = x.Product

           });

Or you could do it other ways, by selecting the entity you want, and just populating manual.