Избавление от ненужного объединения при использовании таблиц ссылок с EF

Question

У меня есть эта схема базы данных:

User  <--  UserRole  -->  Role

Таблица Role имеет несколько столбцов, но меня интересует только первичный ключ, и я могу получить его из внешнего ключа UserRole, так что мне вообще не нужно присоединяться к таблице Role, но я не могу понять, как чтобы избежать присоединения к этой таблице с EF.

Я попробовал этот код:

context.Users.Where(u => u.UserId == x).Single().Roles.Select(r => r.RoleId);

Это создает два запроса. Один на пользовательской таблице и один на объединении UserRole и Role. Конечно, я могу сократить его до одного запроса, используя.Include("Roles") или SelectMany(), но этот запрос объединит три таблицы. Есть ли способ избавиться от лишнего соединения? Я хочу, чтобы мой SQL был похож на это:

SELECT u.*, ur.RoleId
FROM User u
LEFT OUTER JOIN UserRole ur on ur.UserId = u.UserId
WHERE ...

На самом деле я использую automapper, но я думаю, что этот пример демонстрирует ту же проблему. Роли - это небольшая таблица, поэтому на этот раз я могу выдержать снижение производительности, но меня беспокоит то, что я не могу получить ее так же эффективно, как рукописный SQL. Кто-нибудь еще сталкивался с этим до меня и нашел решение?

Answer 1

Вы можете использовать следующее (я не использую var здесь, чтобы сделать типы явными):

IQueryable<IEnumerable<int>> query = context.Users
    .Where(u => u.UserId == x)
    .Select(u => u.Roles.Select(r => r.RoleId));

IEnumerable<int> result = query.Single();

это

• выдает исключение, если пользователь с UserId == x не существует (Я думаю, вы хотите этого, потому что вы используете Single в вашем примере.)
• возвращает коллекцию RoleIdс пользователя x, Коллекция может быть пустой (result.Count() == 0) если пользователь не в какой-либо роли.

• создает следующий SQL для query (с x == 1) который только присоединяется User а также UserRoles Таблица:

  SELECT 
  [Project1].[UserId] AS [UserId], 
  [Project1].[C1] AS [C1], 
  [Project1].[RoleId] AS [RoleId]
  FROM ( SELECT 
          [Extent1].[UserId] AS [UserId], 
          [Extent2].[RoleId] AS [RoleId], 
         CASE WHEN ([Extent2].[UserId] IS NULL) 
             THEN CAST(NULL AS int) 
             ELSE 1
         END AS [C1]
         FROM  [dbo].[Users] AS [Extent1]
         LEFT OUTER JOIN [dbo].[UserRoles] AS [Extent2]
             ON [Extent1].[UserId] = [Extent2].[UserId]
         WHERE 1 = [Extent1].[UserId]
       )  AS [Project1]
  ORDER BY [Project1].[UserId] ASC, [Project1].[C1] ASC
  

Если вы не хотите различать, если пользователь не существует или существует, но не имеет ролей, вы можете получить намного более простой SQL с этим запросом:

IQueryable<int> query = context.Users
    .Where(u => u.UserId == x)
    .SelectMany(u => u.Roles.Select(r => r.RoleId));

IEnumerable<int> result = query.ToList();

Это возвращает пустую коллекцию, если у пользователя нет ролей или если пользователь не существует. Но SQL очень прост:

SELECT 
[Extent1].[RoleId] AS [RoleId]
FROM [dbo].[UserRoles] AS [Extent1]
WHERE 1 = [Extent1].[UserId]

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

Answer 2

Я использую роли более эффективно.

Флажки Checkout для добавления ролей в ваши пользовательские таблицы.

Что делает флаг:

Создает вид Enum с:

Administrator = 1
Moderator = 2
SuperUser = 4
User = 8
Visitor = 16

Вы добавляете атрибут Role в Usertable как Integer.

"User" = Administrator + Moderator --> Role =  3 ( 1 + 2 )
"User" = Moderator + SuperUser     --> Role =  6 ( 2 + 4 )
"User" = SuperUser + User          --> Role = 12 ( 4 + 8 )

Нет распределительной таблицы больше и решит вашу проблему.

(Хотя для добавления логики в ваше приложение потребуется дополнительное кодирование)