Использование Dapper’a для чтения связанных записей

Разберем чтение записей из таблиц, связанных отношением Parent-Child, с помощью Dapper.
Допустим, есть две таблицы:

tst_parent с колонками id, name
tst_child с колонками id, parentID, name, где parentID – внешний ключ для таблицы Parent

Создадим хранимую процедуру для чтения записей из этих таблиц. Запрос будет выглядеть так:

SELECT * from tst_parent p
inner join tst_child c on c.parendID = p.id

Очевидно, что в этом запросе будут повторяться записи из таблицы tst_parent для каждой связанной с ней записью из таблицы tst_child. Мы распределим эти записи в специально методе Dapper’a.

Вызовем хранимую процедуру с помощью Dapper:

using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings[“RudenTestEntitiesSimple”].ConnectionString))
{
conn.Open();
var parentLookup = new Dictionary<int, tst_parent>();
conn.Query<tst_parent, tst_child, tst_parent>(“GetObjects”, (p, c) =>
{
tst_parent parent;
if (!parentLookup.TryGetValue(p.id, out parent))
{
parentLookup.Add(p.id, parent = p);
}
if (parent.tst_child == null) parent.tst_child = new List();
parent.tst_child.Add(c);

return parent;
});
return parentLookup.Values.ToArray();
}

Разберем приведенный выше код построчно.

conn.Query<tst_parent, tst_child, tst_parent>: указываем в шаблоне запроса, что мы хотим получить записи из таблиц tst_parent и tst_child, и в результате будет запись из tst_parent, хранящая ссылки на свои дочерние записи.

“GetObjects”: указываем имя хранимой процедуры.
Далее создаем функцию преобразования. В качестве параметров она принимает значения из таблиц tst_parent и tst_child, при этом, как уже упоминалось, значения таблицы tst_parent будут повторяться для каждой записи из tst_child. В этой фукции мы должны исключить эти повторения.

До вызова функции мы создали словарь

var parentLookup = new Dictionary<int, tst_parent>();

В нем мы будем хранить найденные значения tst_Parent, и проверять, не был ли уже добавлен tst_Parent.

tst_parent parent;
if (!parentLookup.TryGetValue(p.id, out parent))
{
parentLookup.Add(p.id, parent = p);
}

Проверяем, не был ли уже добавлен parent, если нет, то добавляем.

if (parent.tst_child == null) parent.tst_child = new List();
parent.tst_child.Add(c);

Записи из child у нас не повторяются, поэтому просто добавим запись.

Теперь представим, что у нас добавилась еще одна таблица tst_subChild c колонками id, childID, name, где childID ссылается на таблицы tst_child. Изменим запрос хранимой процедуры, чтобы включить новую таблицу в результат.

SELECT * from tst_parent p
inner join tst_child c on c.parendID = p.id
inner join tst_subChild s on s.childID = c.id;

В данном случае значения из tst_child также будут повторяться для каждой дочерней записи из tst_subChild. В данном случае однозначно для того, чтобы однозначно идентифицировать, в какую таблицу добавлять запись, нам необходимо два ключа из таблиц tst_Parent и tst_Child. Поэтому создадим вспомогательный класс:

public class ChildKey
{
public int parentId { get; set; }
public int childId { get; set; }

public override bool Equals(object obj)
{
var a = obj as ChildKey;

return parentId == a.parentId && childId == a.childId;
}

public override int GetHashCode()
{
return this.childId + this.parentId;
}
}

Данный класс будет использоваться в качестве ключа в Dictionary, поэтому необходимо переопределить в нем методы Equals и GetHashCode.

Перепишем функцию запроса:
var childLookup = new Dictionary<ChildKey, tst_child>();
conn.Query<tst_parent, tst_child, tst_subChild, tst_parent>(“GetObjects”, (p, c, s) =>
{
tst_parent parent;
if (!parentLookup.TryGetValue(p.id, out parent))
{
parentLookup.Add(p.id, p);
}
tst_child child;
var childKey = new ChildKey { parentId = c.parendID, childId = c.id };
if (!childLookup.TryGetValue(childKey, out child))
{
childLookup.Add(childKey, child = c);
parent = parentLookup[c.parendID];
if (parent.tst_child == null) parent.tst_child = new List();
parent.tst_child.Add(c);
}
if (child.tst_subChild == null) child.tst_subChild = new List();
child.tst_subChild.Add(s);
return parent;
});

Разберем код построчно:

conn.Query<tst_parent, tst_child, tst_subChild, tst_parent> теперь в результате запросы мы получаем записи из трех таблиц.
var childLookup = new Dictionary<ChildKey, tst_child>(); создадим дополнительный словарь для таблицы tst_Child.

Функция отображения теперь в качестве параметров получает три значения.

По прежнему исключаем повторы из таблицы parent.
if (!parentLookup.TryGetValue(p.id, out parent))
{
parentLookup.Add(p.id, p);
}

Для каждой записи из таблицы tst_Child создаем ключ.

var childKey = new ChildKey { parentId = c.parendID, childId = c.id };

Eсли такая запись еще не была обработана, добавим ее в словарь и найдем для нее parent.

if (!childLookup.TryGetValue(childKey, out child))
childLookup.Add(childKey, child = c);
parent = parentLookup[c.parendID];

Добавим запись к соответствующему parent.

if (parent.tst_child == null) parent.tst_child = new List();
parent.tst_child.Add(c);

Записи из tst_subChild не повторяются, поэтому просто добавим ее к соответствующему child.

if (child.tst_subChild == null) child.tst_subChild = new List();
child.tst_subChild.Add(s);

На самом деле в алгоритме можно обойтись и без дополнительных словарей, но в этом случае придется проверять существование записи поиском в массиве, а это сильно замедлит алгоритм при большом количестве записей.

При более глубокой вложенности таблиц необходимо будет создавать более сложные ключи, содержащие ключи по количеству вложенных таблиц, и аналогичным способом менять функцию отображения.