c# – 显式加载N：M过滤[复制]

发布时间：2020-12-15 08:06:30 所属栏目：百科来源：网络整理

导读：参见英文答案 How to manually load related entities in a N:N relationship?2个我试图从EntityFramework中的显式加载过滤结果. 当我不应用任何过滤器但是在应用过滤器后它不加载结果时,显式加载有效. 类 public partial class Student{ public int Studen

参见英文答案 > How to manually load related entities in a N:N relationship?2个
我试图从EntityFramework中的显式加载过滤结果.

当我不应用任何过滤器但是在应用过滤器后它不加载结果时,显式加载有效.

类

public partial class Student
{
    public int StudentId { get; set; }
    public int CourseId { get; set; }
    public string Name { get; set; }
    public string Status { get; set; }
    public virtual ICollection<Grade> Grades { get; set; }
}

public partial class Grade
{
    public int GradeId { get; set; }
    public string Value { get; set; }
    public string Status { get; set; }
    public virtual ICollection<Student> Students { get; set; }
}

流畅的API映射

modelBuilder.Entity<Grade>()
    .HasMany(e => e.Students)
    .WithMany(x => x.Grades)
    .Map(m => m.ToTable("StudentGrades").MapLeftKey("GradeId").MapRightKey("StudentId"));

用法

这适用于学生.Grades属性.

using (var context = new Model1())
{
    context.Configuration.LazyLoadingEnabled = false;

    var student = context.Students.Single(x => x.StudentId == 1);
    context.Entry(student).Collection(x => x.Grades).Load();
}

生成的SQL如下所示：

SELECT 
[Extent2].[GradeId] AS [GradeId],[Extent2].[Value] AS [Value],[Extent2].[Status] AS [Status]
FROM  [dbo].[StudentGrades] AS [Extent1]
INNER JOIN [dbo].[Grades] AS [Extent2] ON [Extent1].[GradeId] = [Extent2].[GradeId]
WHERE [Extent1].[StudentId] = 1 // this is parameterized in the actual hit.

当我运行此查询时,我得到了完整的结果.

但是,当我应用过滤并使用以下行时,它不会填充student.Grades.

context.Entry(student).Collection(x => x.Grades).Query().Where(x => x.Status == "A").Load();

此行生成此查询：

SELECT 
[Extent2].[GradeId] AS [GradeId],[Extent2].[Status] AS [Status]
FROM  [dbo].[StudentGrades] AS [Extent1]
INNER JOIN [dbo].[Grades] AS [Extent2] ON [Extent1].[GradeId] = [Extent2].[GradeId]
WHERE ([Extent1].[StudentId] = 1) AND ('A' = [Extent2].[Status])
//the "1" is parameterized in the actual hit.

当我手动对数据库运行时,我在SQL Server中获得正确筛选的结果.问题是这不会填充C#对象中的student.Grades.

解决方法

在明确加载相关实体部分时,MSDN Article – 应用过滤器中提到了这种技术,因此它应该得到支持和工作.奇怪的是,它适用于一对多关系,多对多具有显式链接表和2个一对多关联,但不适用于具有隐式链接表的多对多关系.

我没有解释为什么(没有找到相关文档).我也没有解释为什么,但将它与急切加载其他集合的请求相结合可以解决问题：

context.Entry(student).Collection(s => s.Grades)
    .Query().Where(g => g.Status == "A")
    .Include(g => g.Students)
    .Load();

这个的缺点(如评论中所提到的)是它还会加载很多属于加载等级的学生.

所以更好的方法是使用显式链接表和这样的关系：

模型：

public partial class Student
{
    public int StudentId { get; set; }
    public int CourseId { get; set; }
    public string Name { get; set; }
    public string Status { get; set; }
    public virtual ICollection<StudentGrade> StudentGrades { get; set; }
}

public partial class Grade
{
    public int GradeId { get; set; }
    public string Value { get; set; }
    public string Status { get; set; }
    public virtual ICollection<StudentGrade> StudentGrades { get; set; }
}

public class StudentGrade
{
    public int StudentId { get; set; }
    public int GradeId { get; set; }
    public virtual Student Student { get; set; }
    public virtual Grade Grade { get; set; }
}

组态：

modelBuilder.Entity<StudentGrade>()
   .ToTable("StudentGrades")
   .HasKey(e => new { e.GradeId,e.StudentId });

modelBuilder.Entity<StudentGrade>()
    .HasRequired(e => e.Grade)
    .WithMany(x => x.StudentGrades)
    .HasForeignKey(e => e.GradeId)
    .WillCascadeOnDelete();

modelBuilder.Entity<StudentGrade>()
    .HasRequired(e => e.Student)
    .WithMany(x => x.StudentGrades)
    .HasForeignKey(e => e.StudentId)
    .WillCascadeOnDelete();

现在显式加载不需要技巧,并且将加载已填充的GradeId和StudentId字段的已过滤的相关StudentGrade实体,从而避免加载其他Grade和Student对象：

context.Entry(student).Collection(s => s.StudentGrades)
    .Query().Where(sg => sg.Grade.Status == "A")
    .Load();

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!