欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Dapper 一对多查询 one to many

程序员文章站 2022-05-03 14:12:34
参考文档:Dapper one to many Table C Code pulic List GetPersons(){ var sql = @"SELECT 1 AS Id, 'Daniel Dennett' AS Name, 1942 AS Born, 1 AS CountryId, 'Uni ......

参考文档:dapper one to many

table

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; }
}

c# code

pulic list<person> getpersons(){
 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");

return remaininghorsemen.value.tolist(); 
}