CODEWITHSUNDEEP
c#linq
Joe Ruder
Joe Ruder

Reputation: 2194

C# and Linq to Entities using count in a where lambda statement

I have the following that returns all records that have a child.

var allStops = (from s in db.stop_details
                where db.billing_transactions.Any(c=>c.stop_details_id == s.id)
                orderby s.id
                select s).ToArray();

I want to know all records that have exactly 3 children, like:

var allStops = (from s in db.stop_details
                where db.billing_transactions.Any(c=>c.stop_details_id == s.id).Count() == 3
                orderby s.id
                select s).ToArray();

or

var allStops = (from s in db.stop_details
                where db.billing_transactions.Count(c=>c.stop_details_id == s.id) == 3
                orderby s.id
                select s).ToArray();

I just can't seem to get the syntax correct...

Upvotes: 0

Views: 203

Answers (3)

Tinypond
Tinypond

Reputation: 51

using dot notation, please try the following example this assumes you have a from billing_transaction to stop_details. 

var allstops = db.stop_details
    .Where (s=>s.billing_transactionsc.stop_details_id.Count() == 3)
    .OrderBy (s => s.id)
    .ToArray();

Upvotes: -1

ocuenca
ocuenca

Reputation: 39376

If you are using EF you should use navigation properties instead. According to what I saw, you should have an one to many relationship between stop_details and billing_transactions, so stop_details entity should have a collection navigation property (lets call it billing_transactions). Your query could be like:

var allstops=db.stop_details.Where(s=>s.billing_transactions.Count()==3)
                            .OrderBy(s=>s.id)
                            .ToArray();

Now if you don't want to use the navigation property I suggest you to do a group join:

var allStops = (from s in db.stop_details
                join bt in db.billing_transactions on bt.stop_details_id equals s.id into bts
                where bts.Count()==3
                orderby s.id
                select s).ToArray();

Upvotes: 1

Jonathan Wood
Jonathan Wood

Reputation: 67345

Since you said you have a database relationship between the two tables, and assuming billing_transactions.stop_details_id is a foreign key, you should be able to do something like this.

var allStops = (from s in db.stop_details
                where s.billing_transactions.Count() == 3
                orderby s.id
                select s).ToArray();

Upvotes: 1

Related Questions