Dapper .NET Core 中的多映射问题-解网

问：

我有以下 2 个模型类：

public class EnrollableClass
{
    public long EnrollableClassId { get; set; }
    public string ClassEnrollmentCode { get; set; } = string.Empty;
    public string ClassTitle { get; set; } = string.Empty;
    public string ClassGrade { get; set; } = string.Empty;
    public DateTime CreatedDate { get; set; } = DateTime.UtcNow;
    public DateTime LastUpdated { get; set; } = DateTime.UtcNow;
    public ICollection<EnrollableClassTeacher> EnrollableClassTeachers { get; set; }
    public ICollection<ClassUnit> ClassUnits { get; set; } = new List<ClassUnit>();
    public ICollection<EnrollableClassStudent> EnrollableClassStudents { get; set; }
}

public class ClassUnit
{
    public long UnitId { get; set; }
    public string UnitTitle { get; set; }
    public string StudentVisibility { get; set; }
    public long EnrollableClassId { get; set; }
    public EnrollableClass EnrollableClass { get; set; }
}

我试图使用它的单位回来，但它没有映射 ClassUnit（s）：EnrollableClass

public async Task<EnrollableClass> GetEnrollleableClassWithUnitData(long EnrollableClassId)
{
    var query = @"SELECT EC.[EnrollableClassId], [ClassEnrollmentCode], [ClassTitle], [ClassGrade], [CreatedDate], [LastUpdated],
                    [ClassUnitId], CU.[EnrollableClassId], [UnitCMSDocId], [IsVisible]
                    FROM [spark].[EnrollableClass] EC
                    LEFT JOIN spark.ClassUnit CU ON EC.EnrollableClassId=CU.EnrollableClassId
                    WHERE EC.EnrollableClassId=@EnrollableClassId";

    using (var connection = _context.CreateConnection())
    {
         var result = await connection.QueryAsync<EnrollableClass, ClassUnit, EnrollableClass>(query,
                (enrollableClass, classUnit) =>
                {
                    enrollableClass.ClassUnits.Add(classUnit);
                    return enrollableClass;
                },
                new { EnrollableClassId = EnrollableClassId },
                splitOn: "EnrollableClassId, ClassUnitId");
            return result.FirstOrDefault();
    }
}

作为记录，这是查询的结果集：

EnrollableClassId   ClassEnrollmentCode     ClassTitle  ClassGrade  
CreatedDate     LastUpdated     ClassUnitId     EnrollableClassId   
UnitCMSDocId    IsVisible

1   R8W60G  asdf    5th 2023-10-28 16:32:21.517 2023-10-28 16:32:21.517 2   1   155 1

1   R8W60G  asdf    5th 2023-10-28 16:32:21.517 2023-10-28 16:32:21.517 3   1   156 1

运行时没有任何反应，它会抛出 classUnit 为 null 的异常。我尝试了各种 splitOn 参数，包括 just 和 just 以及组合。这是一对多，所以我一定错过了一些明显的东西。EnrollableClassIDClassUnitId

sql . .net-core dapper

public async Task<EnrollableClass> GetEnrolleableClassWithUnitData(long EnrollableClassId)
{
    var query = @"SELECT EC.[EnrollableClassId], [ClassEnrollmentCode], [ClassTitle], [ClassGrade], [CreatedDate], [LastUpdated],
                    [ClassUnitId], CU.[EnrollableClassId], [UnitCMSDocId], [IsVisible]
                    FROM [spark].[EnrollableClass] EC
                    LEFT JOIN spark.ClassUnit CU ON EC.EnrollableClassId=CU.EnrollableClassId
                    WHERE EC.EnrollableClassId=@EnrollableClassId";

    using (var connection = _context.CreateConnection())
    {
         EnrollableClass enrollableClass = null;
         var result = await connection.QueryAsync<EnrollableClass, ClassUnit, EnrollableClass>(query,
                (e, classUnit) =>
                {
                    if (enrollableClass = null)
                    {
                        enrollableClass = e;
                    }
                    enrollableClass.ClassUnits.Add(classUnit);
                    return null;
                },
                new { EnrollableClassId = EnrollableClassId },
                splitOn: "ClassUnitId");
        return enrollableClass;
    }
}

诀窍是只创建一个并将单位添加到该单位。map 函数可以返回 null，因为您没有使用其输出，并且只需要一个 splitOn 即可将结果集一分为二。EnrollableClass

0赞 Josiah Hooten 11/1/2023 #2

我在这个 youtube 视频中找到了答案。问题在于它是多对一的，这在 Dapper 文档中被奇怪地省略了，尽管它类似于它们对多对多所做的。

这是我有效的代码：

public async Task<IEnumerable<TeacherClassViewModel>> GetAllTeachersClasses(long teacherUserId)
        {
            var query = @"SELECT EC.[EnrollableClassId] As EnrolledClassId, [ClassTitle], [ClassEnrollmentCode], [ClassGrade]
                            FROM spark.EnrollableClassTeacher ECT
                            LEFT JOIN [spark].[EnrollableClass] EC ON EC.EnrollableClassId=ECT.EnrollableClassId
                            WHERE ECT.UserAccountId=@teacherUserId
                            GROUP BY EC.[EnrollableClassId], [ClassTitle], [ClassEnrollmentCode], [ClassGrade]";
            
            var unitQuery = @"SELECT EC.[EnrollableClassId], ClassUnitId
                            FROM spark.EnrollableClassTeacher ECT
                            JOIN [spark].[EnrollableClass] EC ON EC.EnrollableClassId=ECT.EnrollableClassId
                            JOIN spark.ClassUnit CU ON EC.EnrollableClassId=CU.EnrollableClassId
                            WHERE ECT.UserAccountId=@teacherUserId
                            GROUP BY EC.[EnrollableClassId], ClassUnitId";

            using (var connection = _context.CreateConnection())
            {
                var result = await connection.QueryAsync<TeacherClassViewModel>(query, new { teacherUserId = teacherUserId });
                var unitResult = await connection.QueryAsync<ClassUnitRecord>(unitQuery, new { teacherUserId = teacherUserId });

                foreach (var iClass in result)
                {
                    iClass.Units = unitResult.Where(x => x.EnrollableClassId == iClass.EnrolledClassId && x.ClassUnitId != 0).Select(x => x.ClassUnitId).ToList();
                }
                return result;
            }
        }

上一个：带有使用“子字符串”字的 SQL 请求的 C# .Net OleDbCommand 不适用于 DB2/400 数据库

下一个：检查要包含在解决方案生成中的生成错误，以便在 sql 文件中出现任何错误时生成失败

Dapper .NET Core 中的多映射问题

Trouble multi-mapping in Dapper .NET Core

评论