实体框架4-连接2个表然后对其进行分页的语法是什么? [英] Entity Framework 4 - What is the syntax for joining 2 tables then paging them?
问题描述
我有以下带有2个联接表的linq-to-entities查询,我想向其中添加分页:
I have the following linq-to-entities query with 2 joined tables that I would like to add pagination to:
IQueryable<ProductInventory> data = from inventory in objContext.ProductInventory
join variant in objContext.Variants
on inventory.VariantId equals variant.id
where inventory.ProductId == productId
where inventory.StoreId == storeId
orderby variant.SortOrder
select inventory;
我意识到我需要使用.Join()扩展方法,然后调用.OrderBy().Skip().Take()来执行此操作,我只是迷上了Join()的语法,并且可以似乎找不到任何示例(在线或书籍中).
I realize I need to use the .Join() extension method and then call .OrderBy().Skip().Take() to do this, I am just gettting tripped up on the syntax of Join() and can't seem to find any examples (either online or in books).
注意::我加入表格的原因是为了进行排序.如果有一种比关联表更好的基于相关表中的值进行排序的方法,请将其包括在您的答案中.
NOTE: The reason I am joining the tables is to do the sorting. If there is a better way to sort based on a value in a related table than join, please include it in your answer.
2种可能的解决方案
我想这只是一个可读性问题,但是这两个都可以工作并且在语义上是相同的.
I guess this one is just a matter of readability, but both of these will work and are semantically identical.
IQueryable<ProductInventory> data = objContext.ProductInventory
.Where(y => y.ProductId == productId)
.Where(y => y.StoreId == storeId)
.Join(objContext.Variants,
pi => pi.VariantId,
v => v.id,
(pi, v) => new { Inventory = pi, Variant = v })
.OrderBy(y => y.Variant.SortOrder)
.Skip(skip)
.Take(take)
.Select(x => x.Inventory);
2
var query = from inventory in objContext.ProductInventory
where inventory.ProductId == productId
where inventory.StoreId == storeId
join variant in objContext.Variants
on inventory.VariantId equals variant.id
orderby variant.SortOrder
select inventory;
var paged = query.Skip(skip).Take(take);
对Khumesh和Pravin的帮助表示感谢.感谢其余的贡献.
Kudos to Khumesh and Pravin for helping with this. Thanks to the rest for contributing.
推荐答案
在查询中添加以下行
var pagedQuery = data.Skip(PageIndex * PageSize).Take(PageSize);
数据变量是IQueryable,因此您可以添加add skip&采取方法.并且如果您在Product&变体,您实际上并不需要显式加入,您可以引用类似这样的变体
The data variable is IQueryable, so you can put add skip & take method on it. And if you have relationship between Product & Variant, you donot really require to have join explicitly, you can refer the variant something like this
IQueryable<ProductInventory> data =
from inventory in objContext.ProductInventory
where inventory.ProductId == productId && inventory.StoreId == storeId
orderby inventory.variant.SortOrder
select new()
{
property1 = inventory.Variant.VariantId,
//rest of the properties go here
}
pagedQuery = data.Skip(PageIndex * PageSize).Take(PageSize);
这篇关于实体框架4-连接2个表然后对其进行分页的语法是什么?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!