Как сделать подзапрос в 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, и у меня есть крайний срок. Я не смог найти пример, который соответствует, но я уверен, что он где-то там.
6 ответов:
вот подзапрос для вас!
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));
вы хотите сохранить хороший контроль над тем, что отправляется в базу данных. В исходном коде одним из возможных чтений является то, что необрезанная строка отправляется в базу данных для обрезки-что не очень хорошо работает для базы данных делающий.
в этом заявлении нет подзапроса, который лучше писать как
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" в обоих случаях сам, но это так...
вот как я делал подзапросы в 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;
вы могли бы сделать нечто подобное для вашего случая - (синтаксис может быть немного выключен). Также посмотрите на это ссылке
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;
хорошо, вот базовый запрос соединения, который получает правильные записи:
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
вот версия 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 используются для динамического добавления предложений предикатов где для запроса, в зависимости от того, какие поля формы заполнены пользователем веб-приложения. Поэтому сложная часть для меня заключается в том, как преобразовать рабочий запрос в выражение LINQ, которое я могу прикрепить к динамическому списку выражений.
Я дам некоторые из примеров запросов LINQ выстрел и посмотреть, если я могу интегрировать их с нашим кодом, а затем получить опубликовать мои результаты. Спасибо!
Марсель