Как сделать подзапрос в 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 63
c# linq linq-to-sql

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));

вы хотите сохранить хороший контроль над тем, что отправляется в базу данных. В исходном коде одним из возможных чтений является то, что необрезанная строка отправляется в базу данных для обрезки-что не очень хорошо работает для базы данных делающий.

69

в этом заявлении нет подзапроса, который лучше писать как

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" в обоих случаях сам, но это так...

22

вот как я делал подзапросы в 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;
3

вы могли бы сделать нечто подобное для вашего случая - (синтаксис может быть немного выключен). Также посмотрите на это ссылке

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;
2

хорошо, вот базовый запрос соединения, который получает правильные записи:

   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

2

вот версия 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 выстрел и посмотреть, если я могу интегрировать их с нашим кодом, а затем получить опубликовать мои результаты. Спасибо!

Марсель

1