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.
l.code.Substring(0, 2) == culture
andb => b.language_id.Equals(l.ID)
?l.code.Substring(0, 2) == culture
withSystem.Data.Entity.DbFunctions.Left(l.code, 2) == culture
?