Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Mapping Parent-Child Relationships with Dapper
public class Widget
{
public int Id { get; set; }
public string Name { get; set; }
public string Description { get; set; }
}
public IEnumerable<Widget> GetAllWidgets(SqlConnection connection)
{
List<Widget> widgets = new List<Widget>();
using (SqlCommand command = new SqlCommand("SELECT [Id], [Name], [Description] FROM [Widgets]", connection))
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.read())
{
widgets.Add
(
new Widget
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
Description = reader.GetString(2)
}
);
}
}
return widgets;
}
public IEnumerable<Widget> GetAllWidgets(SqlConnection connection)
{
return connection.Query<Widget>
(
"SELECT [Id], [Name], [Description] FROM [Widgets]"
);
}
public void AddWidget(SqlConnection connection, Widget widget)
{
connection.Execute
(
"INSERT INTO [Widgets] ([Id], [Name], [Description]) VALUES (@Id, @Name, @Description)",
widget
);
}
public class Category
{
public int Id { get; set; }
public string Name { get; set; }
public List<Widget> Widgets { get; set; }
public Category()
{
Widgets = new List<Widget>();
}
}
public class Widget
{
public int Id { get; set; }
public string Name { get; set; }
public Category Category { get; set; }
}
Query<TFirst, TSecond, ..., TReturn>
(
string query,
Func<TFIrst, TSecond, ... TReturn> map,
...
)
public IEnumerable<Category> GetHierarchy(SqlConnection connection)
{
return connection.Query<Category, Widget, Category>
(
@"SELECT
[Categories].[Id],
[Categories].[Name],
[Widgets].[Id],
[Widgets].[Name]
FROM
[Categories]
JOIN
[Widgets]
ON
[Widgets].[CategoryId] = [Categories].[Id]",
(category, widget) =>
{
category.Widgets.Add(widget);
widget.Category = category;
return category;
}
);
}
public IEnumerable<Category> GetHierarchy(SqlConnection connection)
{
// Use a lookup to store unique categories.
Dictionary<int, Category> lookup = new Dictionary<int, Category>();
return connection.Query<Category, Widget, Category>
(
@"SELECT
[Categories].[Id],
[Categories].[Name],
[Widgets].[Id],
[Widgets].[Name]
FROM
[Categories]
JOIN
[Widgets]
ON
[Widgets].[CategoryId] = [Categories].[Id]",
(possibleDupeCategory, widget) =>
{
Category category;
// Look for the current category, storing it in `category` if it
// exists.
if (!lookup.TryGetValue(possibleDupeCategory.Id, out category))
{
// If the lookup doesn't contain the current category, add
// it and store it in `category` as well.
lookup.Add(possibleDupeCategory.Id, possibleDupeCategory);
category = possibleDupeCategory;
}
// Regardless of the state of the lookup before this mapping,
// `category` now refers to a distinct category.
category.Widgets.Add(widget);
widget.Category = category;
return category;
}
).Distinct();
}
@sbolum

This comment has been minimized.

Copy link

@sbolum sbolum commented Feb 28, 2016

Nice examples. Could you please explain what's different between 7 and 8?

@wezzix

This comment has been minimized.

Copy link

@wezzix wezzix commented Jul 6, 2017

If you ordered the results by Category.Id then you wouldn't need a dictionary, simply test if the new category id is same as last, then reuse.

@Fagro

This comment has been minimized.

Copy link

@Fagro Fagro commented Aug 25, 2017

Great sample!!!

@kaanacar34

This comment has been minimized.

Copy link

@kaanacar34 kaanacar34 commented Aug 20, 2020

public class Product
{
public int ProductId { get; set; }
public string ProductName { get; set; }
public int CategoryId { get; set; }
public Category Category { get; set; }
public int ProductOriginId { get; set; }
public ProductOrigin ProductOrigin { get; set; }
}

public class Category
{
public int CategoryId { get; set; }
public string CategoryName { get; set; }
public ICollection Products { get; set; }
}

public class ProductOrigin
{
public int ProductOriginId { get; set; }
public string ProductOriginName { get; set; }
}

public static async Task<List> GetCategoryWithProducts()
{
using (var connection = new SqlConnection(connString))
{
var sql = @" SELECT c.CategoryId,c.CategoryName,p.ProductId,p.ProductName,po.ProductOriginName
FROM Categories c
LEFT JOIN Products p ON p.CategoryId=c.CategoryId
LEFT JOIN ProductOrigins po on po.ProductOriginId=p.ProductOriginId";

            var categoryDictionary = new Dictionary<int, Category>();




            var list = connection.QueryAsync<Category, Product, Category>(
                    sql,
                    (category, product) =>
                    {
                        Category categoryentry;


                        if (!categoryDictionary.TryGetValue(category.CategoryId, out categoryentry))
                        {
                            categoryentry = category;
                            categoryentry.Products = new List<Product>();
                            categoryDictionary.Add(categoryentry.CategoryId, categoryentry);
                        }


                        categoryentry.Products.Add(product);
                        return categoryentry;
                    },
                    splitOn: "CategoryId,ProductId").Result
                .Distinct()
                .ToList();


           // Console.WriteLine(list.Count);


            return list.ToList();


        }


    }

with the code category> product, I can buy the category and the products under the category together.
I want to get a list like category> product> productorigin.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment