多重联和GROUP BY LINQ(Multiple Join and Group By LINQ)

我需要生成这个数据模型（例如）：

IList<FeatureGroupFeaturesDto> fcf = new List<FeatureGroupFeaturesDto>();

fcf.Add(new FeatureGroupFeaturesDto
{
    FeatureGroup = new FeatureGroupDto { Id = 1, Name = "Interior" },
    Features = new List<FeatureDto> { 
        new FeatureDto { Id = 7, Name = "Bancos Traseiros Rebatíveis" },
        new FeatureDto { Id = 35, Name = "Computador de Bordo" },
        new FeatureDto { Id = 38, Name = "Suporte para Telemóvel" }
    },
});

fcf.Add(new FeatureGroupFeaturesDto
{
    FeatureGroup = new FeatureGroupDto { Id = 2, Name = "Exterior" },
    Features = new List<FeatureDto> { 
        new FeatureDto { Id = 13, Name = "Barras de Tejadilho" },
        new FeatureDto { Id = 15, Name = "Retrovisores Aquecidos" },
        new FeatureDto { Id = 16, Name = "Retrovisores Elétricos" }
    },
});

基于对实体：

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class CategoryFeatureGroupFeature
{
    [Key]
    [Column("Category_Id", Order = 0)]
    public int Category_Id { get; set; }

    [Key]
    [Column("FeatureGroup_Id", Order = 1)]
    public int FeatureGroup_Id { get; set; }

    [Key]
    [Column("Feature_Id", Order = 2)]
    public int Feature_Id { get; set; }
}

public class Feature
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class FeatureGroup
{
    public int Id { get; set; }
    public string Name { get; set; }
}

基本想法是让由FeatureGroup分组类别的所有功能。

编辑：

我试图把信息在这个模型：

public class FeatureCategoryFeatures
{
    public FeatureGroup FeatureGroup { get; set; }
    public IList<Feature> Features { get; set; }
}

我如何与LINQ和Entity Framework实现这一目标？

谢谢。

Answer 1:

可能这会工作 - 我说好的检查。

虽然很糟糕的代码：

List<CategoryFeatureGroupFeature> cfcgQ = new List<CategoryFeatureGroupFeature>();
        List<Category> cat = new List<Category>();
        List<Feature> fe = new List<Feature>();
        List<FeatureGroup> feag = new List<FeatureGroup>();

        var query = (from p in cfcgQ
                     join q in feag on p.FeatureGroup_Id equals q.Id
                     join r in fe on p.Feature_Id equals r.Id
                     where p.Category_Id == 1
                     select new { q.Name, q.Id, FeatureName = r.Name }).GroupBy(p => new { p.Name, p.FeatureName, p.Id }).ToList().OrderBy(p => p.Key.FeatureName).ThenBy(p => p.Key.Name);

Answer 2:

我修改回答了一下，假设该类别可以有多个组（希望我的假设是正确的）。 我用匿名对象作为返回结果，但本意应该是清楚的。

/*** DATA ***/
IList<Feature> featuresList = new List<Feature> { 
        new Feature { Id = 13, Name = "Barras de Tejadilho" },
        new Feature { Id = 15, Name = "Retrovisores Aquecidos" },
        new Feature { Id = 16, Name = "Retrovisores Elétricos" },
        new Feature { Id = 7, Name = "Bancos Traseiros Rebatíveis" },
        new Feature { Id = 35, Name = "Computador de Bordo" },
        new Feature { Id = 38, Name = "Suporte para Telemóvel" },
        new Feature { Id = 1, Name = "2nd Exterior Feature" }
};
IList<FeatureGroup> featureGroupList = new List<FeatureGroup>{
    new FeatureGroup { Id = 1, Name = "Interior" },
    new FeatureGroup { Id = 2, Name = "Exterior" },
    new FeatureGroup { Id = 3, Name = "2nd Exterior" }
};
IList<Category> categoryList = new List<Category>{
    new Category{ Id=1, Name="All interior" },
    new Category { Id=2, Name="All exterior" }
};
IList<CategoryFeatureGroupFeature> cfcList = new List<CategoryFeatureGroupFeature>
{
    new CategoryFeatureGroupFeature { Category_Id = 1, FeatureGroup_Id = 1, Feature_Id = 7 },
    new CategoryFeatureGroupFeature { Category_Id = 1, FeatureGroup_Id = 1, Feature_Id = 35 },
    new CategoryFeatureGroupFeature { Category_Id = 1, FeatureGroup_Id = 1, Feature_Id = 38 },
    new CategoryFeatureGroupFeature { Category_Id = 2, FeatureGroup_Id = 2, Feature_Id = 13 },
    new CategoryFeatureGroupFeature { Category_Id = 2, FeatureGroup_Id = 2, Feature_Id = 15 },
    new CategoryFeatureGroupFeature { Category_Id = 2, FeatureGroup_Id = 2, Feature_Id = 16 },
    new CategoryFeatureGroupFeature { Category_Id = 2, FeatureGroup_Id = 3, Feature_Id = 1 }
};

/*** QUERY ***/
var result = from c in categoryList
                select new {
                    Id = c.Id,
                    Name = c.Name,
                    FeatureGroups = from fg in featureGroupList
                                    where (from cfc in cfcList
                                        where cfc.Category_Id == c.Id
                                        select cfc.FeatureGroup_Id).Distinct()
                                    .Contains(fg.Id)
                                    select new {
                                        Id = fg.Id,
                                        Name = fg.Name,
                                        Features = (from f in featuresList
                                                join cfc2 in cfcList on f.Id equals cfc2.Feature_Id
                                                where cfc2.FeatureGroup_Id == fg.Id
                                                select f).Distinct()
                                    }
                };

你可以使用组合achive相同的结果join和group by ，但我去了Distinct()

文章来源: Multiple Join and Group By LINQ