How do i write this query in entity-framework

Question

I have a table (Items) with records in this format.

Name     | ProductId | Owner
Product1 | 1         | xx
Product2 | 2         | yy
Product1 | 1         | xx
Product3 | 3         | xx
Product3 | 3         | xx
Product3 | 3         | xx
Product4 | 4         | xx
Product2 | 2         | xx
Product5 | 5         | xx

I want to write entity framework query that will return the top 3 products, their names and Count. The result should look like this.

Result
Name: [Product3, Product2, Product1],
Count:[3, 2, 2]

I have checked other answers here on SO but still did not find something close to what i want. Note Name and Count returns a list of the top 3 respectively.

What about using linq GroupBy? Do you want to use linq?

Aryan Firouzian
– Aryan Firouzian

2017-10-21 15:23:08 +00:00
Commented Oct 21, 2017 at 15:23 — Aryan Firouzian
– Aryan Firouzian, Commented Oct 21, 2017 at 15:23
@AryanFirouzyan, yes if returns the expected output

demo.b
– demo.b

2017-10-21 15:29:15 +00:00
Commented Oct 21, 2017 at 15:29 — demo.b
– demo.b, Commented Oct 21, 2017 at 15:29
@InBetween answer seems ok

Aryan Firouzian
– Aryan Firouzian

2017-10-21 15:31:05 +00:00
Commented Oct 21, 2017 at 15:31 — Aryan Firouzian
– Aryan Firouzian, Commented Oct 21, 2017 at 15:31

Kahbazi · Accepted Answer · 2017-10-21 15:33:18Z

3

You can try this code

var query = context
            .products
            .GroupBy(p => p.Name)
            .OrderByDescending(g => g.Count())
            .Take(3);

products.Select(p => new
{
    Name = query.Select(g => g.Key).ToList(),
    Count = query.Select(g => g.Count()).ToList(),
})
.FirstOrDefault();

Although I recommend that you get top 3 product with count together from database and then put it in different list, like this :

var products = context.products
            .GroupBy(p => p.Name)
            .OrderByDescending(g => g.Count())
            .Take(3)
            .Select(g => new
            {
                Name = g.Key,
                Count = g.Count()
            })
            .ToList();
List<string> names = products.Select(p => p.Name).ToList();
List<int> counts = products.Select(p => p.Count).ToList();

edited Oct 21, 2017 at 15:33

answered Oct 21, 2017 at 15:30

Kahbazi

15.1k3 gold badges52 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

InBetween Over a year ago

You should be ordering by descending order, otherwise you are taking the bottom 3 products.

Kahbazi Over a year ago

@InBetween you're right, thanks. I edited the answer

demo.b Over a year ago

Thank you @Arvin, just what i was looking for.

InBetween · Accepted Answer · 2017-10-21 15:29:48Z

2

The following should work:

products.GroupBy(p => p.ProductId)
        .OrderByDescending(g => g.Count())
        .Take(3);

answered Oct 21, 2017 at 15:29

InBetween

32.9k3 gold badges52 silver badges98 bronze badges

Collectives™ on Stack Overflow

How do i write this query in entity-framework

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related