Как сделать подзапрос в LINQ
Вот пример запроса, который я пытаюсь преобразовать в LINQ:
SELECT *
FROM Users
WHERE Users.lastname LIKE '%fra%'
AND Users.Id IN (
SELECT UserId
FROM CompanyRolesToUsers
WHERE CompanyRoleId in (2,3,4) )
Существует отношение FK между CompanyRolesToUsers и Users, но это отношение много-много, а CompanyRolesToUsers - таблица соединений.
У нас уже есть большая часть нашего сайта, и мы уже имеем большую часть фильтрации, работая, создавая выражения, используя класс PredicateExtensions.
Код для простых фильтров выглядит примерно так:
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, и у меня есть крайний срок. Я не смог найти пример, который соответствует, но я уверен, что он где-то там.
Ответы
Ответ 1
Вот подзапрос для вас!
List<int> IdsToFind = new List<int>() {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));
Вы хотите сохранить хороший контроль над тем, что отправляется в базу данных. В исходном коде одно возможное чтение состоит в том, что необработанная строка отправляется в базу данных для обрезки - что плохо работает для базы данных.
Ответ 2
В этом заявлении нет подзапроса, который лучше записывается как
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
Что опять-таки, оба уважаемых способа представить это. Я предпочитаю явный синтаксис "join" в обоих случаях сам, но там он...
Ответ 3
Вот как я делал подзапросы в 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;
Ответ 4
Вы можете сделать что-то подобное для своего случая - (синтаксис может быть немного выключен). Также посмотрите на ссылку
subQuery = (from crtu in CompanyRolesToUsers where crtu.RoleId==2 || crtu.RoleId==3 select crtu.UserId).ToArrayList();
finalQuery = from u in Users where u.LastName.Contains('fra') && subQuery.Contains(u.Id) select u;
Ответ 5
Хорошо, вот базовый запрос соединения, который получает правильные записи:
int[] selectedRolesArr = GetSelectedRoles();
if( selectedRolesArr != null && selectedRolesArr.Length > 0 )
{
//this join version requires the use of distinct to prevent muliple records
//being returned for users with more than one company role.
IQueryable retVal = (from u in context.Users
join c in context.CompanyRolesToUsers
on u.Id equals c.UserId
where u.LastName.Contains( "fra" ) &&
selectedRolesArr.Contains( c.CompanyRoleId )
select u).Distinct();
}
Но вот код, который наиболее легко интегрируется с алгоритмом, который у нас уже был на месте:
int[] selectedRolesArr = GetSelectedRoles();
if ( useAnd )
{
predicateAnd = predicateAnd.And( u => (from c in context.CompanyRolesToUsers
where selectedRolesArr.Contains(c.CompanyRoleId)
select c.UserId).Contains(u.Id));
}
else
{
predicateOr = predicateOr.Or( u => (from c in context.CompanyRolesToUsers
where selectedRolesArr.Contains(c.CompanyRoleId)
select c.UserId).Contains(u.Id) );
}
который благодаря плакату на форуме LINQtoSQL
Ответ 6
Здесь версия SQL, которая возвращает правильные записи:
select distinct u.*
from Users u, CompanyRolesToUsers c
where u.Id = c.UserId --join just specified here, perfectly fine
and u.firstname like '%amy%'
and c.CompanyRoleId in (2,3,4)
Также обратите внимание, что (2,3,4) - это список, выбранный из списка флажков пользователем веб-приложения, и я забыл упомянуть, что я просто жестко запрограммировал это для простоты. Действительно, это массив значений CompanyRoleId, поэтому он может быть (1) или (2,5) или (1,2,3,4,6,7,99).
Кроме того, еще одна вещь, которую я должен указать более четко, заключается в том, что PredicateExtensions используются для динамического добавления предикатных предложений в адрес Where для запроса, в зависимости от того, какие поля форм заполнено пользователем веб-приложения. Таким образом, сложная часть для me - как преобразовать рабочий запрос в выражение LINQ, которое я могу прикрепить к динамическому списку выражений.
Я приведу некоторые из примеров запросов LINQ и посмотрю, смогу ли я их интегрировать с нашим кодом, а затем опубликую свои результаты. Спасибо!
Marcel
