Mapping 4 Tables to a single Object in Dapper

dapper mapping

Question

I have a quick question about Dapper. I have a query that returns 4 tables.

Three tables have just one integer column. Call them field1, field2, and field3.

The 4th table has 5 columns say: A,B,C,D,E.

I have made an object called ResultSet that has all the fields from the 4 tables

public class ResultSet
{
    int field1;
    int field2;
    int field3;
    string A;
    string B;
    string C;
    string D;
    string E
}

How do I map the results to the ResultSet object?

Currently I am using QueryMultiple to get the desired result. But it is only mapping the 1st 3 columns. A,B,C,D,and E are all null.

I do not want to use a Union to get all the Fields in just one single table.

Popular Answer

You should be able to achieve this by handing the connection.Query extension method an appropriate parameterised SQL statement, and pass it your object as the Type parameter.

Dapper will then map your query to the object magically, assuming you alias the items in the select list appropriately (ie, alias them with the corresponding property name of your object).

Something along these lines should work:

public class SomeObject
{
   public int Field1 {get; set;}
   public int Field2 {get; set;}
   public int A {get; set;}
   public int B {get; set;}
   public int C {get; set;}
   public int D {get; set;}
}

using(var connection = SomeConnectionFactory.GetConnection())
{
    var yourObject = 
        connection.Query<SomeObject>("select tab1.someThing as Field1, " +
                                     "tab2.someThing as Field2, " +
                                     "tab4.onePotato as A, " +
                                     "tab4.twoPotato as B, " +
                                     "tab4.threePotato as C, " +
                                     "tab4.four as D " +
                                     "from someTable tab1 " +
                                     "join someTable2 tab2 on tab1.Id = tab2.Id " +
                                     "$$ etc etc for the other joins $$" +
                                    "where tab1.Id = :ID " + ,new {ID = someId});
};

One note is that i've used the bind variable syntax for an Oracle database (:). You'll need to replace this with the equivalent for your DB.

Hope that's useful.



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