涉及联接的复杂sql查询的Linq和Lambda表达式

Question

在MVC 3项目中使用Linq到实体(实体框架)。

我的模特：

表-用户

UserID (PK)

..。

表-客户

ClientID (PK)

表- PropertyItems

PropertyItemID (PK)

表- MemberContactPreference (包含用户选择的PropertyItems -多到多)

UserID(FK)

PropertyItemID(FK)

表ClientProperties (包含属于客户端的PropertyItems --多到多)

ClientID (FK)

PropertyItemID (FK)

我想列出选择了客户端选择的所有属性的所有不同用户。

我的方法：

中的特定客户端的所有属性列表。

Iqueryable<ClientProperty> clientProperties  = GetClientProperties(ClientID)

Iqueryable<User> UsersMatchingClientProperties = GetAllUsers();



foreach (ClientProperty property in clientproperties)
{

 UsersMatchingClientProperties = (from uem in UsersMatchingClientProperties
                                  join ucp in GetAllMemberContactPreferences on 
                                  ucp.UserID == uem.UserID
                                  where uem.MemberContactPreferences.SelectMany(      
                                  mcp => mcp.PropertyItemID == property.PropertyItemID)
                                  select uem).Distinct;
}

它只给出了正确的结果第一次。因为它不会在每次迭代中减少UsersMatchingClientProperties中的项目数。实际上，它用新的结果集替换集合。我想在每次迭代中过滤掉这个集合。

另外，在没有使用Linq的情况下在Lambda表达式中执行此操作的任何建议。

谢谢

Answer 1

在for循环中生成的iqueryable似乎是一件危险的事情，它可能会在一次执行一个巨大的sql联接时结束。

不管怎么说，我不认为你需要那个。像这样的怎么样？

// for a given client, find all users 
// that selected ALL properties this client also selected

Iqueryable<ClientProperty> clientProperties  = GetClientProperties(ClientID)

Iqueryable<User> allUsers= GetAllUsers();

Iqueryable<MemberContactPreference> allMemberContactProperties = GetAllMemberContactPreferences();


Iqueryable<User> UsersMatchingClientProperties = allUsers
.Where(user => allMemberContactProperties
               .Where(membP => membP.UserID==user.UserID)
               .All(membP => clientProperties
                           .Select(clientP => clientP.PropertyID)
                           .Contains(membP.PropertyID)
               )
);

下面是另一个查询，以防您希望用户为给定客户端选择任何属性

// for a given client, find all users 
// that selected ANY properties this client also selected

Iqueryable<ClientProperty> clientProperties  = GetClientProperties(ClientID)

Iqueryable<User> allUsers= GetAllUsers();

Iqueryable<MemberContactPreference> allMemberContactProperties = GetAllMemberContactPreferences();


Iqueryable<User> UsersMatchingClientProperties = clientproperties
.Join(allMembersContactProperties, // join clientproperties with memberproperties
      clientP => clientP.PropertyItemID, 
      membP   => membP.PropertyItemID,
      (clientP, membP) => membP)) // after the join, ignore the clientproperties, keeping only memberproperties
.Distinct()                       // distinct is optional here. but perhaps faster with it?
.Join(allUsers,                   //join memberproperties with users
      membP => membP.UserID,
      user  => user.UserID,
      (membP, user) => user))     // after the join, ignore the member properties, keeping only users 
.Distinct();

Answer 2

我相信Hugo做得很好，提出了改进您的查询的方法(+1)。但这还不能解释问题的原因，这就是修改后的闭包陷阱。

我认为，在您的循环之后，有一些代码实际上在UsersMatchingClientProperties中执行查询。此时，使用循环变量property的最后一个值执行查询！(循环变量是在迭代中创建的每个查询委托中的闭包，并由每个迭代修改)。

按如下方式更改循环：

foreach (ClientProperty property in clientproperties)
{
    var property1 = property;
    ...

并在查询中使用property1。这应该能解决问题的根源。但如前所述，整个过程似乎还可以改进。