当使用实体框架核心检索数据时，我得到了重复的数据

Question

我尝试使用Entity Framework Core和FromSqlRaw方法来获取数据，也使用SQL Server中的存储过程：

var ProjectList = db.Projectlist.FromSqlRaw("EXEC [NI].GetProject @ProCode", ProjectProjectCode).AsEnumerable().ToList();

结果是：

"totalRowCount": 4,
  "data": [
    {
      "proName": "RBC CAS Training",
      "proCode": "PR-225/08",
      "less": "YY",
      "imp": "HK",
      "recom": "",
      "lastChanged": "2020-06-08T11:31:40.35"
    },
    {
      "proName": "RBC CAS Training",
      "proCode": "PR-225/08",
      "less": "YY",
      "imp": "HK",
      "recom": "",
      "lastChanged": "2020-06-08T11:31:40.35"
    },
    {
      "proName": "RBC CAS Training",
      "proCode": "PR-225/08",
      "less": "YY",
      "imp": "HK",
      "recom": "",
      "lastChanged": "2020-06-08T11:31:40.35"
    },
    {
      "proName": "RBC CAS Training",
      "proCode": "PR-225/08",
      "less": "YY",
      "imp": "HK",
      "recom": "",
      "lastChanged": "2020-06-08T11:31:40.35"
    }
  ]

在数据库结果中

ProCode      ProName           Less    Imp       Recom           LastChanged
PR-225/08   RBC CAS Training   YY      HK        ""            2020-06-08 11:31:40.350
PR-225/08   RBC CAS Training   YY      HK        ""            2020-06-08 11:31:40.350
PR-225/08   RBC CAS Training   XXX     XX        ""            2020-06-08 11:31:20.973
PR-225/08   RBC CAS Training   XXX     XX        ""            2020-06-08 11:31:20.973

这就是我正在使用的查询

  SELECT PL.ProID,PS.ProCode,ps.ProName,ll.Less ,ll.Imp,ll.Recom,PL.LastChanged
  FROM ProLessonLearn PL
    INNER JOIN ProStruct AS ps ON ps.ProID = PL.ProId
    INNER JOIN LessLearn AS ll ON ll.LessLeaId = PL.LessLeaId
  WHERE  PS.ProCode = 'PR-225/08' and PL.Active=1   
  ORDER BY PS.ProCode

我刚刚发现，没有使用实体框架核心，并使用SqlCommand，它得到了以下结果

{
  "totalRowCount": 4,
  "data": [
    {
      "pro Name": "RBC CAS Training",
      "proCode": "PR-225/08",
      "less": "YY",
      "imp": "HK",
      "recom": "",
      "lastChanged": "2020-06-08T11:31:40.35"
    },
    {
      "proName": "RBC CAS Training",
      "proCode": "PR-225/08",
      "less": "YY",
      "imp": "HK",
      "recom": "",
      "lastChanged": "2020-06-08T11:31:40.35"
    },
    {
      "proName": "RBC CAMMS Training",
      "proCode": "PR-225/08",
      "less": "XXX",
      "imp": "XX",
      "recom": "",
      "lastChanged": "2020-06-08T11:31:20.973"
    },
    {
      "proName": "RBC CAMMS Training",
      "proCode": "PR-225/08",
      "less": "XXX",
      "imp": "XX",
      "recom": "",
      "lastChanged": "2020-06-08T11:31:20.973"
    }
  ]
}

我可以在代码端解决这个问题。需要帮助

Answer 1

这里的问题是您的连接是错误的，并且生成重复的数据

看看这个更简单的系统示例，它可以跟踪一个人曾经生活过的任何地方：

Person - Name, AddressId
John, 1

Address - Id, City, UntilDate
1, Chicago, null

这个查询是OK的：

SELECT p.* FROM person p JOIN address a ON p.addressid = a.id

John, 1

直到我们为约翰在2000年搬到纽约添加另一个地址：

Address - Id, City, UntilDate
1, Chicago, 2000-01-01
1, New York, null

SELECT p.* FROM person p JOIN address a ON p.addressid = a.id

John, 1
John, 1

如果我们显示了所有数据，查询就不会有重复项：

SELECT * FROM person p JOIN address a ON p.addressid = a.id

John, 1, 1, Chicago, 2000-01-01
John, 1, 1, New York, null

我们忘记了这个查询是业务需求"create a page that shows users“的一部分--在这个系统中，当一个用户不再有当前地址时，该用户被标记为已经死亡

SELECT p.*
FROM person p JOIN address a ON p.addressid = a.id
WHERE a.untildate is null

John, 1

那好多了!

因此，要么是因为数据库中确实有重复项(可能性较小)，要么是因为您选择的数据子集包含重复数据，因为在数据中的某个地方您没有选择使其具有唯一性。修复它，确保你排除了不相关的数据(就像我排除了所有用户过去的地址一样)

因为您选择的列子集包含重复数据