CODEWITHSUNDEEP
c#asp.net-coreentity-framework-core
SU7
SU7

Reputation: 1766

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

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?

Upvotes: 2

Views: 197

Answers (3)

Progman
Progman

Reputation: 19555

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.

Upvotes: 2

igg
igg

Reputation: 2250

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.

Upvotes: 2

pwistu
pwistu

Reputation: 1

Try the longer way:

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

Upvotes: 0

Related Questions