要排除重写值的LINQ查询？
要排除重写值的LINQ查询？
提问于 2014-04-30 18:01:11
我有以下几张表：
ImageSettingOverrides
TechniqueSettings
SettingKeyValues
来自TechniqueSettings表：
BAZ-FOO设置(SettingKeyId: 7)是对BAZ-Default (SettingKeyId: 4)设置的重写。
按覆盖值分组的查询的预期返回的示例：
我希望编译一个SettingKeyValues给定技术BAZ的列表，并覆盖FOO，该列表排除了重写的BAZ-Default设置，并包含非重写的BAZ-Default设置。
我现在有一个LINQ查询，它根据默认值/重写值对设置键值进行分组：
var techniqueSettings = _dataRepository.All<TechniqueSetting>()
.Where(s => s.Technique.Equals(TechniqueName, StringComparison.InvariantCultureIgnoreCase))
// group settings by: e.g. Default | FOO
.GroupBy(s => s.Override);从这里，我确定用户是在查询默认值，还是只查询带有重写的默认值：
var techniqueGroups = techniqueSettings.ToArray();
if (OverridesName.Equals("Default", StringComparison.InvariantCultureIgnoreCase)) {
// get the default group and return as queryable
techniqueSettings = techniqueGroups
.Where(grp => grp.Key.Equals("Default", StringComparison.InvariantCultureIgnoreCase))
.AsQueryable();
} else {
// get the overrides group - IGrouping<string, TechniqueSetting>
var overridesGroup = techniqueGroups
.Where(grp => !grp.Key.Equals("Default", StringComparison.InvariantCultureIgnoreCase))
.First();
var defaultGroup = techniqueGroups
.Where(grp => grp.Key.Equals("Default", StringComparison.InvariantCultureIgnoreCase))
// we know what is in the overrides, so exlude them from being selected here
// how to exlude overridden defaults???
.First();
}此外，我情不自禁地认为必须有一个使用JOIN (可能？)的更简单、不那么笨拙的LINQ查询。
注：使用EntityFramework 6.x
__
更新:我发现似乎简化了一些Aggregate，但仍然需要一个匿名方法。
var defaultGroup = techniqueGroups
.Where(grp => grp.Key.Equals("Default", StringComparison.InvariantCultureIgnoreCase))
.Aggregate(overridesGroup,
(overrides, defaults) => {
var settings = new List<TechniqueSetting>();
foreach (var setting in defaults) {
if (overrides.Any(o => o.SettingKey.Key == setting.SettingKey.Key)) {
continue;
}
settings.Add(setting);
}
return settings.GroupBy(s => s.Override).First();
},
setting => setting);根据@MarkoDevcic的评论，我还没有试过Join。
在这个查询中可以使用Except吗？
Stack Overflow用户
发布于 2014-05-08 02:59:39
我要做一些假设。
- 如果存在ImageSettingOverrides，则重写还必须匹配在AKA中传递的覆盖(这是join子句中的iSettingsOverrides => iSettingsOverrides.Override == OverridesName )。
- 您只需要一个独立的SettingKeyValues列表
- TechniqueSetting.Id是键，ImageSettingOverride.TechniqueSettingsId是外键，这就是它们之间的关系。
- SettingKeyValue.Id是键，TechniqueSetting.SettingKeyId是外键，这就是它们之间的关系。
- 您没有导航属性，我必须进行连接。
如果我了解您的类以及它们之间的关系，这将给出一个SettingKeyValues列表。因为一切都保持IQueryable，所以它应该在服务器上执行。
//I'm assuming these are your variables for each IQueryable
IQueryable<TechniqueSetting> techniqueSettings;
IQueryable<ImageSettingOverride> imageSettingOverrides;
IQueryable<SettingKeyValue> settingKeyValues;
var OverridesName = "FOO";
var TechniqueName = "BAZ";
IQueryable<TechniqueSetting> tSettings;
if (OverridesName.Equals("Default", StringComparison.InvariantCultureIgnoreCase))
{
// Get a list of TechniqueSettings that have this name and are default
tSettings = techniqueSettings.Where(t => t.Override == OverridesName && t.Technique == TechniqueName);
}
else
{
// Get a list of TechniqueSettings Id that are overridden
// The ImageSettingOverrides have the same override
var overriddenIDs = techniqueSettings.Where(t => t.Technique == TechniqueName && t.Override == "Default")
.Join(
imageSettingOverrides.Where(
iSettingsOverrides =>
iSettingsOverrides.Override == OverridesName),
tSetting => tSetting.SettingKeyId,
iSettings => iSettings.TechniqueSettingsId,
(tSetting, iSettingsOverrides) => tSetting.Id);
// Get a list of techniqueSettings that match the override and TechniqueName but are not part of the overriden IDs
tSettings =
techniqueSettings.Where(
t =>
t.Technique == TechniqueName && !overriddenIDs.Contains(t.Id) &&
(t.Override == OverridesName || t.Override == "Default"));
}
// From expected results seems you just want techniqueSettings and that's what would be in techniqueSettings right now.
// If you want a list of SettingKeyValues (which is what is stated in the questions we just need to join them in now)
var settings = tSettings.Join(settingKeyValues, tSetting => tSetting.SettingKeyId,
sKeyValues => sKeyValues.Id, (tSetting, sKeyValues) => sKeyValues)
.Distinct();页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/23395011
复制相关文章
相似问题
腾讯云开发者
