Geek Answers Handbook

Dapper Nested Object Query - Not Filling All Properties

I am trying to return a flat result set to a nested object using Dapper.NET (EmergingIssue contains Reason and Status objects), but the query does not fill all the columns. See 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 am trying to project a result set into 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 returns multiple fields, but UserComment, PlannerID, DateItemAdded, and several other fields are not populated. It seems obvious that the problem is with the Linq function, which projects the data, but I am not familiar with the syntax enough to understand how to solve it.

Also, is there a valuable Linq resource for learning more advanced features? I often use .Where(x => x.StringValue == "Test") (and other similar, simple) functions, for example, but I do not often pass functions like the one I'm trying to do above.

+8
c # linq dapper
source share
2 answers

The problem is here:

splitOn: "ReasonID, StatusID"

You cannot add spaces inside the splitOn line, because Dapper just separates this line with commas - it will look for a column called " StatusId" (with a space at the beginning)

+7
source share

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

I also find out that this guy is creating a big extension. https://github.com/ronnieoverby/UniqueNamespace.SqlBuilder

+1
source share

More articles:


All Articles