Entity Framework 4 - What is the syntax for joinin

问题:

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;

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 Possible Solutions

I guess this one is just a matter of readability, but both of these will work and are semantically identical.

1

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);

Kudos to Khumesh and Pravin for helping with this. Thanks to the rest for contributing.

回答1:

Add following line to your query

var pagedQuery = data.Skip(PageIndex * PageSize).Take(PageSize); 

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); 

回答2:

Define the join in your mapping, and then use it. You really don't get anything by using the Join method - instead, use the Include method. It's much nicer.

var data = objContext.ProductInventory.Include("Variant")
               .Where(i => i.ProductId == productId && i.StoreId == storeId)
               .OrderBy(j => j.Variant.SortOrder)
               .Skip(x)
               .Take(y);

回答3:

My answer here based on the answer that is marked as true but here I add a new best practice of the code above

    var data= (from c in db.Categorie.AsQueryable().Join(db.CategoryMap,
                    cat=> cat.CategoryId, catmap => catmap.ChildCategoryId, 
    cat, catmap) => new { Category = cat, CategoryMap = catmap })
select (c => c.Category)

this is the best practice to use the Linq to entity because when you add AsQueryable() to your code; system will converts a generic System.Collections.Generic.IEnumerable to a generic System.Linq.IQueryable which is better for .Net engine to build this query at run time

thank you Mr. Khumesh Kumawat

回答4:

You would simply use your Skip(itemsInPage * pageNo).Take(itemsInPage) to do paging.