Entity Framework Paged Join and Unions?

I'm developing a new web interface for a database with the following structure:

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)

The Relations table is used to establish relations between a parent category and its children, which can be categories, products, webpages, or links.

I need to be able to get back a paged list of the child nodes for a given parent category. The nodes have to be Active, Visible, and Sorted (default SortOrder then by Name).

I tried doing some things with EF/LINQ and had issues JOINing Relations because of needing multiple criteria (example: R.CmsForeignKeyID = N.ID, R.CmsItemType = 1) so I didn't even get to the unions and paging.

Would it be best to forego EF as a tool in this scenario and do it with traditional SQL, something like:

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.IsVisib开发者_如何学运维le = 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

And then do the paging logic on the resultset? Or is there a better way to structure the DB that will make this easier?

---

My thought on views kind of pointed me in the right direction. After some additional testing, I was able to get this to work. You have to set the same properties in the SELECT portion of the LINQ query for each part of the UNION in order for it to work. If you don't have a value, you still have to manually set it to a default (null). Example:

(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 })