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

Question

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...

Why are you ignoring the answers? Is that just a newbie thing, or what? — Jonathan Wood
– Jonathan Wood, Commented Jan 16, 2018 at 1:37
No Johathan, I'm sorry...I had accepted the answer but my form apparently did not post. I had it minimized until just now. The answer is sincerely appreciated. — Joe Ruder
– Joe Ruder, Commented Jan 16, 2018 at 16:59

Jonathan Wood · Accepted Answer · 2018-01-16 00:27:57Z

1

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();

answered Jan 16, 2018 at 0:27

Jonathan Wood

68.1k86 gold badges309 silver badges542 bronze badges

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

1 Comment

Joe Ruder Over a year ago

As usual over thinking was the problem here...thank you.

ocuenca · Accepted Answer · 2018-01-16 02:59:33Z

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();

Tinypond · Accepted Answer · 2018-01-16 04:07:37Z

-1

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();

edited Jan 16, 2018 at 4:07

answered Jan 16, 2018 at 0:53

Tinypond

511 silver badge8 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related