CODEWITHSUNDEEP
sqldapper
Tom
Tom

Reputation: 53

Dapper.NET column alias not mapped correctly

I've got a problem executing a query with Dapper.net. In the code sample below, you can see a few subselects that I have aliased to the column name I want, and which also correspond exactly to the property name of my object.

However, in the result, none of the properties which correspond to the subqueries are filled out correctly (always value 0).

I have profiled the query as well, and if I execute the query which I saw in the sql profiler, the results are correct (ie. for example NrEvents does have a value where as in my resulting objects, it is always 0).

Also, the mapping of the TvLogLockInfo with EMUser is correct, that gets translated fine, so there is not the issue.

EDIT: Actually..if I remove the mapping of EMUser, the subquery values DO get filled out.. So the question is, how to add the mapping between TvLogLockInfo and EMUser and keep the subquery values filled out..?

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

Here is the full search function:

        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;

    }

And this is the object I want the results in :

    [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; }

}

Any ideas?

Thanks, Tom

Upvotes: 3

Views: 2145

Answers (1)

Alex
Alex

Reputation: 8116

The problem could be the ordering of your query and the splitOn parameter. You are using 'LockedByUserId' that means, all columns AFTER (inclusive) the splitOn parameter belong to the EMUser object. Thats why the mapping for EMUser works but not for the other columns coming after the splitOn key.

Try it with using EvoMonUserId as your splitOn parameter.

Upvotes: 2

Related Questions