使用Lambda删除重复项
本文关键字:删除 Lambda 使用 | 更新日期: 2023-09-27 18:27:16
我需要一些帮助,使用lambda表达式来删除实体框架上下文中的重复项。我有一张表,上面有以下几列:
Id, DateOfIncident, Description, EmployeeId, IncidentTypeId, and IsAttendanceIncident
我想删除DateOfIncident, EmployeeID, IncidentTypeID and IsAttendanceIncident are the same.
中的重复条目。我确实想保留一个条目。我知道如何使用SQL中带有CTE的存储过程来完成这项任务,但我不知道如何使用Lambda表达式来完成这一任务。
这段代码返回一个列表,其中不包括我的重复项,但现在我该如何删除不在此列表中的重复项?
var query = db.Incidents.Where(x => x.IsAttendanceIncident == "Y").GroupBy(x => new { x.EmployeeId, x.DateOfIncident, x.IsAttendanceIncident })
.Select(x => x.FirstOrDefault());
更新:
所以我继续写自定义IEqualityComparer。现在,我如何使用id删除上下文中不在distinctItems中的事件?
static void Main(string[] args)
{
DALIncidents.AttendanceEntities1 db = new DALIncidents.AttendanceEntities1();
IEnumerable<DALIncidents.Incident> distinctItems = db.Incidents.Where(c => c.IsAttendanceIncident == "Y");
distinctItems = distinctItems.Distinct(new DALIncidents.DistinctIncidentComparer());
Console.ReadLine();
}
var query = db.Incidents
.Where(x => x.IsAttendanceIncident == "Y")
.GroupBy(x => new { x.EmployeeId, x.DateOfIncident, x.IsAttendanceIncident })
示例1:
.Select(x => x.FirstOrDefault()); // your original code which retrieves entities to not delete
var dupes = db.Incidents.Except( query ); // get entities to delete
示例2:
.SelectMany( x => x.OrderBy( y => y.Id ).Skip(1) ); // gets dupes directly
var dupes = query; // already have what we need
最后:
foreach( var dupe in dupes )
{
db.Incidents.Remove( dupe );
}
从我之前使用的测试上下文生成的示例SQL,其中Person实体与手表有1:N关系:
C#:
context.Persons.SelectMany(x => x.Watches.OrderBy(y => y.Id).Skip(1))
生成的SQL:
SELECT
1 AS [C1],
[Skip1].[Id] AS [Id],
[Skip1].[Brand] AS [Brand],
[Skip1].[Person_Id] AS [Person_Id]
FROM [dbo].[Persons] AS [Extent1]
CROSS APPLY (SELECT [Project1].[Id] AS [Id], [Project1].[Brand] AS [Brand], [Project1].[Person_Id] AS [Person_Id]
FROM ( SELECT [Project1].[Id] AS [Id], [Project1].[Brand] AS [Brand], [Project1].[Person_Id] AS [Person_Id], row_number() OVER (ORDER BY [Project1].[Id] ASC) AS [row_number]
FROM ( SELECT
[Extent2].[Id] AS [Id],
[Extent2].[Brand] AS [Brand],
[Extent2].[Person_Id] AS [Person_Id]
FROM [dbo].[Watches] AS [Extent2]
WHERE [Extent1].[Id] = [Extent2].[Person_Id]
) AS [Project1]
) AS [Project1]
WHERE [Project1].[row_number] > 1 ) AS [Skip1]
var query = db.Incidents.Where(x => x.IsAttendanceIncident == "Y")
.GroupBy(x => new { x.Id, x.EmployeeId, x.DateOfIncident, x.IsAttendanceIncident })
.Select(x => x.FirstOrDefault());
var query2 = from duplicate in db.Incidents
.Where(x => x.IsAttendanceIncident == "Y" && !query.Any(i => i.Id == duplicate.Id));
query2现在只包含重复项?
您将需要使用Distinct函数,如果您只想要一些字段,则需要创建一个Equality Comparer。(IEqualityComparer)
Ahh刚刚看到上面的评论,查看更多:
使用linq 删除列表中的重复项
var query = db.Incidents.Where(x => x.IsAttendanceIncident == "Y").GroupBy(x => new { x.EmployeeId, x.DateOfIncident, x.IsAttendanceIncident })
.SelectMany(x => x.Skip(1));