Dapper Syntax for Nested Multi Mapping

Question

    class Person
    {
        Address Addr { get; set; }
        int Age { get; set; }
    }

    class Address 
    {
        string StreetName { get; set; }
        County Cnty { get; set; }
    }

    class County
    {
         string CntyName;
         string CntyCode;
    }

Here is my Stored Proc which populates the data from Database.

    Create Procedure SpGetAllPersons
    As
    Select CntyName, CntyCode, StreetName, Age from Persons

I tried to write below dapper query but getting an exception

    DBConn.Query<County, Address , Person, Person>
    (DomainConstants.SpGetAllPersons,
    (cnty, address, person) =>
    {
            address.Cnty = cnty;
            person.Addr = address;
            return person;
    },
    commandType: CommandType.StoredProcedure,
    splitOn: "StreetName, Age").ToList();

I tried to use below concept, but it returns single object only. I need a list of persons.

     var sql =
    @"select 
        1 as PersonId, 'bob' as Name, 
        2 as AddressId, 'abc street' as Name, 1 as PersonId,
        3 as Id, 'fred' as Name
        ";
                var personWithAddress = connection.Query<Person, Address, Extra, Tuple<Person, Address, Extra>>
                    (sql, (p, a, e) => Tuple.Create(p, a, e), splitOn: "AddressId,Id").First();

Thanks in advance.

score 11 · Accepted Answer · answered Jul 28 '11 at 23:03

Thanks Mark and Bob for jumping on it. I was able to resolve the issue other way:

    DBConn.Query(DomainConstants.SpGetAllPersons, commandType: CommandType.StoredProcedure)
    .Select(x => new Person
    {
        Addr = new Address
        {
            Cnty = new County
            {
                CntyName = x.CntyName,
                CntyCode = x.CntyCode
            },
            StreetName = x.StreetName
        },
        Age = x.Age
    }).ToList();

Honestly I don't see the point of this block. It should automatically map, that's the beauty of dapper ;) — coffekid, Jan 18 '13 at 16:48

score -1 · Answer 2 · answered Jul 28 '11 at 07:33

-1

Well, in the example you are only selecting a single row, so that won't ever return more than one. However, Query<T> returns an IQueryable<T>. You are calling .First(), so it isn't surprising that you get exactly one result. If you change the last part of your statement to use .ToList() you will get a list with one item per row.

answered Jul 28 '11 at 07:33

Marc Gravell

1,026,079
266
2,566
2,900

Thanks Mark. If you see the query I wrote, It does have ToList() at the end. At the end, I just mentioned the Concept which I could found on Dapper Tests repository. I already tried that with ToList() but I am not getting expected object. All I am having is an exception. I tried to debug SQLMapper and getting error while Desierializing the class. – Rakshit Bakshi Jul 28 '11 at 21:36
1

@Rakshit if you give me the full exception details, and enough of your classes to make sense of it, I can probably help more – Marc Gravell Jul 28 '11 at 21:57
1

@Rakshit in all honesty, saying "an exception" is really not in the least bit helpful :) The exact message (and ideally stacktrace), copy/paste verbatim without interpretation etc is best – Marc Gravell Jul 28 '11 at 21:58
@Mark: Aren't provided classes and table structure seen from stored procedure body enough to test? Just create a table with those 4 columns and same 3 classes and you should be good to test his C# code... – Robert Koritnik Jul 28 '11 at 22:23
1

@Robert possibly; I'll check after some sleep – Marc Gravell Jul 28 '11 at 22:47
Sorry Mark for not providing detailed error message, I found the solution other way. – Rakshit Bakshi Jul 28 '11 at 23:04

Dapper Syntax for Nested Multi Mapping

2 Answers2