In my demo, I have three table in database, TProduct, TCategory and TProductCategoryMap.
TProduct (ProductId int PK, OtherFields)
TCategory (CategoryId int PK, OtherFields)
TMap (ProductId int PK, CategoryId int PK)
Now, I need to get a PagedList of Products with specific categoryid.
Here is my code:
IQueryable<Product> products = from product in _repo.All<TProduct>()
join map in _repo.All<TMap>() on product.ProductId equals map.ProductId
where map.CategoryId == specificCagetoryId
select product;
If I stop here and return products, everything is ok. But If I return a pagedlist like this:
return new PagedList<TProduct>(products, pageIndex, pageSize);
the generated sql text will cause a syntax error "The ranking function "ROW_NUMBER" must have an ORDER BY clause. "
Do I use the wrong linq expression? Then how can I get a correct result?
Give me some advice, thank you.