CODEWITHSUNDEEP
c#.netlinq
ragu89
ragu89

Reputation: 345

Linq Query with a where condition - count items

How can I change this request :

query = query.Where(item => (from table in context.Table 
                             where table.amount == item.Amount 
                             select table).Count() >= 10);

to not use the subquery (from ... in ...) ?

I tried to create the subquery separately, to use it with the Where condition :

var subQuery = from table in context.Table select table.amount;

var list = subQuery.ToList()

But I don't know how I can use it after that, because of the .Count() operation.

Thank you for your comments.

Upvotes: 0

Views: 3436

Answers (3)

D Stanley
D Stanley

Reputation: 152541

How about this:

query = query.Where(item => context.Table
                                   .Count(t => t.amount == item.Amount) >= 10);

Or to reduce the number of round-trips:

var counts = context.Table
                    .GroupBy(t => t.amount)
                    .Select(g => new {amount = g.Key, count = g.Count()});

query = from q in query
        join c in counts
        on q.amount equals c.amount
        where c.count >= 10
        select q;

Upvotes: 3

user3497970
user3497970

Reputation:

what about this one 

var subQuery = (from table in context.Table select table.amount).ToList();

query = query.Where(item => subQuery.Count() >= 10);

Upvotes: 0

Selman Genç
Selman Genç

Reputation: 101681

Just use Count with a predicate directly:

query.Where(item => context.Table.Count(table.amount == item.Amount) >= 10);

Upvotes: 0

Related Questions