Map a nested object using Dapper

Question

In my project I have two classes

public class Node
    {
        public int IdNode { get; set; }
        public string Description { get; set; }
        public int IdLocation { get; set; }
        public int IdNearStation { get; set; }
        public bool IsEnable { get; set; }
        public bool IsRealSensor { get; set; }
        public bool IsSprinklerON { get; set; }
        public bool IsLightOn { get; set; }
      

and this one

 public class DashboardNodeData
    {
        public Node Node { get; set; }
        public double Latitude { get; set; }
        public double Longitude { get; set; }
        public string County { get; set; }
        public string DistrictName { get; set; }
    }

And I want to execute this query

 SELECT N.IdNode
        , N.Description
        , N.IdLocation
        , N.IdNearStation
        , N.IsEnable
        , N.IsRealSensor
        , N.IsSprinklerON
        , N.IsLightOn
        , N.IsSecurityCameraOn
        , L.Latitude
        , L.Longitude
        , C.Name as County
        , D.DistrictName 
FROM [dbo].[Node] N
inner join [dbo].[Location] L on N.IdLocation = L.Id_Location
inner join District D on D.Id_District=L.Id_District
inner join Counties C on L.Id_Countie =C.CountyId
where N.IdNode=1

and map the result of this query to DashboardNodeData object

Using dapper Im doing in this way

using (IDbConnection db = new SqlConnection(_connectionString))
            {
                    string command = $@"  
                    SELECT N.IdNode
                           , N.Description
                           , N.IdLocation
                           , N.IdNearStation
                           , N.IsEnable
                           , N.IsRealSensor
                           , N.IsSprinklerON
                           , N.IsLightOn
                           , N.IsSecurityCameraOn
                           , L.Latitude
                           , L.Longitude
                           , C.Name as County
                           , D.DistrictName 
                    FROM [dbo].[Node] N
                    inner join [dbo].[Location] L on N.IdLocation = L.Id_Location
                    inner join District D on D.Id_District=L.Id_District
                    inner join Counties C on L.Id_Countie =C.CountyId
                    where N.IdNode=@idNode".Replace("@idNode",idNode.ToString());

                    var dashboard = db.Query<DashboardNodeData, Node, DashboardNodeData>(command, (dash, node) =>
                    {
                        dash.Node = node;
                        return dash;
                    }, splitOn:"Latitude,County").AsList();

                    return dashboard;

            }

The problem is That inside de Query function, my node object always come empty:

But if I copy and paste the exact same query in the database it is returned the output:

What Am I doing Wrong?

Answer 1

First, you have Node and DashboardNodeData switched. Dapper will map the first split to the first generic type and the second to the second and so on.

Secondly, you don't actually want to split on County, the latitude, longitude, county, and district are part of the same object (DashboardNodeData). It doesn't actually matter what tables the data is coming from, just the objects that you want to map to.

var dashboard = db.Query<Node, DashboardNodeData, DashboardNodeData>(command, (node, dash) =>
                {
                    dash.Node = node;
                    return dash;
                }, splitOn:"Latitude").AsList();