使用lambda删除重复 [英] Delete duplicates using Lambda

问题描述

我需要使用lambda表达式来删除我的实体框架范围内重复的条目一些帮助。我有一个表有以下栏目：

标识，DateOfIncident，说明，雇员，IncidentTypeId和IsAttendanceIncident

我要删除其中 DateOfIncident，雇员，IncidentTypeID和IsAttendanceIncident是相同的重复的条目。我要保留一个条目。我知道如何做到这一点使用与CTE在SQL存储过程，但我想不出如何完成使用Lambda表达式这个任务。

这代码返回一个列表不包括我的重复，但现在我怎么去删除那些不在该列表复制？

  VAR的查询=分贝。 Incidents.Where（X => x.IsAttendanceIncident ==Y）GROUPBY（X =>新建{x.EmployeeId，x.DateOfIncident，x.IsAttendanceIncident}）
。选择（X => x.FirstOrDefault（））; 
  

更​​新：

于是我继续并写下了自定义的IEqualityComparer。现在我该怎样删除我的上下文事件使用id是不是在我distinctItems？

 静态无效的主要（字符串[ ]参数）
 {
 DALIncidents.AttendanceEntities1 DB =新DALIncidents.AttendanceEntities1（）; 
 
 IEnumerable的< D​​ALIncidents.Incident> distinctItems = db.Incidents.Where（C => c.IsAttendanceIncident ==Y）; 
 distinctItems = distinctItems.Distinct（新DALIncidents.DistinctIncidentComparer（））; 
 
到Console.ReadLine（）; 
} 
  

解决方案

  VAR的查询= db.Incidents 
。凡（X => x.IsAttendanceIncident ==Y）
 .GroupBy（X =>新建{x.EmployeeId，x.DateOfIncident， x.IsAttendanceIncident}）
  

例1：

 。选择（X => x.FirstOrDefault（））; //你以检索实体不会删除
 
变种原代码愚弄= db.Incidents.Except（查询）; //获取实体删除
  

例2：

  .SelectMany（X => x.OrderBy（Y => y.Id）.Skip（1））; //获取受骗者直接
 
 VAR愚弄=查询; //已经拥有我们所需要的
  

最后：

 的foreach（在愚弄VAR重复）
 {
 db.Incidents.Remove（重复）; 
} 
  

这是我之前使用的测试环境，其中Person实体有1生成实例SQL ：手表带N的关系：

C＃：

  context.Persons .SelectMany（X => x.Watches.OrderBy（Y => y.Id）.Skip（1））
  

生成的SQL：

  SELECT 
 1 AS [C1]，
 [SKIP1 ]。[ID] AS [ID]，
 [SKIP1] [品牌] AS [品牌]，
 [SKIP1]。[PERSON_ID] AS [PERSON_ID] 
从[DBO]。 [人口] AS [Extent1] 
 CROSS APPLY（SELECT [PROJECT1]。[ID] AS [ID]，[PROJECT1] [品牌] AS [品牌]，[PROJECT1]。[PERSON_ID] AS [PERSON_ID] 
 FROM（SELECT [PROJECT1]。[ID] AS [ID]，[PROJECT1] [品牌] AS [品牌]，[PROJECT1]。[PERSON_ID] AS [PERSON_ID]，ROW_NUMBER（）OVER（ORDER BY [PROJECT1]。[ID] ASC）AS [ROW_NUMBER] 
 FROM（SELECT 
 [Extent2]。[ID] AS [ID]，
 [Extent2] [品牌] AS [品牌]，
 [Extent2]。[PERSON_ID] AS [PERSON_ID] 
从[DBO]。[手表] AS [Extent2] 
 WHERE [Extent1]。[ID] = [Extent2。 [PERSON_ID] 
）AS [PROJECT1] 
）AS [PROJECT1] 
 WHERE [PROJECT1] [ROW_NUMBER> 1）[SKIP1] 
  

I need some help using a lambda expression to remove duplicate entries in my Entity Framework context. I have a table with the following columns:

Id, DateOfIncident, Description, EmployeeId, IncidentTypeId, and IsAttendanceIncident

I want to remove duplicate entries where DateOfIncident, EmployeeID, IncidentTypeID and IsAttendanceIncident are the same. I do want to keep one entry. I know how to do this using a Stored Procedure with CTE in SQL, but I cannot figure out how to accomplish this task using a Lambda expression.

This code returns a list excluding my duplicates, but now how do I go about removing those duplicates that are not in this list ?

  var query = db.Incidents.Where(x => x.IsAttendanceIncident == "Y").GroupBy(x => new { x.EmployeeId, x.DateOfIncident, x.IsAttendanceIncident })
         .Select(x => x.FirstOrDefault());

UPDATE:

So I went ahead and wrote the custom IEqualityComparer. Now how do I remove the incidents in my context that are not in my distinctItems using the id ?

 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 })

Example 1:

    .Select(x => x.FirstOrDefault());  // your original code which retrieves entities to not delete

var dupes = db.Incidents.Except( query ); // get entities to delete

Example 2:

    .SelectMany( x => x.OrderBy( y => y.Id ).Skip(1) ); // gets dupes directly

var dupes = query; // already have what we need

And finally:

foreach( var dupe in dupes )
{
    db.Incidents.Remove( dupe );
}

Example SQL generated from a test context I used earlier where Person entity has a 1:N relationship with watches:

C#:

context.Persons.SelectMany(x => x.Watches.OrderBy(y => y.Id).Skip(1))

Generated 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]

这篇关于使用lambda删除重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！