[C#/COMMON/DAPPER] 자식 리스트를 포함해 조회하기

■ 자식 리스트를 포함해 조회하는 방법을 보여준다.

▶ 자식 리스트를 포함해 조회하기 예제 (C#)


using System.Collections.Generic;
using System.Data;

public class CodeGroupModel
{
    public string ID { get; set; }

    ...

    public List<CodeModel> CodeList { get; set; }
}

public class CodeModel
{
    public string ID { get; set; }

    public string GROUP_ID { get; set; }

    ...
}

...

private IDbConnection connetcion;

...

string sql = @"
SELECT
    PARENT.*
   ,CHILD.*
FROM      COM_CODE       CHILD
LEFT JOIN COM_CODE_GROUP PARENT ON PARENT.ID = CHILD.GROUP_ID
WHERE PARENT.ID = @ID
ORDER BY
    PARENT.ID ASC
   ,CHILD.ID  ASC;
";

var result = this.connection.QueryIncludingChildList<CodeGroupModel, string, CodeModel>
(
    sql,
    codeGroup => codeGroup.ID,
    codeGroup => {

        if(codeGroup.CodeList == null)
        {
            codeGroup.CodeList = new List<CodeModel>();
        }

        return codeGroup.CodeList;
    },
    parameter : new { ID = id },
    splitOn : "ID"
);

using System.Collections.Generic;

using System.Data;

public class CodeGroupModel

{

public string ID { get; set; }

...

public List<CodeModel> CodeList { get; set; }

}

public class CodeModel

{

public string ID { get; set; }

public string GROUP_ID { get; set; }

...

}

...

private IDbConnection connetcion;

...

string sql = @"

SELECT

PARENT.*

,CHILD.*

FROM COM_CODE CHILD

LEFT JOIN COM_CODE_GROUP PARENT ON PARENT.ID = CHILD.GROUP_ID

WHERE PARENT.ID = @ID

ORDER BY

PARENT.ID ASC

,CHILD.ID ASC;

var result = this.connection.QueryIncludingChildList<CodeGroupModel, string, CodeModel>

(

sql,

codeGroup => codeGroup.ID,

codeGroup => {

if(codeGroup.CodeList == null)

{

codeGroup.CodeList = new List<CodeModel>();

}

return codeGroup.CodeList;

parameter : new { ID = id },

splitOn : "ID"

);

※ COM_CODE_GROUP 테이블은 CodeGroupModel 타입에 대응하고 PK는 ID이다.
※ COM_CODE 테이블은 CodeModel 타입에 대응하고 PK는 ID, FK는 GROUP_ID이다.
※ SQL문에서 부모 테이블 필드를 먼저 나열하고 자식 테이블 필드는 다음에 나열해야 한다.

▶ 자식 리스트를 포함해 조회하기 (C#)


using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

using Dapper;

#region 자식 리스트를 포함해 조회하기 - QueryIncludingChildList<TParent, TChild, TParentKey>(connection, sql, parentKeySelector, childSelector, parameter, transaction, buffered, splitOn, commandTimeout, commandType)

/// <summary>
/// 자식 리스트를 포함해 조회하기
/// </summary>
/// <typeparam name="TParent">부모 타입</typeparam>
/// <typeparam name="TChild">자식 타입</typeparam>
/// <typeparam name="TParentKey">부모 키 타입</typeparam>
/// <param name="connection">연결</param>
/// <param name="sql">SQL</param>
/// <param name="parentKeySelector">부모 키 셀렉터</param>
/// <param name="parentChildListSelector">부모 자식 리스트 셀렉터</param>
/// <param name="parameter">매개 변수</param>
/// <param name="transaction">트랜잭션</param>
/// <param name="buffered">버퍼링 여부</param>
/// <param name="splitOn">분리자</param>
/// <param name="commandTimeout">명령 타임아웃</param>
/// <param name="commandType">명령 타입</param>
/// <returns>부모 리스트</returns>
public static List<TParent> QueryIncludingChildList<TParent, TParentKey, TChild>
(
    this IDbConnection           connection,
    string                       sql,
    Func<TParent, TParentKey>    parentKeySelector,
    Func<TParent, IList<TChild>> parentChildListSelector,
    dynamic                      parameter = null,
    IDbTransaction               transaction = null,
    bool                         buffered = true,
    string                       splitOn = "ID",
    int?                         commandTimeout = null,
    CommandType?                 commandType = null
)
{
    Dictionary<TParentKey, TParent> parentDictionary = new Dictionary<TParentKey, TParent>();

    connection.Query<TParent, TChild, TParent>
    (
        sql,
        (parent, child) => {

            TParentKey parentKey = parentKeySelector(parent);

            if(parentDictionary.ContainsKey(parentKey))
            {
                IList<TChild> parentChildList = parentChildListSelector(parentDictionary[parentKey]);

                parentChildList.Add(child);
            }
            else
            {
                parentDictionary.Add(parentKey, parent);

                IList<TChild> parentChildList = parentChildListSelector(parent);

                parentChildList.Add(child);
            }

            return parent;
        },
        parameter as object,
        transaction,
        buffered,
        splitOn,
        commandTimeout,
        commandType
    );

    return parentDictionary.Values.ToList();
}

#endregion

using System;

using System.Collections.Generic;

using System.Data;

using System.Linq;

using Dapper;

#region 자식 리스트를 포함해 조회하기 - QueryIncludingChildList<TParent, TChild, TParentKey>(connection, sql, parentKeySelector, childSelector, parameter, transaction, buffered, splitOn, commandTimeout, commandType)

/// <summary>

/// 자식 리스트를 포함해 조회하기

/// </summary>

/// <typeparam name="TParent">부모 타입</typeparam>

/// <typeparam name="TChild">자식 타입</typeparam>

/// <typeparam name="TParentKey">부모 키 타입</typeparam>

/// <param name="connection">연결</param>

/// <param name="sql">SQL</param>

/// <param name="parentKeySelector">부모 키 셀렉터</param>

/// <param name="parentChildListSelector">부모 자식 리스트 셀렉터</param>

/// <param name="parameter">매개 변수</param>

/// <param name="transaction">트랜잭션</param>

/// <param name="buffered">버퍼링 여부</param>

/// <param name="splitOn">분리자</param>

/// <param name="commandTimeout">명령 타임아웃</param>

/// <param name="commandType">명령 타입</param>

/// <returns>부모 리스트</returns>

public static List<TParent> QueryIncludingChildList<TParent, TParentKey, TChild>

(

this IDbConnection connection,

string sql,

Func<TParent, TParentKey> parentKeySelector,

Func<TParent, IList<TChild>> parentChildListSelector,

dynamic parameter = null,

IDbTransaction transaction = null,

bool buffered = true,

string splitOn = "ID",

int? commandTimeout = null,

CommandType? commandType = null

)

{

Dictionary<TParentKey, TParent> parentDictionary = new Dictionary<TParentKey, TParent>();

connection.Query<TParent, TChild, TParent>

(

sql,

(parent, child) => {

TParentKey parentKey = parentKeySelector(parent);

if(parentDictionary.ContainsKey(parentKey))

{

IList<TChild> parentChildList = parentChildListSelector(parentDictionary[parentKey]);

parentChildList.Add(child);

}

else

{

parentDictionary.Add(parentKey, parent);

IList<TChild> parentChildList = parentChildListSelector(parent);

parentChildList.Add(child);

}

return parent;

parameter as object,

transaction,

buffered,

splitOn,

commandTimeout,

commandType

);

return parentDictionary.Values.ToList();

}

#endregion