Entity Framework 4 - What is the syntax for joining 2 tables then paging them?

Question

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.

http://stackoverflow.com/questions/21051612/entity-framework-join-3-tables — Erçin Dedeoğlu, Feb 11 '17 at 05:58

Kirk Broadhurst · Answer 1 · 2012-04-19T06:27:23.717

8

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

edited Apr 19 '12 at 06:27

answered Apr 19 '12 at 06:20

Kirk Broadhurst

27,836
16
104
169

The only reason for the join is to do the sort (see the example). Will Include work the same way as join? – NightOwl888 Apr 19 '12 at 06:23
`Include` just specifies that the Variant should be 'included' when the Inventory is retrieved from the database. The join should be defined within your edmx file / on your EF designer. If you have a many->one between Inventory and Variant then you can do what I've suggested (see update) – Kirk Broadhurst Apr 19 '12 at 06:28
@NightOwl888 That will also work although Entity Framework can occasionally have issues with 1-1 relationships. – Kirk Broadhurst Apr 19 '12 at 06:48
Thanks. I didn't test this but I am sure it works this way. The main issue I see with this though, is the fact the compiler won't be able to detect if the relationship is broken (because of the string reference) and the fact it depends on an external resource to work. Using a join, the compiler will definitely complain if the EDMX changes in a way that doesn't make sense. Is there any other benefit to this method (such as performance) that could make me lean the other way? – NightOwl888 Apr 19 '12 at 07:32
@NightOwl888 I agree - the string reference is horrible, I don't know why this design choice was made by the EF team. However the compiler will still complain if the EDMX changes, because you explicitly reference the relationship `j.Variant.SortOrder`. Benefits to this method? Simplicity, readability, standardisation. – Kirk Broadhurst Apr 19 '12 at 22:48
A problem with `.Include()` is you can't use it with `.Where()` to push that filter to the database .. for instance if you do `context.SomeType.Include("SomeChild").Where(x=> x.IsActive)` .. then EF will return ALL the `SomeType` items from the database with a join to the `SomeChild` table and then apply the filter in-memory.. – JoeBrockhaus Oct 30 '13 at 22:29
Be aware that the Include expects the EntitySetName. Since you using a string in the Include parameter for your child entity/table name, be sure you haven't pluralized it like I did! In my case my Entity was named "ContentBlock" but due to pluralization my EntitySetName was "ContentBlocks". – PhillipPDX Mar 01 '17 at 18:47

Pravin Pawar · Accepted Answer · 2012-04-19T06:24:53.160

5

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

edited Apr 19 '12 at 06:24

answered Apr 19 '12 at 06:02

Pravin Pawar

2,559
3
34
40

Not sure if your second method is what I am looking for - I am trying to sort inventory by the variant.SortOrder field. If sorting can be done without joining, let me know. Still testing the first idea. – NightOwl888 Apr 19 '12 at 06:18
I have updated the query, as long as you have relation in dbml/edmx file between these entities, this query should be fine. – Pravin Pawar Apr 19 '12 at 06:26
I tested your first method and this works. My only concern here is will it execute 1 or 2 queries against the database? – NightOwl888 Apr 19 '12 at 07:18
As I said earlier, as long as the data/pagedQuery variables are IQueryable, it wont get fired against database. It will only be executed once you iterate through it. ( I mean you use data.ToList() or pageData.ToList(). You may want to read through deferred execution [here](http://stackoverflow.com/questions/215548/whats-the-hardest-or-most-misunderstood-aspect-of-linq) – Pravin Pawar Apr 19 '12 at 07:43
+1 - I will most likely use this solution (first idea) because it is more readable than the original one I was seeking. – NightOwl888 Apr 19 '12 at 08:15

score 1 · Answer 3 · answered Nov 03 '13 at 15:42

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

score 0 · Answer 4 · edited Feb 06 '19 at 22:02

0

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

edited Feb 06 '19 at 22:02

T.S.

18,195
11
58
78

answered Apr 19 '12 at 05:56

sacha barber

2,214
1
24
37

Entity Framework 4 - What is the syntax for joining 2 tables then paging them?

1

2

4 Answers4

Linked