Linq join on two values

Suppose I have a list of {City, State}. It originally came from the database, and I have LocationID, but by now I loaded it into memory. Suppose I also have a table of fast food restaurants that has City and State as part of the record. I need to get a list of establishments that match city and state.

NOTE: I try to describe a simplified scenario; my business domain is completely different.

I came up with the following LINQ solution:

var establishments = from r in restaurants
from l in locations
where l.LocationId == id &&
      l.City == r.City &&
      l.State == r.State
select r

and I feel there must be something better. For starters, I already have City/State in memory – so to go back to the database only to have a join seems very inefficient. I am looking for some way to say {r.City, r.State} match Any(MyList) where MyList is my collection of City/State.

UPDATE
I tried to update based on suggestion below:

List<CityState> myCityStates = ...;
var establishments =
from r in restaurants
join l in myCityStates
    on new { r.City, r.State } equals new { l.City, l.State } into gls
select r;

and I got the following compile error:
Error CS1941 The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'Join'.

UPDATE 2
Compiler didn’t like anonymous class in the join. I made it explicit and it stopped complaining. I’ll see if it actually works in the morning…

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

It seems to me that you need this:

var establishments =
    from r in restaurants
    join l in locations.Where(x => x.LocationId == id)
        on new { r.City, r.State } equals new { l.City, l.State } into gls
    select r;

Method 2

Well, there isn’t a lot more that you can do, as long as you rely on a table lookup, the only thing you can do to speed up things is to put an index on City and State.

The linq statement has to translate into a valid SQL Statement, where “Any” would translate to something like :

SELECT * FROM Restaurants where City in ('...all cities')

I dont know if other ORM’s give better performance for these types of scenarios that EF, but it might be worth investigating. EF has never had a rumor for being fast on reads.

Edit: You can also do this:

List<string> names = new List { "John", "Max", "Pete" };
bool has = customers.Any(cus => names.Contains(cus.FirstName));

this will produce the necessary IN(‘value1’, ‘value2’ …) functionality that you were looking for

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0