Dapper nested object query - not populating all properties

c# dapper linq

Question

I am trying to return a flat result set into a nested object with Dapper.NET (EmergingIssue contains both Reason and Status objects), but the query is not populating all the columns. See POCOs below:

public class EmergingIssue
{
    public string Category;
    public string Brand;
    public string Sku;
    public string SkuDesc;
    public string ManufacturingSite;
    public double? CurrentAvailableWfc;
    public Reason Reason;
    public Status Status;
    public double? TargetWfc;
    public DateTime? DateItemAdded;
    public string UserComment;
    public string PlannerID;
}

public class Reason
{
    public int ReasonId;
    public string ReasonDesc;
}

public class Status
{
    public int StatusId;
    public string StatusDesc;
}

Below is the code where I'm attempting to project the result set to my EmergingIssue class (adopted from a similar StackOverflow question):

public static List<EmergingIssue> GetEmergingIssues()
{
    using (var conn = new OracleConnection(Constant.DatabaseConnection()))
    {
        conn.Open();
        StringBuilder sql = new StringBuilder();

        sql.Append("SELECT ei.CATEGORY, ei.BRAND, ei.SKU, ei.SKU_DESC SkuDesc");
        sql.Append(" ,MANUFACTURING_SITE ManufacturingSite, CURRENT_AVAILABLE_WFC CurrentAvailableWfc");
        sql.Append(" ,ei.TARGET_WFC TargetWFC");
        sql.Append(" ,ei.DATE_ITEM_ADDED DateItemAdded");
        sql.Append(" ,ei.USER_COMMENT UserComment");
        sql.Append(" ,ei.PLANNER_ID PlannerID");
        sql.Append(" ,ei.REASON_ID ReasonID, r.REASON_DESC ReasonDesc");
        sql.Append(" ,ei.STATUS_ID StatusID, s.STATUS_DESC StatusDesc");
        sql.Append(" FROM EMERGING_ISSUE ei");
        sql.Append(" LEFT OUTER JOIN EMERGING_ISSUE_STATUS s ON s.STATUS_ID = ei.STATUS_ID");
        sql.Append(" LEFT OUTER JOIN MERGING_ISSUE_REASON r ON r.REASON_ID = ei.REASON_ID");

        List<EmergingIssue> emergingIssues = conn.Query<EmergingIssue, Reason, Status, EmergingIssue>(sql.ToString(),
            (ei, r, s) =>
            {
                ei.Reason = r;
                ei.Status = s;
                return ei;
            },
            splitOn: "ReasonID, StatusID").ToList();

        return emergingIssues;
    }
}

This result is returning several fields, but UserComment, PlannerID, DateItemAdded, and several other fields aren't populated. It seems obvious that the problem lies in the Linq function that's projecting the data, but I'm not familiar enough with the syntax to understand how to resolve it.

Furthermore, is there a valuable Linq resource for learning about more advanced functionality? I frequently use .Where(x => x.StringValue == "Test") (and other similar, simple) functionality, for instance, but I don't often pass functions like I'm attempting above.

Accepted Answer

The problem is here:

splitOn: "ReasonID, StatusID"

You cannot add any whitespaces inside the splitOn string as the Dapper simply splits this string by commas - it will look for a column named " StatusId" (with a space in the beginning)


Popular Answer

Instead of creating a queries by using StringBuilder, why don't you try using SqlBuilder class. It will provide you with more readable query creation in my point of view.

I also find out that this guy, create a great extension of it. https://github.com/ronnieoverby/UniqueNamespace.SqlBuilder



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why