How to use Skip() and Take() for some DbSet's property which is a ICollection<>

Question

I have a DbSet class:

public class Manufacturer
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public string City { get; set; }
    public virtual Category Category { get; set; }
    public virtual ICollection<Product> Products { get; set; }
}

I know I can use Skip() and Take() to get limited manufacturers. But my requirement is to get limited Products of all the manufacturers. I'm using something like this but it's not working

var manufacturers = await _context.Manufacturers.Where(x => x.Products.Take(10))
                    .ToListAsync();

PS: I'm using Lazy Loading (Not eager loading)

Compile error is:

Cannot implicitly convert type 'System.Collections.Generic.IEnumerable<Domain.Product>' to 'bool' Cannot convert lambda expression to intended delegate type because some of the return types in the block are not implicitly convertible to the delegate return type

How can I achieve to get all the manufacturers but limited products in them?

@PavelAnikhouski updated the question with the compiler error — SU7, Jun 21 '20 at 10:41
What is your requirement exactly? Only get manufacturers with 10 products? What is the 10? — igg, Jun 21 '20 at 10:43
I think you want something like `await _context.Manufacturers.Select(m => { m.Products = m.Products.Take(10); return m; }).ToListAsync();` but I doubt its possible like this in EF. — misha130, Jun 21 '20 at 10:46
This is currently not supported - it is planned to be a feature in the new EF Core 5.0, due out in Nov 2020, with the .NET 5 release — marc_s, Jun 21 '20 at 11:25
@SafiUllah Is `SelectManay` work here?, like `var manufacturers = await _context.Manufacturers.SelectMany(x => x.Products.Take(10)).ToListAsync();` — Mohammed Sajid, Jun 21 '20 at 11:39
@igg I want to get all the manufacturers, but each manufacturer must not contain more than 10 products — SU7, Jun 21 '20 at 11:48
@misha130 Thank you for your response. However, it didn't work with compiler error: Cannot implicitly convert type 'System.Collections.Generic.IEnumerable' to 'System.Collections.Generic.ICollection'. An explicit conversion exists (are you missing a cast?) — SU7, Jun 21 '20 at 11:49
@Sajid using your `SelectManay` suggestion, it returns a `List` not a `List` — SU7, Jun 21 '20 at 11:51
@SafiUllah that's right, you miss `.ToList()` for `m.Products = m.Products.Take(10)` to resolve the issue mentioned above. — Mohammed Sajid, Jun 21 '20 at 11:58
Just on an unrelated note these are all terrible solutions and this is a not a rare case scenario. EF should somehow be able to resolve this with one query — misha130, Jun 21 '20 at 12:02
I think one way to rethink this problem is to go in reverse. Start from the products and go up to the manufacturer,if you can't for reasons....Some of the proposed solutions work but get more data than you would need. Hmmm — panoskarajohn, Jun 21 '20 at 12:24

score 2 · Answer 1 · answered Jun 21 '20 at 12:00

2

I believe there is no way to do this directly with a queryable source. You can manage it in memory.

var manufacturers = await _context.Manufacturers.Include(m => m.Products).ToListAsync();

foreach(var m in manufacturers)
{
    m.Products = m.Products.Take(10).ToList();
}

This will get all products for each manufacturer from the DB and then keep only the first 10.

answered Jun 21 '20 at 12:00

igg

2,172
3
10
33

Thanks for your response. But Can you suggest a way where I don't have to fetch all the products from the DB? I could have thousands of products for a manufacturer and I only want to fetch 10 at most – SU7 Jun 21 '20 at 12:03
I'm not sure if it's possible with EF, but I will update my answer if I find anything. – igg Jun 21 '20 at 12:06

score 2 · Answer 2 · answered Jun 21 '20 at 12:24

You can load the Manufacturer entity without the Product list first (so without an Include() call) and then run a separate query to load only the products you want for a specific Manufacturer entity. EF will automatically update the navigation properties. See the following example (authors can have multiple posts in this example):

using (var context = new MyContext())
{
    Author author = context.Author.First();
            
    Console.WriteLine(context.Post.Where(it => it.Author == author).Count());
            
    context.Post.Where(it => it.Author == author).Take(2).ToList();
            
    Console.WriteLine(author.Posts.Count());
}

This will generate the following output:

3
2

Even though there are three entries available in my test database, only two are actually read. See the generated SQL queries:

For the Author author = context.Author.First(); line:

SELECT `a`.`Id`, `a`.`Name`
FROM `Author` AS `a`
LIMIT 1

For the context.Post.Where(it => it.Author == author).Count() line:

SELECT COUNT(*)
FROM `Post` AS `p`
INNER JOIN `Author` AS `a` ON `p`.`AuthorId` = `a`.`Id`
WHERE `a`.`Id` = 1

For the context.Post.Where(it => it.Author == author).Take(2).ToList(); line:

SELECT `p`.`Id`, `p`.`AuthorId`, `p`.`Content`
FROM `Post` AS `p`
INNER JOIN `Author` AS `a` ON `p`.`AuthorId` = `a`.`Id`
WHERE `a`.`Id` = 1
LIMIT 2

However, you have to do this trick for each individual Manufacturer entity, that it loads only ten associated Product entities. This can result in 1+N SELECT queries.

score 0 · Answer 3 · answered Jun 21 '20 at 11:33

0

Try the longer way:

_await _context.Manufacturers.Select(x => 
{
  x.Products = x.Products.Take(10).ToList();
  return x;
}).ToListAsync();

answered Jun 21 '20 at 11:33

pwistu

1

Thank you for your response. However it didnt work with a compiler warning: Because this call is not awaited, execution of the current method continues before the call is completed. Consider applying the 'await' operator to the result of the call. [Application]csharp(CS4014) A lambda expression with a statement body cannot be converted to an expression tree [Application]csharp(CS0834) An expression tree may not contain an assignment operator – SU7 Jun 21 '20 at 11:54

How to use Skip() and Take() for some DbSet's property which is a ICollection<>

3 Answers3