■ 부모 객체를 포함해 조회하는 방법을 보여준다.
▶ 부모 객체를 포함해 조회하기 예제 (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 |
using System.Collections.Generic; using System.Data; public class MenuModel { public string ID { get; set; } ... } public class MenuStructureModel { public string MENU_TEMPLATE_ID { get; set; } public string TARGET { get; set; } public string ID { get; set; } ... public string MENU_ID { get; set; } ... public MenuModel Menu { get; set; } ... } ... private IDbConnection connetcion; ... string sql = @" SELECT PARENT.* ,CHILD.* FROM COM_MENU_STRUCTURE CHILD LEFT JOIN COM_MENU PARENT ON PARENT.ID = CHILD.MENU_ID WHERE CHILD.MENU_TEMPLATE_ID = @MENU_TEMPLATE_ID AND CHILD.TARGET = @TARGET ORDER BY CHILD.ROWID ASC; "; var result = this.connection.QueryIncludingParent<MenuModel, string, MenuStructureModel> ( sql, parent => parent.ID, (child, parent) => { child.Menu = parent; }, parameter : new { MENU_TEMPLATE_ID = menuTemplateID, TARGET = target }, splitOn : "MENU_TEMPLATE_ID" ); |
※ COM_MENU 테이블은 MenuModel 타입에 대응하고 PK는 ID이다.
※ COM_MENU_STRUCTURE 테이블은 MenuStructureModel 타입에 대응하고 PK는 MENU_TEMPLATE_ID, TARGET, ID이며 FK는 MENU_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 81 82 83 |
using System; using System.Collections.Generic; using System.Data; using System.Linq; using Dapper; #region 부모 객체를 포함해 조회하기 - QueryIncludingParent<TParent, TChild, TParentKey>(connection, sql, parentKeySelector, childSelector, parameter, transaction, buffered, splitOn, commandTimeout, commandType) /// <summary> /// 부모 객체를 포함해 조회하기 /// </summary> /// <typeparam name="TParent">부모 타입</typeparam> /// <typeparam name="TParentKey">부모 키 타입</typeparam> /// <typeparam name="TChild">자식 타입</typeparam> /// <param name="connection">연결</param> /// <param name="sql">SQL</param> /// <param name="parentKeySelector">부모 키 셀렉터</param> /// <param name="childSetter">자식 설정자</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<TChild> QueryIncludingParent<TParent, TParentKey, TChild> ( this IDbConnection connection, string sql, Func<TParent, TParentKey> parentKeySelector, Action<TChild, TParent> childSetter, 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>(); List<TChild> childList = new List<TChild>(); connection.Query<TParent, TChild, TChild> ( sql, (parent, child) => { TParentKey parentKey = parentKeySelector(parent); if(parentKey != null) { if(parentDictionary.ContainsKey(parentKey)) { childSetter(child, parentDictionary[parentKey]); } else { childSetter(child, parent); parentDictionary.Add(parentKey, parent); } } childList.Add(child); return child; }, parameter as object, transaction, buffered, splitOn, commandTimeout, commandType ); return childList; } #endregion |