Many-To-Many multi mapping in dapper-CodePudding

This is my database

Product
 ---- ---------- 
| Id |   Name   |
 ---- ---------- 
|  1 | Product1 |
|  2 | Product2 |
|  3 | Product3 |
 ---- ---------- 

Category
 ---- ----------- 
| Id |   Name    |
 ---- ----------- 
|  1 | Category1 |
|  2 | Category2 |
|  3 | Category3 |
 ---- ----------- 

ProductCategory
 ---- ------------ ------------ 
| Id | ProductId  | CategoryId |
 ---- ------------ ------------ 
|  1 |          1 |          1 |
|  2 |          1 |          1 |
|  3 |          2 |          2 |
 ---- ------------ ------------

Now, I want to get all products with its categories. In my repository I added this code:

public async Task<IEnumerable<Product>> GetAllProducts()
{
    var products = await _dbConnection.QueryAsync<Product, Category, Product>(
        @"
            SELECT p.Id, p.Name, c.Id, c.Name
            FROM Product p
            LEFT JOIN ProductCategory pc on pc.ProductId = p.Id
            LEFT JOIN Category c on c.Id = pc.CategoryId
        ",
        (product, category) =>
        {
            product.Categories.Add(category);
            return product;
        }, splitOn: "Id",
        transaction: _dbTransaction
    );

    return products.GroupBy(p => p.Id).Select(g =>
    {
        var product = g.First();
        product.Categories = g.Select(p => p.Categories.Single()).ToList();
        return product;
    });
}

In results, I got list of 3 items:

Product1 with 2 categories (Category1, Category2)
Product2 with 1 category (Category2)
Product3 with 1 category (null)

The problem is that I don't want Product3 to have filled category list with null value. What I wanted is to have empty Category list in this case. What should I do to achieve that?

CodePudding user response：

Why not just change the mapping function to?:

(product, category) =>
{
    if (category!=null)
    {
        product.Categories.Add(category);
    }
    return product;
}

That will of course fail for the .Single() in GroupBy, but how would you group by missing category anyway?

CodePudding user response：

I run into the same situation. Checking the source code that is chained to the group, I figured out that it will always return that null object in case that that there is no matching entry on the second table of the many to many relationship. Therefore I added a check on the first object of that List. If that is null, I simply added an empty new list. Please check out the answer here solution It may not be the most pretty solution but it does work reliable and performance is not affected.

CodePudding user response：

It's easier to return the raw data from the query first and then do some processing. Then you have all logic in one place. For example:

var products = await _dbConnection
    .QueryAsync<Product, Category, (Product Product, Category Category)>(
    @"
        SELECT p.Id, p.Name, c.Id, c.Name
        FROM Product p
        LEFT JOIN ProductCategory pc on pc.ProductId = p.Id
        LEFT JOIN Category c on c.Id = pc.CategoryId
    "
    ,(product, category) => (product, category)
    , splitOn: "Id"
    ,transaction: _dbTransaction
);

return products.GroupBy(pc => pc.Product.Id)
    .Select(g =>
    {
        var product = g.First().Product;
        product.Categories = g.Select(pc => pc.Category)
            .Where(c => c != null).ToList();
        return product;
    });

The null check skips the null category that's inevitably in the SQL result set because of the outer joins.