I am trying to convert from EntityFramework Core to Dapper for most queries. I have a sql table (Tree Structure) defined as:
[Table("bma_ec_categories")]
public class Category
{
[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
[Column("category_id")]
public int CategoryId { get; set; }
[Column("parent_category_id")]
public int ParentId { get; set; }
[Required]
[Column("category_name")]
[StringLength(50)]
public string Name { get; set; }
public ICollection<Category> Children { get; set; }
[Column("title")]
[StringLength(150)]
public string Title { get; set; }
[Column("description")]
[StringLength(250)]
public string Description { get; set; }
[Column("keywords")]
[StringLength(250)]
public string Keywords { get; set; }
[Column("page_content", TypeName = "text")]
public string PageContent { get; set; }
[Column("banner_group_id")]
public int? BannerGroupId { get; set; }
[Column("inactive")]
public bool? Inactive { get; set; }
[Column("issitecategory")]
public bool Issitecategory { get; set; }
[Column("sort_order")]
public int? SortOrder { get; set; }
}
It is a tree. I also have a dto defined as:
public class CategoryDto
{
[Key]
public int CategoryId { get; set; }
public int ParentId { get; set; }
public string Name { get; set; }
public string Title { get; set; }
public string Description { get; set; }
public string Keywords { get; set; }
public string PageContent { get; set; }
public int? BannerGroupId { get; set; }
public bool? Inactive { get; set; }
public bool Issitecategory { get; set; }
public int? SortOrder { get; set; }
public ICollection<CategoryDto> Children { get; set; }
}
My Item Service has this Dapper query that I copied for the most part from EF Core:
public IEnumerable<CategoryDto> GetCategoriesWithDapperUnbuffered()
{
var plainResult = _dapper.GetCategoriesUnbuffered();
var categoryDtos = plainResult.ToList();
var lookup = categoryDtos.Where(x => x.ParentId != 0).ToLookup(x => x.ParentId);
foreach (var c in categoryDtos)
{
if (lookup.Contains(c.CategoryId))
{
c.Children = lookup[c.CategoryId]
.OrderBy(x => x.SortOrder)
.ThenBy(x => x.Name)
.ToList();
}
}
var dto = categoryDtos
.Where(x => x.ParentId == 0)
.OrderBy(x => x.SortOrder)
.ThenBy(x => x.Name)
.ToList();
return dto;
}
... where GetCategoriesUnbuffered is:
public IEnumerable<CategoryDto> GetCategoriesUnbuffered()
{
string sql = "SELECT * FROM [dbo].[bma_ec_categories]";
using (DbConnection db = _connectionFactory.CreateConnection())
{
var categories = db.Query<CategoryDto>(sql, buffered: false).ToList();
return categories;
}
}
I also have a Dapper Fluent mapping I can show. This query does work. But I am trying to optimize further by using Dapper's Multi-Mapping feature in the original query. Here is what I have so far:
public IEnumerable<CategoryDto> GetCategoriesMultiMapping()
{
string sql = "SELECT * FROM [dbo].[bma_ec_categories] AS [A] INNER JOIN [dbo].[bma_ec_categories] AS [B] ON [A].[category_id] = [B].[parent_category_id];";
using (DbConnection db = _connectionFactory.CreateConnection())
{
var categoryDictionary = new Dictionary<int, CategoryDto>();
var list = db.Query<CategoryDto, CategoryDto, CategoryDto>(
sql,
(category, children) =>
{
if (!categoryDictionary.TryGetValue(category.ParentId, out var categoryEntry))
{
categoryEntry = category;
categoryEntry.Children = new List<CategoryDto>();
categoryDictionary.Add(categoryEntry.ParentId, categoryEntry);
}
categoryEntry.Children.Add(children);
return categoryEntry;
},
splitOn: "category_id,category_id", buffered: false)
.Distinct()
.ToList();
return list;
}
}
This returns the first category with all the other categories as its child. I am running into trouble trying to splitOn the second instance of category_id. I tried to adapt the One to Many example from Dapper's Example - Query Multi-Mapping (One to Many). Any help would be greatly appreciated.
