How do I do this in Entity Framework (multiple where's or Join)?

Question

I have 2 tables that have a relation ship to each other

Table A has 1 to many relationship with table B, so this creates a navigation property into each.

Now I need to check a value from Table A (userName) and I need to check a value from table B (ClubId).

So in my mind it would be something like

Join the tables together
Where A.userName == "bob" &&
where B.clubId == "Car"

// return the count.

but now I know with Entity stuff it should make joins less common so I am wondering if I can do it with a join then.

I tried this

int count = Entity.TableA.where(a => a.userName == "bob" && a.TableB.where(i => i.ClubId == "Car")).Count();

so this does not work since it won't return the right type(the 2nd where). This is just how I thought along the lines how I would expect it to be done would work.

So how should it look?

P.S

I rather have an example done in the Linq method queries like I did above.

Answer 1

Assuming your EF model has the relationship between Users and Clubs could do something like this:

var usersNamedBobInCarClub = 
             from A in User
             from B in A.Clubs
             where A.userName == "bob" &&
                   B.clubId == "Car"
             select A;

If you want return elements of both Users and Clubs have a look at joins within the query.

Answer 2

Filtering TableA before your join is probably more efficient:

var clubs = from a in Entity.TableA
            where a.userName == "bob"
            from b in a.TableB
            where b.clubId == "Car"
            select b;

var count = clubs.Count();

You don't have to use two variables, it's just my preference here for clarity.

Or in method syntax you can simplify a bit:

var count = Entity.TableA.Where(a => a.userName == "bob")
                         .SelectMany(a => a.TableB)
                         .Count(b => b.clubId == "Car");

However, I'm not certain EF understands those particular expressions. If not, the compiler would translate the above query like this:

var count = Entity.TableA.Where(a => a.userName == "bob")
                         .SelectMany(a => a.TableB, (a,b) => new { a, b })
                         .Where(x => x.b.clubId == "Car")
                         .Count();