Как принудительно включить внутреннее соединение в Include? ⇐ C#

[Anonymous]

Я хотел бы спросить, почему ядро ​​Entity Framework 6 автоматически переводит Include (с условиемwhere) в LEFT JOIN вместо INNER JOIN и есть ли возможность это изменить?

Код: Выделить всё

public async Task GetUserGroupWithUser()
{
var UserIds = new List{ 1};

var userGroups = await context.UserGroups.Include(userGroup =>
userGroup.UserToUserGroups.Where(ugtu => ugtu.IsActive &&
UserIds.Contains(ugtu.UserId)))
.ThenInclude(ugtu => ugtu.User)
.Where(userGroup => userGroup.UserToUserGroups.Any())
//Removed some projection
.ToListAsync();

return userGroups;
}

Запрос:

Код: Выделить всё

SELECT [u].[Id], [u].[IsActive], [u].[Name], [t].[Id], [t].[IsActive], [t].[UserGroupId], [t].[UserId], [t].[Id0], [t].[IsActive0], [t].[Name]
FROM [UserGroups] AS [u]
LEFT JOIN (
SELECT [u1].[Id], [u1].[IsActive], [u1].[UserGroupId], [u1].[UserId], [u2].[Id] AS [Id0], [u2].[IsActive] AS [IsActive0], [u2].[Name]
FROM [UserToUserGroups] AS [u1]
INNER JOIN [Users] AS [u2] ON [u1].[UserId] = [u2].[Id]
WHERE ([u1].[IsActive] = CAST(1 AS bit)) AND ([u1].[UserId] = 1)
) AS [t] ON [u].[Id] = [t].[UserGroupId]
WHERE EXISTS (
SELECT 1
FROM [UserToUserGroups] AS [u0]
WHERE [u].[Id] = [u0].[UserGroupId])
ORDER BY [u].[Id], [t].[Id]

Объекты:

Код: Выделить всё

public class User
{
public int Id { get; set; }
public string Name { get; set; }
public bool IsActive { get; set; }
public virtual ICollection UserToUserGroups { get; set;}
}

public class UserToUserGroup
{
public int Id { get; set; }
public int UserId { get; set; }
public int UserGroupId { get; set; }
public bool IsActive { get; set; }
public virtual UserGroup UserGroup { get; set; }
public virtual User User { get; set; }
}

public class UserGroup
{
public int Id { get; set; }
public string Name { get; set; }
public bool IsActive { get; set; }
public virtual ICollection UserToUserGroups { get; set; }
}

Конфигурация:

Код: Выделить всё

public class UserConfiguration : IEntityTypeConfiguration
{
public void Configure(EntityTypeBuilder builder)
{
builder.ToTable("Users");
builder.HasKey(user => user.Id);
builder.Property(user => user.Id)
.IsRequired()
.ValueGeneratedOnAdd();
}
}

public class UserGroupConfiguration : IEntityTypeConfiguration
{
public void Configure(EntityTypeBuilder builder)
{
builder.ToTable("UserGroups");
builder.HasKey(userGroup => userGroup.Id);
builder.Property(userGroup => userGroup.Id)
.IsRequired()
.ValueGeneratedOnAdd();
}
}

public class UserToUserGroupConfiguration : IEntityTypeConfiguration
{
public void Configure(EntityTypeBuilder builder)
{
builder.ToTable("UserToUserGroups");
builder.HasKey(ugtu => ugtu.Id);
builder.Property(ugtu => ugtu.Id)
.IsRequired()
.ValueGeneratedOnAdd();

builder.HasIndex(ugtu => new
{
ugtu.UserId,
ugtu.UserGroupId
}).HasFilter("IsActive = 'true'").IsUnique();

builder
.HasOne(utug => utug.User)
.WithMany(user => user.UserToUserGroups)
.HasForeignKey(ugtu => ugtu.UserId)
.OnDelete(DeleteBehavior.Cascade);
builder.HasOne(utug => utug.UserGroup)
.WithMany(user => user.UserToUserGroups)
.HasForeignKey(ugtu => ugtu.UserGroupId)
.OnDelete(DeleteBehavior.Cascade);

}
}

Проблема:


Проверено

[*]Я попробовал использовать IsRequired() внутри конфигурации.


Подробнее здесь: https://stackoverflow.com/questions/762 ... in-include