c# - 实体框架组映射表

Question

我有三个名为 Products、Product_Category、Categories 的表。Product_Category 有 Id、ProductId 和 CategoryId。我想得到类似的东西

select p.*, group_concat(distinct c.name SEPARATOR ',') categories 
from products p 
inner join product_category pc 
  on p.id = pc.product_id 
inner join categories c 
  on pc.category_id = c.id 
group by p.id

使用 linq。

我试过了

var result = (from pc in ProductCategory1 
              join c in Categories1 
                   on pc.CategoryId equals c.Id into table1 
              from c in table1.ToList() 
              join p in Products1 
                   on pc.ProductId equals p.Id into table2 
              from p in table2.ToList()
              group c by new {p, c} into g 
             select new ViewModel
                          { 
                            products = g.Key.p, 
                            categories = string.Join(", ", g.Key.c.Name)
                            }).ToList();

但它复制了产品

{
    "products": {
        "id": 1,
        "name": "Active classic boxers ",
        "description": "\"There`s a reason why our boxers are a cult favorite # they keep their cool, especially in sticky situations. The quick-drying, lightweight underwear takes up minimal space in a travel pack. An exposed, brushed waistband offers next-to-skin softness, five-panel construction with a traditional boxer back for a classic fit, and a functional fly. Made of 3.7-oz 100% recycled polyester with moisture-wicking performance. Inseam (size M) is 4 1/2\"\". Recyclable through the Common Threads Recycling Program.<br><br><b>Details:</b><ul> <li>\"\"Silky Capilene 1 fabric is ultralight, breathable and quick-to-dry\"\"</li> <li>\"\"Exposed, brushed elastic waistband for comfort\"\"</li> <li>5-panel construction with traditional boxer back</li> <li>\"\"Inseam (size M) is 4 1/2\"\"\"\"\"\"</li></ul><br><br><b>Fabric: </b>3.7-oz 100% all-recycled polyester with Gladiodor natural odor control for the garment. Recyclable through the Common Threads Recycling Program<br><br><b>Weight: </b>99 g (3.5 oz)<br><br>Made in Mexico.\"",
        "createdAt": "2016-10-28T14:03:53+03:00",
        "updatedAt": "2016-11-06T15:03:53",
        "price": 38.94,
        "quantity": 6
    },
    "categories": {
        "id": 1,
        "name": "Housekeeping"
    }
},
{
    "products": {
        "id": 1,
        "name": "Active classic boxers ",
        "description": "\"There`s a reason why our boxers are a cult favorite # they keep their cool, especially in sticky situations. The quick-drying, lightweight underwear takes up minimal space in a travel pack. An exposed, brushed waistband offers next-to-skin softness, five-panel construction with a traditional boxer back for a classic fit, and a functional fly. Made of 3.7-oz 100% recycled polyester with moisture-wicking performance. Inseam (size M) is 4 1/2\"\". Recyclable through the Common Threads Recycling Program.<br><br><b>Details:</b><ul> <li>\"\"Silky Capilene 1 fabric is ultralight, breathable and quick-to-dry\"\"</li> <li>\"\"Exposed, brushed elastic waistband for comfort\"\"</li> <li>5-panel construction with traditional boxer back</li> <li>\"\"Inseam (size M) is 4 1/2\"\"\"\"\"\"</li></ul><br><br><b>Fabric: </b>3.7-oz 100% all-recycled polyester with Gladiodor natural odor control for the garment. Recyclable through the Common Threads Recycling Program<br><br><b>Weight: </b>99 g (3.5 oz)<br><br>Made in Mexico.\"",
        "createdAt": "2016-10-28T14:03:53+03:00",
        "updatedAt": "2016-11-06T15:03:53",
        "price": 38.94,
        "quantity": 6
    },
    "categories": {
        "id": 5,
        "name": "Public Areas"
    }
}

编辑 产品类别ProductCategory

标签： c#.netentity-framework

Answer 1

在 LINQ-to-Entities 中使用join几乎总是错误的方法。

怎么样：

var query = from p in db.Products
             select new ViewModel()
                 { 
                    product = p, 
                    categories = p.ProductCategories.SelectMany(pc => pc.Category).ToList()
                 };

?