How to do this in the Entity Framework (several where or Join)?

Question

I have two tables that are related to each other.

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

Now I need to check the value from table A (username), and I need to check the value from table B (ClubId).

So, in my opinion, it will be something like

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

// return the count.

but now I know that with Entity stuff it should make connections less common, so I wonder if I can do this with a connection.

I tried this

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

so this will not work, as it will not return the correct type (2nd place). This is how I thought line by line, how I would expect it to be done. [/ P>

So what should it look like?

PS

, Linq, .

Answer 1

TableA , , , :

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

, .

:

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

, EF . , :

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

Answer 2

, EF , - :

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

, .