CODEWITHSUNDEEP
c#dapper
devspec
devspec

Reputation: 49

Dapper Multi Mapping by parent_id

I have an SQLite table "phrases" like:

CREATE TABLE [phrases] (
[id] INTEGER PRIMARY KEY AUTOINCREMENT, 
[phrase] CHAR, 
[parent_id] INTEGER);

and I fill it with demo data:

id  | phrase         | parent_id
1   | parent phrase  | (null)
2   | some child 1   | 1
3   | some child 2   | 1

And I have a C# model like:

class Phrase
{
    int id { get; set; }
    string phrase { get; set; }
    int parent_id { get; set; }
    Phrase parent { get; set; }
}

How can I correctly use Dapper to map this table to this class? Thanks.

Upvotes: 1

Views: 788

Answers (1)

Daniel Tshuva
Daniel Tshuva

Reputation: 503

You need add to your select query the phrase child (I use MS-SQL):

SELECT      p.id        AS id,
            p.phrase    AS phrase,
            p.parent_id AS parent_id,
            c.id        AS id,
            c.phrase    AS phrase
FROM        phrases p
LEFT JOIN   phrases c ON p.id = c.parent_id

Dapper is able to split the returned row by making an assumption that your Id columns are named Id or id (by default).

C#:

public IEnumerable<Phrase> GetPhrase()
    {
        const string sql = @"   SELECT      p.id        AS id,
                                            p.phrase    AS phrase,
                                            p.parent_id AS parent_id,
                                            c.id        AS id,
                                            c.phrase    AS phrase
                                FROM        phrases p
                                LEFT JOIN   phrases c ON p.id = c.parent_id";

        using (var connection = OpenConnection(_connectionString))
        {
            return connection.Query<Phrase, Phrase, Phrase>(sql, (p, c) =>
            {
                p.parent = c;

                return p;
            }, commandType: CommandType.Text);
        }
    }
  • This work only for one level child to parent (not for nested)

Upvotes: 1

Related Questions