实体框架分页联接和联合？

Question

我正在为具有以下结构的数据库开发一个新的 Web 界面：

Categories Table (ID, Name, Active, ...)
Products Table (ID, Name, ItemNo, Price, Active, ...)
WebPages Table (ID, Name, Active, ...)
Links Table (ID, Name, Href, Target, Active, ...)
Relations Table (ID, ParentCategoryID, CmsForeignKeyID, CmsItemType, SortOrder, Visible)

关系表用于建立父类别与其子类别之间的关系，这些子类别可以是类别、产品、网页或链接。

我需要能够获取给定父类别的子节点的分页列表。节点必须是活动的、可见的且已排序（默认排序顺序，然后按名称排序）。

我尝试使用 EF/LINQ 做一些事情，但由于需要多个条件（例如：R.CmsForeignKeyID = N.ID，R.CmsItemType = 1），所以在 JOINing 关系方面遇到了问题，所以我什至没有进行联合和分页。

在这种情况下，最好放弃 EF 作为工具，而使用传统 SQL 来实现，例如：

SELECT C.CategoryID As ID, R.CmsItemTypeID, C.Name, R.SortOrder, '' AS Href, '' As LinkTarget, null As MSRP, null As ListPrice, null As Price
FROM kc_Relations R
JOIN kc_Categories C ON R.CmsItemID = C.CategoryID AND R.CmsItemTypeID = 1
WHERE C.IsActive = 1 AND R.IsVisible = 1
UNION
SELECT W.WebPageID As ID, R.CmsItemTypeID, W.Name, R.SortOrder, '' AS Href, '' As LinkTarget, null AS MSRP, null As ListPrice, null As Price
FROM kc_Relations R
JOIN kc_WebPages W ON R.CmsItemID = W.WebPageID AND R.CmsItemTypeID = 0
WHERE W.IsActive = 1 AND R.IsVisible = 1
UNION
SELECT P.ProductID As ID, R.CmsItemTypeID, P.Name, R.SortOrder, '' AS Href, '' As LinkTarget, P.MSRP, P.ListPrice, P.Price
FROM kc_Relations R
JOIN kc_Products P ON R.CmsItemID = P.ProductID AND R.CmsItemTypeID = 2
WHERE P.IsActive = 1 AND R.IsVisible = 1
UNION
SELECT L.LinkID As ID, R.CmsItemTypeID, L.Name, R.SortOrder, L.Href, L.Target As LinkTarget, null As MSRP, null AS ListPrice, null AS Price
FROM kc_Relations R
JOIN kc_Links L ON R.CmsItemID = L.LinkID AND R.CmsItemTypeID = 3
WHERE L.IsActive = 1 AND R.IsVisible = 1

然后对结果集执行分页逻辑？或者是否有更好的方法来构建数据库以使这变得更容易？

Answer 1

我对观点的思考为我指明了正确的方向。经过一些额外的测试，我能够让它工作。您必须在 LINQ 查询的 SELECT 部分中为 UNION 的每个部分设置相同的属性才能使其正常工作。如果没有值，您仍然需要手动将其设置为默认值（空）。例子：

(from c in Categories select new { ID = c.CategoryId, Name = c.Name, Price = null })
.UNION
(from p in Products select new { ID = p.ProductId, Name = p.Name, Price = p.Price })