The entity or complex type 'xyz' cannot be constructed in a LINQ to Entities query

Question

I know this has already been debated before, and I have been carefully reading and trying all of them, but every solution returned a List while I expressly need an IQueryable to be returned in order to feed an already implemented Asynchronous paging method.

ASP NET MVC5 web application, E.F.6, Visual Studio 2015

public partial class Product
    {
        public int? ID { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public int CategoryID { get; set; }
        public virtual Category Category { get; set; }
        public virtual CategoryTrans CategoryTrans { get; set; }
    }

 public partial class Category
{
    public int ID { get; set; }
    public string Name { get; set; }
    public int? ParentID { get; set; }
    public bool IsDeleted { get; set; }
    public virtual ICollection<Product> Products { get; set; }
    public virtual ICollection<CategoryTrans> CategoryTrans { get; set; }
}

 public class ISO_Languages
{
    public int ID { get; set; }
    public string code { get; set; }
    public bool IsEnabled { get; set; }
    public string name_en { get; set; }
    public string name_fr { get; set; }
    public string name_it { get; set; }
    public string name_de { get; set; }
    public string name_es { get; set; }
}

 public class CategoryTrans
{
    [Key, Column(Order = 1)]
    public int category_id { get; set; }
    [Key, Column(Order = 2)]
    public int language_id { get; set; }
    [ForeignKey("category_id")]
    public virtual Category categoryId { get; set; }
    [ForeignKey("language_id")]
    public virtual ISO_Languages languageId { get; set; }
    public string name { get; set; }
}

Following query returns ALL CategoryTrans in p.Category.CategoryTrans, that means any category translation (that's a starting point though, NOT what I need):

public static IQueryable<Product> ActiveProductsPerUser(BaseContext db,     string userid, string culture)
{
    var query = (from p in db.Products
                 join ct in db.CategoryTrans
                 on p.CategoryID equals ct.category_id
                 join l in db.ISO_Languages
                 on ct.language_id equals l.ID
                 where l.code.Substring(0, 2) == culture
                 select p);

    return query;
}

What I am trying to do is filtering, for every product, the single category translation, depending on culture input parameter. Something like:

public static IQueryable<Product> ActiveProductsPerUser(BaseContext db, string userid, string culture)
    {
        var query = from p in db.Products
                     join ct in db.CategoryTrans
                     on p.CategoryID equals ct.category_id
                     join l in db.ISO_Languages
                     on ct.language_id equals l.ID
                     where l.code.Substring(0, 2) == culture
                     select new Product
                    {
                        ID = p.ID,
                        Name = p.Name,
                        Description = p.Description,
                        CategoryTrans = p.Category.CategoryTrans.Where(b => b.language_id.Equals(l.ID)).SingleOrDefault()
                    };
        return query;
    }

but returning error:

The entity or complex type 'xyz.DAL.Product' cannot be constructed in a LINQ to Entities query.

I have tried several alternatives but none worked. I understand the problem being returning Linq to Entity instead Linq to Object but an entity is what required, as I definitely need to return a Product IQueryable to following asynchronous method in charge for paging & sorting:

public async Task<ActionResult> UserList(int? page, int sortBy = 1)
    {
       int currentPage = (page ?? 1);
       ViewBag.CurrentPage = currentPage;
       viewModel.productList = await userlist(sortBy).ReturnPages(currentPage, Constants.PageItems);
       return View(viewModel);}

where the awaited "userlist" input is the Product IQueryable to be returned by the method I am struggling to implement.

Answer 1

I think this kind of thing would be more appropriate.

var query = db.Products
            .Where(p => p.CategoryTrans.languageId.code.StartWith(culture))
            .Include(p => p.CategoryTrans.languageId);

Answer 2

I could not find an answer to the

The entity or complex type 'xyz.DAL.Product' cannot be constructed in a LINQ to Entities query.

error. I eventually solved the problem by adding a

[NotMapped]
public virtual string LocalizedCategoryName { get; set; }

to Product model Class, in charge for displaying localized category name, and moving the filtering to the ViewModel, setting two nested foreach loops, returning a new List of fully localized products:

private List<Product> _LocalizedProductList = new List<Product>();

public List<Product> LocalizedProductList
    {
        get
        {
            HttpUtilities HttpHelper = new HttpUtilities();
            string culture = HttpHelper.getFullCulture();
            int IsoCode = GenericUtilities.getIsoID(culture, db);
            List<Product> localized = new List<Product>();

            foreach (Product p in _LocalizedProductList)
            {
                foreach (CategoryTrans c in p.Category.CategoryTrans)
                {
                    if (c.language_id.Equals(IsoCode))
                    {
                        Product x = new Product
                        {
                            ID = p.ID,
                            LocalizedCategoryName = c.name,
                            DateCreated = p.DateCreated,
                            DateExpire = p.DateExpire,
                            DateLastModified = p.DateLastModified,
                            Name = p.Name,
                            Description = p.Description,
                            IsApproved = p.IsApproved,
                            IsDeleted = p.IsDeleted,
                            ProductImages = p.ProductImages,
                            User = p.User
                        };
                        localized.Add(x);
                    };
                }
            }
            return localized;
        }

        set { _LocalizedProductList = value; }
    }

No idea if it's the best or the only way to do it, but works as intended. I can now stay with the simple query and pass the returned IQueryable of Product onto the sorting and paging async method or whatever. Whenever I am done, the result is being assigned to ViewModel.LocalizedProductList, where the getter takes care of the final filtering.