■ 자식 리스트를 포함해 조회하는 방법을 보여준다.
▶ 자식 리스트를 포함해 조회하기 예제 (C#)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
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#)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 |
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 |