첨부 실행 코드는 나눔고딕코딩 폰트를 사용합니다.
728x90
반응형
728x170

■ 자식 리스트를 포함해 조회하기 예제

▶ 예제 모델

using System.Collections.Generic;

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

    ...
}

 

728x90

 

▶ 예제 코드

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

...

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문에서 부모 테이블 필드를 먼저 나열하고 자식 테이블 필드는 다음에 나열해야 한다.

 

300x250

 

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

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
728x90
반응형
그리드형(광고전용)
Posted by icodebroker

댓글을 달아 주세요