In Entity Framework, how can I do this (many where's or Join)?

c# entity-framework linq linq-to-entities

Question

I have two tables that are related to one another.

Table A and Table B have a 1 to many connection, which adds a navigation attribute to each.

I must now verify the value from Table A (userName) and the 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 given that entity-related information should make joins less frequent, I'm wondering whether I can do this using a join instead.

I did it.

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

Therefore, because it won't return the correct type, this is ineffective (the 2nd where). Just how I would anticipate it to be done, this is how I had it in mind.

Then, how should it appear?

P.S

I would want to see an example using the Linq method queries, as I did above.

1
5
1/23/2016 9:30:44 AM

Accepted Answer

Filtering TableA before you join is likely more productive:

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

It's simply my preference for clarity in this case; you don't have to use two variables.

Or you could make a few changes to the method syntax:

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

I'm not sure EF, however, is capable of understanding those specific statements. If not, the compiler would interpret the above statement as follows:

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();
4
8/13/2009 6:01:55 PM

Popular Answer

If your EF model incorporates the connection between Users and Clubs, you may proceed as follows:

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

Look at joins in the query if you want to retrieve items from both Users and Clubs.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow