时间戳

首页 > 解决方案 > 我需要在表中查询两个重复列中的值

c# - 我需要在表中查询两个重复列中的值

问题描述

我在 ASP.NET Core 中工作。我在查询具有相同的行时遇到问题User_id，Definition_id如果有类似的行，我需要行的 ID。

+----+---------+---------------+
| Id | User-id | Definition-id |
+----+---------+---------------+
|  1 |       1 |             1 |
|  2 |       2 |             1 |
|  3 |       2 |             2 |
|  4 |       3 |             1 |
|  5 |       4 |             1 |
|  6 |       4 |             1 |
|  7 |       5 |             2 |
|  8 |       6 |             1 |
+----+---------+---------------+

我需要像这样查询表，将 { 5, 6 } 返回给我，因为它们具有相同的用户和定义 ID。

我已经尝试Groupby了这两个值，但我无法得到IQueryableorIGrouping给我特定行的 id。

我想它会像那样工作，但事实并非如此。

var placementsWithDuplicates =
    from p in _context.Placements
    group p by new { p.User_id, p.Definition_id } into what
    select new
    {
        Id = what.Id,
        User = what.User_id,
        Defi = what.Definition_id,
    };

foreach (var p in placementsWithDuplicates)
{
    issues.Add(new IssueModel()
        {
            Type = "Praxe ID[" + p.Id + "]",
            Name = "User id [" + p.User + "], Definition id [" + p.Defi + "]",
            Detail = "So user shouldnt have data for more definitons!"
        });
};

感谢 Satish Hirpara 的最佳答案，它需要一些更新，所以我发布了最终运行良好的东西：

var B = from p in _context.Placements
        group p by new { p.User_id, p.Definition_id } into what
        where what.Count() > 1
        select new
        {
            User_id = what.Key.User_id,
            Definition_id = what.Key.Definition_id
        };

var placementsWithDuplicates = from A in _context.Placements
                               join b in B on new { A.User_id, A.Definition_id } equals new { b.User_id, b.Definition_id }
                               select A;

标签： c#asp.netlinqasp.net-coreentity-framework-core

解决方案

请在下面找到 SQL 查询：

    SELECT A.* 
    FROM Placements A 
    INNER JOIN 
     (SELECT User_id, Definition_id FROM Placements 
      GROUP BY User_Id, Definition_id 
      HAVING COUNT(*) > 1) B 
     ON A.User_id = B.User_id AND A.Defination_id = 
     B.Defination_id

您可以创建一个临时表以避免连接子查询。

如果您想要 linq 查询，那么我尝试从上面的查询中创建它，请在下面找到它：

 --- sub query
 var B = from p in Placements 
 group p by new { p.User_id, p.Definition_id } into what 
 where what.count() > 1
 select new
   { User_id = what.User_id, 
     Definition_id =what.Definition_id 
    }; 

    --- join
    Var result =  from A in Placements
                          Join B ON A.User_id = B.User_id 
                          AND A.Defination_id = B.Defination_id
                          Select A

请试试这个。

推荐阅读