Dapper.NET Mapeo uno a muchos


Ejemplo

Veamos un ejemplo más complejo que contiene una relación de uno a varios. Nuestra consulta ahora contendrá varias filas que contienen datos duplicados y tendremos que manejar esto. Hacemos esto con una búsqueda en un cierre.

La consulta cambia ligeramente al igual que las clases de ejemplo.

Carné de identidad Nombre Nacido PaísId Nombre del país BookId Nombre del libro
1 Daniel Dennett 1942 1 Estados Unidos de America 1 Tormentas de ideas
1 Daniel Dennett 1942 1 Estados Unidos de America 2 Cuarto de la esquina
2 Sam Harris 1967 1 Estados Unidos de America 3 El paisaje moral
2 Sam Harris 1967 1 Estados Unidos de America 4 Despertar: una guía a la espiritualidad sin religión
3 Richard Dawkins 1941 2 Reino Unido 5 La magia de la realidad: Cómo sabemos lo que es realmente cierto
3 Richard Dawkins 1941 2 Reino Unido 6 Un apetito por la maravilla: la creación de un científico
public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Born { get; set; }
    public Country Residience { get; set; }
    public ICollection<Book> Books { get; set; }
}

public class Country
{
    public int CountryId { get; set; }
    public string CountryName { get; set; }
}

public class Book
{
    public int BookId { get; set; }
    public string BookName { get; set; }
}

Los remainingHorsemen diccionario se rellenarán con instancias completamente materializadas de los objetos personales. Para cada fila del resultado de la consulta, se pasan los valores asignados de las instancias de los tipos definidos en los argumentos lambda y depende de usted cómo manejar esto.

            var sql = @"SELECT 1 AS Id, 'Daniel Dennett' AS Name, 1942 AS Born, 1 AS CountryId, 'United States of America' AS CountryName, 1 AS BookId, 'Brainstorms' AS BookName
UNION ALL SELECT 1 AS Id, 'Daniel Dennett' AS Name, 1942 AS Born, 1 AS CountryId, 'United States of America' AS CountryName, 2 AS BookId, 'Elbow Room' AS BookName
UNION ALL SELECT 2 AS Id, 'Sam Harris' AS Name, 1967 AS Born, 1 AS CountryId,  'United States of America' AS CountryName, 3 AS BookId, 'The Moral Landscape' AS BookName
UNION ALL SELECT 2 AS Id, 'Sam Harris' AS Name, 1967 AS Born, 1 AS CountryId,  'United States of America' AS CountryName, 4 AS BookId, 'Waking Up: A Guide to Spirituality Without Religion' AS BookName
UNION ALL SELECT 3 AS Id, 'Richard Dawkins' AS Name, 1941 AS Born, 2 AS CountryId,  'United Kingdom' AS CountryName, 5 AS BookId, 'The Magic of Reality: How We Know What`s Really True' AS BookName
UNION ALL SELECT 3 AS Id, 'Richard Dawkins' AS Name, 1941 AS Born, 2 AS CountryId,  'United Kingdom' AS CountryName, 6 AS BookId, 'An Appetite for Wonder: The Making of a Scientist' AS BookName";

var remainingHorsemen = new Dictionary<int, Person>();
connection.Query<Person, Country, Book, Person>(sql, (person, country, book) => {
    //person
    Person personEntity;
    //trip
    if (!remainingHorsemen.TryGetValue(person.Id, out personEntity))
    {
        remainingHorsemen.Add(person.Id, personEntity = person);
    }

    //country
    if(personEntity.Residience == null)
    {
        if (country == null)
        {
            country = new Country { CountryName = "" };
        }
        personEntity.Residience = country;
    }                    

    //books
    if(personEntity.Books == null)
    {
        personEntity.Books = new List<Book>();
    }

    if (book != null)
    {
        if (!personEntity.Books.Any(x => x.BookId == book.BookId))
        {
            personEntity.Books.Add(book);
        }
    }

    return personEntity;
}, 
splitOn: "CountryId,BookId");

Observe que el argumento splitOn es una lista delimitada por comas de las primeras columnas del siguiente tipo.