5

I need to get a complex array of objects.

These are my 2 classes

public class Geometria
{
    public int idGeom { get; set; }
    public int idMatriz { get; set; }
    public string Referencia { get; set; } 
    public int toleInf { get; set; }
    public int toleSup { get; set; }
    public ICollection<MedidaX> Medidas { get; set; }
}

public class MedidaX
{
    public int IdRegGeo { get; set; }
    public float X { get; set; }
}

My intended result when returned as a json is basically this

    {
  "Geometrias": [
    {
    "idGeom": 1,
    "idMatriz": 2,
    "Referencia": "M130069",
    "toleInf": 2,
    "toleSup": 7,
    "Medidas":[
      { "IdRegGeo": 1, "X": 5.5 },
      { "IdRegGeo": 2, "X": 7.4 },
      { "IdRegGeo": 3, "X": 5.5 }
    ]
    },
    {
    "idGeom": 2,
    "idMatriz": 2,
    "Referencia": "M130070",
    "toleInf": 1,
    "toleSup": 7,
    "Medidas":[
      { "IdRegGeo": 4, "X": 3.5 },
      { "IdRegGeo": 5, "X": 4.2 },
      { "IdRegGeo": 6, "X": 7.4 }
      ]
    }
  ]
}

here is the sql query result

 idGeom idMatriz    referencia  toleInf toleSup idRegGeo X
2002    2           M130342         2   7       1        9,81
2002    2           M130342         2   7       2        9,8
2002    2           M130342         2   7       3        3,25
2003    2           M130344         1   7       4        2
2002    2           M130342         2   7       5        1,75
2003    2           M130344         1   7       6        4,75
2002    2           M130342         2   7       7        2,78
2003    2           M130344         1   7       8        42,21
2002    2           M130342         2   7       9        7
2003    2           M130344         1   7       10       7
2002    2           M130342         2   7       11       3,55
2003    2           M130344         1   7       12       5,5

I can't seem to figure out how to make this in dapper, i only have been using it for simple selects but now i need nested object to iterate through dynamic graphics.

EDIT Also I forgot to add the primary key of the joined table so i edited to fix it

Here is final result

// Connection String 
    private static string ConnectionString = ConfigurationManager.ConnectionStrings["NOPAPER_ConnectionString"].ConnectionString;

    [WebMethod]
    public void GetRegGeo(string matriz)
    {
        string cmdText = @"SELECT           
        hg.idGeom,          
        hg.idMatriz,
        hg.referencia,
        hg.toleInf,
        hg.toleSup,
        hrg.idRegGeo,
        ROUND(CAST((hrg.x1+hrg.x2+hrg.x3+hrg.x4)/4  AS FLOAT),2) AS X
    FROM hRegGeometrias hrg
    INNER JOIN hGeometria hg 
    ON hrg.idGeometria = hg.idGeom
    WHERE hrg.idMatriz = 2
    GROUP BY hg.referencia, hg.toleInf, hg.toleSup, hrg.x1, hrg.x2, hrg.x3, hrg.x4, hrg.idRegGeo, hg.idMatriz, hg.idGeom, hrg.idRegisto
    ORDER BY Referencia, IdRegisto ASC";

        var lookup = new Dictionary<int, Geometria>();
        using (IDbConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["NOPAPER_ConnectionString"].ConnectionString))
        {
            var multi = connection.Query<Geometria, MedidaX, Geometria>(cmdText,
                                        (geometria, medida) =>
                                        {
                                            Geometria current;
                                            if (!lookup.TryGetValue(geometria.idGeom, out current))
                                            {
                                                lookup.Add(geometria.idGeom, current = geometria);
                                                current.Medidas = new List<MedidaX>();
                                            }
                                            current.Medidas.Add(medida);
                                            return current;
                                        }, splitOn: "idRegGeo").Distinct();            
        }


        var geosList = Database.Query<Geometria>("SP_Geometrias", new { Action = "GETREGGEO", idMatriz = matriz }).ToList();
        JavaScriptSerializer js = new JavaScriptSerializer();
        Context.Response.Write(js.Serialize(geosList));
    }

works like a charm :)

Jackal
  • 3,359
  • 4
  • 33
  • 78
  • 4
    https://stackoverflow.com/questions/35671219/multi-mapping-query-with-dapper/35671402#35671402 – Steve May 10 '19 at 09:18
  • thanks man, i wish i could set your answer as correct one tho. I managed to follow that example and fixed. I'll edit my question 'cause i was missing the primary key from the joined table :) – Jackal May 10 '19 at 09:44
  • 1
    Glad to be of help. If you find answers useful you can still upvote them – Steve May 10 '19 at 10:01

0 Answers0