Linq连接两个值 [英] Linq join on two values

问题描述

假设我有一个{City, State}列表.它最初来自数据库，并且我具有LocationID，但是现在我将其加载到内存中.假设我还有一张快餐店的桌子，其中有城市和州作为记录的一部分.我需要获取与城市和州匹配的场所列表.

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.

我想出了以下LINQ解决方案:

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

我觉得必须有更好的东西.对于初学者来说，我已经在内存中包含了City/State-因此仅返回数据库以进行联接似乎效率很低.我正在寻找一种表达方式{r.City, r.State} match Any(MyList)，其中MyList是我的城市/州的集合.

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;

，并且出现以下编译错误: Error CS1941 The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'Join'.

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'.

更新2 编译器不喜欢联接中的匿名类.我说的很明确，但它不再抱怨了.我会看看它是否真的在早上起作用...

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...

推荐答案

在我看来您需要这个:

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;

这篇关于Linq连接两个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！