c# - 返工 linq 查询以返回嵌套对象
问题描述
我的查询 linq 看起来像
var selectedNotifications = _dbContext.UserNotificationTypeDeliveryChoice
.Include(m => m.NotificationGroup)
.Include(m => m.DeliveryType)
.Where(m => m.UserDefId == userDefId && m.UserTypeId == (int)userType)
.Select(m => new NotificationGroup()
{
NotificationGroupId = m.NotificationGroup.NotificationGroupId,
Name = m.NotificationGroup.Name,
DefaultDeliveryType = m.DeliveryType,
HasChoosen = true
}).ToList();
在我的模型中,我使用虚拟道具来填写外键属性 DeliveryType。它看起来像这样(JSON):
[
{
"notificationGroupId": 1,
"name": "Comments",
"defaultDeliveryType": {
"deliveryTypeId": 2,
"name": "Email"
},
"hasChoosen": true
},
{
"notificationGroupId": 2,
"name": "Q&A",
"defaultDeliveryType": {
"deliveryTypeId": 2,
"name": "Email"
},
"hasChoosen": true
},
{
"notificationGroupId": 3,
"name": "Services",
"defaultDeliveryType": {
"deliveryTypeId": 2,
"name": "Email"
},
"hasChoosen": true
},
{
"notificationGroupId": 4,
"name": "Trial",
"defaultDeliveryType": {
"deliveryTypeId": 2,
"name": "Email"
},
"hasChoosen": true
},
{
"notificationGroupId": 4,
"name": "Trial",
"defaultDeliveryType": {
"deliveryTypeId": 1,
"name": "SMS"
},
"hasChoosen": true
}
]
但是,我有同一个 NotificationGroupId 的多条记录,我希望列出如下:
[
{
"notificationGroupId": 4,
"name": "Trial",
"defaultDeliveryType": {
"deliveryTypeId": 1,
"name": "SMS"
},
"defaultDeliveryType": {
"deliveryTypeId": 2,
"name": "Email"
},
"hasChoosen": true
}
]
请注意“notificationGroupId”的区别: 4,它需要有嵌套的传递类型。
更新 #1 我设法实现了一些目标,但我仍然需要使用 Select 投影仪语句映射到我的模型。
这是一个例子:
var selectedNotifications = _dbContext.UserNotificationTypeDeliveryChoice
.Include(m => m.NotificationGroup)
.Include(m => m.DeliveryType)
.Where(m => m.UserDefId == userDefId && m.UserTypeId == (int)userType)
.GroupBy(p => p.NotificationGroupId,
p => p.DeliveryType,
(key, g) => new { NotificationGroupId = key, DeliveryTypes = g });
更新 #2强文本
public class UserNotificationTypeDeliveryChoice
{
public List<NotificationGroup> NotificationGroups { get; set; }
//public List<DeliveryType> DeliveryTypes { get; set; }
public long UserNotificationTypeDeliveryChoiceId { get; set; }
public int? UserDefId { get; set; }
public int? UserCompanyOrInstitutionId { get; set; }
public byte NotificationGroupId { get; set; }
public byte DeliveryTypeId { get; set; }
public int UserTypeId { get; set; }
public virtual DeliveryType DeliveryType { get; set; }
public virtual NotificationGroup NotificationGroup { get; set; }
public virtual UserDef UserDef { get; set; }
}
NotificationGroup 和 DeliveryType 的模型:
public class NotificationGroup
{
public NotificationGroup()
{
UserNotificationTypeDeliveryChoice = new HashSet<UserNotificationTypeDeliveryChoice>();
NotificationGroupUserType = new HashSet<NotificationGroupUserType>();
}
//public List<DeliveryType> DeliveryTypes { get; set; }
public byte NotificationGroupId { get; set; }
public string Name { get; set; }
public string Description { get; set; }
//public bool HasChoosen { get; set; }
public virtual DeliveryType DefaultDeliveryType { get; set; }
public byte DefaultDeliveryTypeId { get; set; }
public virtual ICollection<UserNotificationTypeDeliveryChoice> UserNotificationTypeDeliveryChoice { get; set; }
public virtual ICollection<NotificationGroupUserType> NotificationGroupUserType { get; set; }
}
public class DeliveryType
{
public DeliveryType()
{
NotificationGroup = new HashSet<NotificationGroup>();
UserNotificationTypeDeliveryChoice = new HashSet<UserNotificationTypeDeliveryChoice>();
NotificationGroupUserType = new HashSet<NotificationGroupUserType>();
}
public byte DeliveryTypeId { get; set; }
public string Name { get; set; }
public string Description { get; set; }
public virtual ICollection<NotificationGroup> NotificationGroup { get; set; }
public virtual ICollection<UserNotificationTypeDeliveryChoice> UserNotificationTypeDeliveryChoice { get; set; }
public virtual ICollection<NotificationGroupUserType> NotificationGroupUserType { get; set; }
}
}
这是我通过 EF Core 映射的模型描述:
解决方案
如果你有一个非唯一的 ID,你不能按 NotificationGroup 分组,你必须坚持使用 ID。
要保留 NotifigrationGroup 的名称,您必须将 NotificationGroup 保留为 GroupBy 的 TElement。(这就是为什么 p=>p。)
稍后在结果选择器中,您可以从其中一个元素中选择一个名称,只需选择第一个 NotificationGroup 的名称。如果您愿意,您可以在此处选择 DeliveryTypes。
var selectedNotifications = _dbContext.UserNotificationTypeDeliveryChoice
.Include(m => m.NotificationGroup)
.Include(m => m.DeliveryType)
.Where(m => m.UserDefId == userDefId && m.UserTypeId == (int)userType)
.GroupBy( p => p.NotificationGroupId, p => p,
(key,g) => new {
NotificationId = key,
Name = g.First().Name,
DeliveryTypes = g.Select(x => x.DeliveryTypes)
}););
推荐阅读
- r - 如何在不重复排名的情况下对 R 中的向量进行排序
- tkinter - 在 python tkinter 中更新天气
- c++ - 大小为零的 QByteArray;begin() 和 end() 指向哪里?
- wordpress - 在商店网格中显示类别描述
- query-string - 如何在 OpenAPI 中转义 URL 中的百分号?
- javascript - Reactjs:来自组件外部的setState
- logging - 将日志记录级别设置为 DEBUG 时,Airflow DAG 不运行
- system-calls - 是否应该在发出环境调用之前保存临时寄存器?
- sql - 从 node.js 中的回调 SQL 函数返回值
- python - 在 tensorflow 1.5 中删除模型的前 2 层