我想问一下为什么Entity Framework 6会自动将Include(with where condition)转换为LEFT JOIN而不是INNER JOIN,是否有任何选项可以更改它?
public async Task<IEnumerable<UserGroup>> GetUserGroupWithUser()
{
var UserIds = new List<int>{ 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<UserToUserGroup> 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<UserToUserGroup> UserToUserGroups { get; set; }
}
配置:
public class UserConfiguration : IEntityTypeConfiguration<User>
{
public void Configure(EntityTypeBuilder<User> builder)
{
builder.ToTable("Users");
builder.HasKey(user => user.Id);
builder.Property(user => user.Id)
.IsRequired()
.ValueGeneratedOnAdd();
}
}
public class UserGroupConfiguration : IEntityTypeConfiguration<UserGroup>
{
public void Configure(EntityTypeBuilder<UserGroup> builder)
{
builder.ToTable("UserGroups");
builder.HasKey(userGroup => userGroup.Id);
builder.Property(userGroup => userGroup.Id)
.IsRequired()
.ValueGeneratedOnAdd();
}
}
public class UserToUserGroupConfiguration : IEntityTypeConfiguration<UserToUserGroup>
{
public void Configure(EntityTypeBuilder<UserToUserGroup> 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);
}
}
问题:
已检查
1.我尝试在配置中使用IsRequired()。
提前感谢所有的回应。
2条答案
按热度按时间np8igboo1#
不幸的是,Entity Framework的连接基于数据库的设计,因此在本例中,它强制使用
LEFT JOIN
。而是手动进行连接
toe950272#
您也可以使用join命令linq-joining-operator-join来执行此操作。
我把生成的查询
查询: