多重联和GROUP BY LINQ [英] Multiple Join and Group By LINQ
问题描述
我需要生成这个数据模型(例如):
I need to generate this data model (example):
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分组类别的所有功能。
Basically the idea is to get all the Features of a Category grouped by FeatureGroup.
编辑:
我试图将信息在这个模型中:
I'm trying to put the information in this model:
public class FeatureCategoryFeatures
{
public FeatureGroup FeatureGroup { get; set; }
public IList<Feature> Features { get; set; }
}
我如何与LINQ和Entity Framework实现这一目标?
How can I achieve this with LINQ and Entity Framework ?
感谢。
推荐答案
我修改回答了一下,假设该类别可以有多个组(希望我的假设是正确的)。我用匿名对象作为返回结果,但本意应该是清楚的。
I modified answer a bit, assuming that category can have multiple groups (hope my assumption is correct). And I used anonymous objects as return result, but intention should be clear.
/*** 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()
}
};
您可以使用加入
和按
,但我去了<$ C $的组合achive相同的结果C>鲜明的()。
You could achive same result using combination of join
and group by
, but I went for Distinct()
.
这篇关于多重联和GROUP BY LINQ的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!