I have an ASP.NET application that talks to an SQL database containing 4 tables:
StatesCities (maps Cities<=>States)
Customers (which stores the ID of the City they live in)
I need to get a count of how many customers live in a particular state. I can achieve this with the following SQL query:
select count(*) from Customers where CityID in
(
select sc.CityID from StatesCities sc, States s
where sc.StateID = s.StateID AND s.Name = 'Texas'
)
How can I express the equivalent of this query in LINQ, either using the EF or LINQ to SQL?
With the EF approach I've made it as far as:
var cities = db.Cities.Where(c => c.States.Any(s => s.Name == "Texas"));
but I'm not sure how to do the Customer/CityID match and count.
@216 got me on the right track with the first suggestion, but because a City can exist in more than one State I needed to tweak the statement slightly:
int customersCount = db.Customers.Count(c => c.City.States.Any(s => s.Name == "Texas"));
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments