sql - Dapper.NET 列别名未正确映射

Question

我在使用 Dapper.net 执行查询时遇到问题。在下面的代码示例中，您可以看到我为所需的列名称设置了别名的一些子选择，它们也与我的对象的属性名称完全对应。

但是，在结果中，没有正确填写与子查询对应的属性(始终为 0)。

我也分析了查询，如果我执行我在 sql 分析器中看到的查询，结果是正确的(例如，NrEvents 确实有一个值，而在我的结果对象中，它总是 0 ).

此外，TvLogLockInfo 与 EMUser 的映射是正确的，可以很好地翻译，因此不存在问题。

编辑:实际上..如果我删除 EMUser 的映射，子查询值确实会被填充..所以问题是，如何添加 TvLogLockInfo 和 EMUser 之间的映射并保持子查询值已填写..？

// this query will give me the correct values, but I lose the mapping of EMUser
res = ctx.Connection.Query<TvLogLockInfo>(query + where, qParams).ToList();

这是完整的搜索功能:

        public List<TvLogLockInfo> SearchTvLogs(DateTime? dateFrom, DateTime? dateUntil, List<int> stationIds, bool isLockedOnly)
    {
        List<TvLogLockInfo> res;

        const string query =  " SELECT       /* tv log */                                                                                                                                                                                       " +
                        "               L.TvLogId, L.ReferenceDay, L.StationId, L.IsLocked, l.LockedDate, l.LockedByUserId,                                                                                                             " +
                        "               /* all events */                                                                                                                                                                                " +
                        "               (SELECT COUNT(TvLogEventId) FROM TvLogEvent E WITH (NOLOCK) WHERE E.TvLogId = L.TvLogId) AS NrEvents,                                                                                           " +
                        "               (SELECT COUNT(TvLogEventId) FROM TvLogEvent E WITH (NOLOCK) WHERE E.TvLogId = L.TvLogId AND E.IsTimeSet = 1) AS NrEventsTimeSet,                                                                " +
                        "               /* spots */                                                                                                                                                                                     " +
                        "               (SELECT COUNT(TvLogEventId) FROM TvLogEvent E WITH (NOLOCK) WHERE E.TvLogId = L.TvLogId  AND E.ThesaurusTypeEMListValueId = @spot AND E.MatchingInfoId IS NULL) AS NrSpotsNotMatched,           " +
                        "               (SELECT COUNT(TvLogEventId) FROM TvLogEvent E WITH (NOLOCK)                                                                                                                                     " +
                        "                                           INNER JOIN MatchingInfo I WITH (NOLOCK) ON E.MatchingInfoId = I.MatchingInfoId AND I.MatchStatusEMListValueId = @matchNew                                           " +
                        "                                           WHERE E.TvLogId = L.TvLogId AND E.ThesaurusTypeEMListValueId = @spot) AS NrSpotsMatchedNew,                                                                         " +
                        "               (SELECT COUNT(TvLogEventId) FROM TvLogEvent E WITH (NOLOCK)                                                                                                                                     " +
                        "                                           INNER JOIN MatchingInfo I WITH (NOLOCK) ON E.MatchingInfoId = I.MatchingInfoId AND I.MatchStatusEMListValueId = @matchValidated                                     " +
                        "                                           WHERE E.TvLogId = L.TvLogId AND E.ThesaurusTypeEMListValueId = @spot) AS NrSpotsMatchedValidated,                                                                   " +
                        "               /* autopromo */                                                                                                                                                                                 " +
                        "               (SELECT COUNT(TvLogEventId) FROM TvLogEvent E WITH (NOLOCK) WHERE E.TvLogId = L.TvLogId  AND E.ThesaurusTypeEMListValueId = @autopromo AND E.MatchingInfoId IS NULL) AS NrAutoPromoNotMatched,  " +
                        "               (SELECT COUNT(TvLogEventId) FROM TvLogEvent E WITH (NOLOCK)                                                                                                                                     " +
                        "                                           INNER JOIN MatchingInfo I WITH (NOLOCK) ON E.MatchingInfoId = I.MatchingInfoId AND I.MatchStatusEMListValueId = @matchNew                                           " +
                        "                                           WHERE E.TvLogId = L.TvLogId AND E.ThesaurusTypeEMListValueId = @autopromo) AS NrAutoPromoMatchedNew,                                                                " +
                        "               (SELECT COUNT(TvLogEventId) FROM TvLogEvent E WITH (NOLOCK)                                                                                                                                     " +
                        "                                           INNER JOIN MatchingInfo I WITH (NOLOCK) ON E.MatchingInfoId = I.MatchingInfoId AND I.MatchStatusEMListValueId = @matchValidated                                     " +
                        "                                           WHERE E.TvLogId = L.TvLogId AND E.ThesaurusTypeEMListValueId = @autopromo) AS NrAutoPromoMatchedValidated,                                                          " +
                        "               /* user */                                                                                                                                                                                      " +
                        "               U.*                                                                                                                                                                                             " +
                        " FROM          TvLog L WITH (NOLOCK)                                                                                                                                                                           " +
                        " LEFT JOIN EMUser U WITH (NOLOCK) ON L.LockedByUserId = U.EvoMonUserId                                                                                                                                         ";

        string where = string.Empty;
        DynamicParameters qParams = new DynamicParameters();
        qParams.AddDynamicParams(new { spot = (int)Enums.ThesaurusTypeList.Spot });
        qParams.AddDynamicParams(new { autopromo = (int)Enums.ThesaurusTypeList.AutoPromotion });
        qParams.AddDynamicParams(new { matchNew = (int)Enums.MatchingStatus.New });
        qParams.AddDynamicParams(new { matchValidated = (int)Enums.MatchingStatus.Validated });


        if (dateFrom.HasValue)
        {
            where += " L.ReferenceDay >= @dateFrom ";
            qParams.AddDynamicParams(new { dateFrom = dateFrom });
        }
        if (dateUntil.HasValue)
        {
            where += string.Format(" {0} L.ReferenceDay <= @dateUntil ", (string.IsNullOrWhiteSpace(where) ? string.Empty : "AND"));
            qParams.AddDynamicParams(new {dateUntil = dateUntil});
        }
        if (stationIds != null && stationIds.Count > 0)
        {
            where += string.Format(" {0} L.StationId IN @stationList ", (string.IsNullOrWhiteSpace(where) ? string.Empty : "AND"));
            qParams.AddDynamicParams(new {stationList = stationIds});
        }
        if (isLockedOnly)
        {
            where += string.Format(" {0} L.IsLocked = 1 ", (string.IsNullOrWhiteSpace(where) ? string.Empty : "AND"));                                
        }
        if (!string.IsNullOrWhiteSpace(where)) where = " WHERE " + where;

        using (var ctx = new DapperContext())
        {
            res = ctx.Connection.Query<TvLogLockInfo, EMUser, TvLogLockInfo>(query + where,
                                                                                (tvLog, emuser) =>
                                                                                {
                                                                                    tvLog.LockedByUser = emuser;
                                                                                    return tvLog;
                                                                                },qParams,
                                                                                splitOn: "LockedByUserId").ToList();
        }

        return res;

    }

这是我想要结果的对象:

    [Serializable]
public class TvLogLockInfo : EntityBase
{
    public int TvLogId { get; set; }
    public DateTime ReferenceDay { get; set; }
    public int StationId { get; set; }
    public bool IsLocked { get; set; }
    public DateTimeOffset? LockedDate { get; set; }
    public EMUser LockedByUser { get; set; }

    public int NrEvents { get; set; }
    public int NrEventsTimeSet { get; set; }
    public int NrSpotsNotMatched { get; set; }
    public int NrSpotsMatchedNew { get; set; }
    public int NrSpotsMatchedValidated { get; set; }
    public int NrAutoPromoNotMatched { get; set; }
    public int NrAutoPromoMatchedNew { get; set; }
    public int NrAutoPromoMatchedValidated { get; set; }

}

有什么想法吗？

谢谢，汤姆

Answer 1

问题可能出在查询的顺序和 splitOn 参数上。您正在使用“LockedByUserId”，这意味着，splitOn 参数之后(含)的所有列都属于 EMUser 对象。这就是为什么 EMUser 的映射有效，但对 splitOn 键之后的其他列无效。

尝试使用 EvoMonUserId 作为您的 splitOn 参数。