Запрос SelectMany с параметром Where создает много запросов SQL

Я думаю, проблема в том, что вы ленивы загружаете группы и роли.

Одно из решений - загрузить их, прежде чем вызвать в SingleOrDefault.

var user = db.Users.Include(x => x.Groups.Select(y => y.Roles))
                   .SingleOrDefault(u => u.InternetId == username);

var groups = user.Groups.SelectMany(
                   g => g.Roles.Where(r => r.Asset.AssetName == application));

var userRoles = Mapper.Map<List<RoleApi>>(groups);

Также обратите внимание: здесь нет проверки работоспособности на null.

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

С AutoMapper вы можете избежать необходимости загружать объекты с нетерпением, используя .ProjectTo<T>() в IQueryable, при условии, что пользователь доступен в группе.

Например:

var roles = db.Groups.Where(g => g.User.Internetid == username)
   .SelectMany(g => g.Roles.Where(r => r.Asset.AssetName == application))
   .ProjectTo<RoleApi>()
   .ToList();

Это должно использовать отложенное выполнение, при котором AutoMapper будет эффективно проецировать .Select(), необходимый для заполнения экземпляра RoleApi на основе вашего сопоставления / проверки.

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

var userRoles = Mapper.Map<List<RoleApi>>(
db.Users.Where(u => u.InternetId == username).Select(../* projection */ )
  .Groups.SelectMany(g => g.Roles.Where(r => r.Asset.AssetName == application)));

EF также включает в себя:

var userRoles = Mapper.Map<List<RoleApi>>(
db.Users.Where(u => u.InternetId == username).Select(../* projection */ )
  .Include(g => g.Roles.Where(r => r.Asset.AssetName == application)));

Затем можно выполнить итерацию коллекции, используя несколько циклов for.

Вы должны знать о двух отличиях:

• Разница между IEnumerable и IQueryable
• Разница между функциями, возвращающими IQueryable<TResult> (ленивый), и функциями, возвращающими TResult (выполнение)

Разница между Enumerable и Queryable

. Оператор LINQ AsEnumerable предназначен для обработки в вашем локальном процессе. Он содержит весь код и все вызовы для выполнения оператора. Этот оператор выполняется, как только вызываются GetEnumerator и MoveNext, явно или неявно с использованием операторов foreach или LINQ, которые не возвращают IEnumerable<...>, например ToList, FirstOrDefault и Any.

Напротив, IQueryable не предназначен для обработки в вашем процессе (однако это можно сделать, если вы хотите). Обычно предполагается, что он будет обрабатываться другим процессом, обычно системой управления базами данных.

Для этого IQueryable содержит Expression и Provider. Expression представляет запрос, который должен быть выполнен. Provider знает, кто должен выполнить запрос (СУБД) и какой язык использует этот исполнитель (обычно SQL). Когда вызываются GetEnumerator и MoveNext, Provider берет Expression и переводит его на язык Executor. Запрос отправляется исполнителю. Возвращенные данные представлены AsEnumerable, где вызываются GetEnumerator и MoveNext.

Из-за этого преобразования в SQL IQueryable не может делать все то, что может делать IEnumerable. Главное, что он не может вызывать ваши локальные функции. Он даже не может выполнять все функции LINQ. Чем лучше качество Provider, тем больше он на него способен. См. поддерживаемые и неподдерживаемые методы LINQ

Ленивые методы LINQ и выполнение методов LINQ

Есть две группы методов LINQ. Те, которые возвращают `IQueryable <...> / IEnumerable <...>, и те, которые этого не делают.

Первая группа использует ленивую загрузку. Это означает, что в конце оператора LINQ запрос был создан, но еще не выполнен. Только «GetEnumeratorandMoveNextwill make that theProviderwill translate theExpression» и приказ СУБД выполнить запрос.

Объединение IQueryables изменит только Expression. Это довольно быстрая процедура. Следовательно, не будет увеличения производительности, если вы создадите одно большое выражение LINQ вместо их объединения перед выполнением запроса.

Обычно СУБД умнее и лучше подготовлена ​​к выбору, чем ваш процесс. Передача выбранных данных в ваш локальный процесс - одна из самых медленных частей вашего запроса.

Advice: Try to create your LINQ statements such, that the executing statement is the last one that can be executed by the DBMS. Make sure that you only select the properties that you actually plan to use.

Так, например, не передавайте внешние ключи, если вы их не используете.

Вернуться к вашему вопросу

Оставляя картограф в стороне от вопроса, с которого вы начинаете:

db.Users.SingleOrDefault(...)

SingleOrDefault - неленивая функция. Он не возвращает IQueryable<...>. Он выполнит запрос. Он перенесет один полный User в ваш локальный процесс, включая его Roles.

Совет отложить SingleOrDefault до последнего утверждения:

var result = myDbcontext.Users
    .Where(user => user.InternetId == username)
    .SelectMany(user => user.Groups.Roles.Where(role => role.Asset.AssetName == application))

     // until here, the query is not executed yet, execute it now:
     .SingleOrDefault();

Проще говоря: из последовательности Users оставьте только те Users с InternetId, равным userName. Из всех оставшихся Users (которые вы надеетесь быть только одним) выберите последовательность Roles из Groups каждого User. Однако мы не хотим выбирать все Roles, мы оставляем только Roles с AssetName равным application. Теперь поместите все оставшиеся Roles в одну коллекцию (часть many в SelectMany) и выберите ноль или один оставшийся Role, который вы ожидаете.