这是我试图转换为LINQ的查询示例:
SELECT * FROM Users WHERE Users.lastname LIKE '%fra%' AND Users.Id IN ( SELECT UserId FROM CompanyRolesToUsers WHERE CompanyRoleId in (2,3,4) )
CompanyRolesToUsers
和之间存在FK关系Users
,但它是多对多的关系,并且CompanyRolesToUsers
是联结表.
我们已经构建了大部分网站,并且我们已经通过使用PredicateExtensions类构建Expressions来完成大部分过滤工作.
简单过滤器的代码如下所示:
if (!string.IsNullOrEmpty(TextBoxLastName.Text)) { predicateAnd = predicateAnd.And(c => c.LastName.Contains( TextBoxLastName.Text.Trim())); } e.Result = context.Users.Where(predicateAnd);
我正在尝试在另一个表中为子选择添加谓词.(CompanyRolesToUsers
)
我希望能够添加的内容是:
int[] selectedRoles = GetSelectedRoles(); if( selectedRoles.Length > 0 ) { //somehow only select the userid from here ???: var subquery = from u in CompanyRolesToUsers where u.RoleID in selectedRoles select u.UserId; //somehow transform this into an Expression ???: var subExpression = Expression.Invoke(subquery); //and add it on to the existing expressions ???: predicateAnd = predicateAnd.And(subExpression); }
有没有办法做到这一点?这很令人沮丧,因为我可以轻松编写存储过程,但我是这个LINQ的新手,我有一个截止日期.我找不到匹配的例子,但我确定它在某处.
这是给你的子查询!
ListIdsToFind = new List () {2, 3, 4}; db.Users .Where(u => SqlMethods.Like(u.LastName, "%fra%")) .Where(u => db.CompanyRolesToUsers .Where(crtu => IdsToFind.Contains(crtu.CompanyRoleId)) .Select(crtu => crtu.UserId) .Contains(u.Id) )
关于这部分问题:
predicateAnd = predicateAnd.And(c => c.LastName.Contains( TextBoxLastName.Text.Trim()));
我强烈建议在创建查询之前从文本框中提取字符串.
string searchString = TextBoxLastName.Text.Trim(); predicateAnd = predicateAnd.And(c => c.LastName.Contains( searchString));
您希望对发送到数据库的内容保持良好的控制.在原始代码中,一个可能的读取是未修剪的字符串被发送到数据库进行修剪 - 这对数据库来说不是一件好事.
此语句不需要子查询,更好地编写为
select u.* from Users u, CompanyRolesToUsers c where u.Id = c.UserId --join just specified here, perfectly fine and u.lastname like '%fra%' and c.CompanyRoleId in (2,3,4)
要么
select u.* from Users u inner join CompanyRolesToUsers c on u.Id = c.UserId --explicit "join" statement, no diff from above, just preference where u.lastname like '%fra%' and c.CompanyRoleId in (2,3,4)
话虽如此,在LINQ中它会是
from u in Users from c in CompanyRolesToUsers where u.Id == c.UserId && u.LastName.Contains("fra") && selectedRoles.Contains(c.CompanyRoleId) select u
要么
from u in Users join c in CompanyRolesToUsers on u.Id equals c.UserId where u.LastName.Contains("fra") && selectedRoles.Contains(c.CompanyRoleId) select u
这也是表达这一点的可敬方式.在这两种情况下,我更喜欢显式的"连接"语法,但它有......
这就是我在LINQ中做子查询的方式,我认为这应该得到你想要的.您可以将显式CompanyRoleId == 2 ...替换为您想要的不同角色的另一个子查询,也可以将其加入.
from u in Users join c in ( from crt in CompanyRolesToUsers where CompanyRoleId == 2 || CompanyRoleId == 3 || CompanyRoleId == 4) on u.UserId equals c.UserId where u.lastname.Contains("fra") select u;