关于 c#:Entity Framework 6 和 LINQ – 重复记录

Entity Framework 6 and LINQ – Duplicated Records

我有一个标准的实体框架实现直接查询一个表,根本不涉及任何视图,代码如下:

var r = cxt.HistoricalQualityComponents.Where(f => f.ProducerID == activeProducer);

一切正常,我希望有 24 条记录,我得到 24 条记录。除了当我查看 r 的内容时,我看到了第一个返回的记录,重复了 24 次。我查看生成的 SQL 并直接针对数据库运行它,我得到 24 条唯一记录。我通过在 activeProducer 更改时观察内容,甚至应用排序作为 LINQ 查询的一部分来得出结论。我不知道如何解决这个问题。这发生在我的数据库中有两个表,但没有其他表。

我尝试删除我的 .edmx 实体文件,我重命名了表(和生成的实体),并创建了一个完整的 .aspx 文件来处理实体。当然,我这样修改了查询:

var r = from h in cxt.HistoricalQualityComponents where h.ProducerId == activeProducer select h;

没有什么不同。

这可能完全不相关,但在(非常懒惰地)在两个结果集上尝试以下操作后,我注意到了这种行为:

r.Sort(delegate(HistoricalQualityComponents c1, HistoricalQualityComponents c2) { return Convert.ToDateTime(c2.Pickup_Date).CompareTo(Convert.ToDateTime(c1.Pickup_Date)); });

此时,我只想回到一个干净的状态,这样我就可以实现一个模型类,它将数据存储到一个正确类型的对象中,我可以更适当地对其进行排序,但我不知所措下一步要去哪里。

更新:生成的 SQL 如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
SELECT
    [Extent1].[RcdID] AS [RcdID],
    [Extent1].[FieldRepID] AS [FieldRepID],
    [Extent1].[ProducerID] AS [ProducerID],
    [Extent1].[MonthYear_PK] AS [MonthYear_PK],
    [Extent1].[Weight] AS [Weight],
    [Extent1].[Fat] AS [Fat],
    [Extent1].[Protein] AS [Protein],
    [Extent1].[Lactose] AS [Lactose],
    [Extent1].[SNF] AS [SNF],
    [Extent1].[StateLeuc] AS [StateLeuc],
    [Extent1].[Leuc] AS [Leuc],
    [Extent1].[StateRaw] AS [StateRaw],
    [Extent1].[Raw] AS [Raw],
    [Extent1].[RawBracket] AS [RawBracket],
    [Extent1].[PI] AS [PI],
    [Extent1].[PIBracket] AS [PIBracket],
    [Extent1].[lpc_avg] AS [lpc_avg],
    [Extent1].[Water] AS [Water],
    [Extent1].[AB] AS [AB],
    [Extent1].[SED] AS [SED],
    [Extent1].[mun] AS [mun],
    [Extent1].[LStd] AS [LStd],
    [Extent1].[RStd] AS [RStd],
    [Extent1].[PStd] AS [PStd],
    [Extent1].[SStd] AS [SStd],
    [Extent1].[QualPremRate] AS [QualPremRate],
    [Extent1].[QualPremAmt] AS [QualPremAmt],
    [Extent1].[FYTDQualScore] AS [FYTDQualScore],
    [Extent1].[FYTDQualPrem] AS [FYTDQualPrem],
    [Extent1].[FYTDAvgQualPremRate] AS [FYTDAvgQualPremRate],
    [Extent1].[OtherSolids] AS [OtherSolids],
    [Extent1].[AshFactor] AS [AshFactor],
    [Extent1].[ash_wtd_avg] AS [ash_wtd_avg],
    [Extent1].[fat_wtd_avg] AS [fat_wtd_avg],
    [Extent1].[leuc_wtd_avg] AS [leuc_wtd_avg],
    [Extent1].[protein_wtd_avg] AS [protein_wtd_avg],
    [Extent1].[os_wtd_avg] AS [os_wtd_avg],
    [Extent1].[snf_wtd_avg] AS [snf_wtd_avg],
    [Extent1].[iodine_wtd_avg] AS [iodine_wtd_avg]
FROM (SELECT
    [HistoricalQualityComponents].[RcdID] AS [RcdID],
    [HistoricalQualityComponents].[FieldRepID] AS [FieldRepID],
    [HistoricalQualityComponents].[ProducerID] AS [ProducerID],
    [HistoricalQualityComponents].[MonthYear_PK] AS [MonthYear_PK],
    [HistoricalQualityComponents].[Weight] AS [Weight],
    [HistoricalQualityComponents].[Fat] AS [Fat],
    [HistoricalQualityComponents].[Protein] AS [Protein],
    [HistoricalQualityComponents].[Lactose] AS [Lactose],
    [HistoricalQualityComponents].[SNF] AS [SNF],
    [HistoricalQualityComponents].[StateLeuc] AS [StateLeuc],
    [HistoricalQualityComponents].[Leuc] AS [Leuc],
    [HistoricalQualityComponents].[StateRaw] AS [StateRaw],
    [HistoricalQualityComponents].[Raw] AS [Raw],
    [HistoricalQualityComponents].[RawBracket] AS [RawBracket],
    [HistoricalQualityComponents].[PI] AS [PI],
    [HistoricalQualityComponents].[PIBracket] AS [PIBracket],
    [HistoricalQualityComponents].[lpc_avg] AS [lpc_avg],
    [HistoricalQualityComponents].[Water] AS [Water],
    [HistoricalQualityComponents].[AB] AS [AB],
    [HistoricalQualityComponents].[SED] AS [SED],
    [HistoricalQualityComponents].[mun] AS [mun],
    [HistoricalQualityComponents].[LStd] AS [LStd],
    [HistoricalQualityComponents].[RStd] AS [RStd],
    [HistoricalQualityComponents].[PStd] AS [PStd],
    [HistoricalQualityComponents].[SStd] AS [SStd],
    [HistoricalQualityComponents].[QualPremRate] AS [QualPremRate],
    [HistoricalQualityComponents].[QualPremAmt] AS [QualPremAmt],
    [HistoricalQualityComponents].[FYTDQualScore] AS [FYTDQualScore],
    [HistoricalQualityComponents].[FYTDQualPrem] AS [FYTDQualPrem],
    [HistoricalQualityComponents].[FYTDAvgQualPremRate] AS [FYTDAvgQualPremRate],
    [HistoricalQualityComponents].[OtherSolids] AS [OtherSolids],
    [HistoricalQualityComponents].[AshFactor] AS [AshFactor],
    [HistoricalQualityComponents].[ash_wtd_avg] AS [ash_wtd_avg],
    [HistoricalQualityComponents].[fat_wtd_avg] AS [fat_wtd_avg],
    [HistoricalQualityComponents].[leuc_wtd_avg] AS [leuc_wtd_avg],
    [HistoricalQualityComponents].[protein_wtd_avg] AS [protein_wtd_avg],
    [HistoricalQualityComponents].[os_wtd_avg] AS [os_wtd_avg],
    [HistoricalQualityComponents].[snf_wtd_avg] AS [snf_wtd_avg],
    [HistoricalQualityComponents].[iodine_wtd_avg] AS [iodine_wtd_avg]
FROM
    [dbo].[HistoricalQualityComponents] AS [HistoricalQualityComponents]) AS [Extent1]
WHERE
    [Extent1].[ProducerID] = @p__linq__0


如果您的实体模型中的键与数据库表中的键不匹配,则可能会发生这种情况。

例如,如果数据库中的键是由 (RcdID, FieldRepID) 组成的组合,但在您的模型实体中,只有 RcdID 被定义为键,并且您运行的查询将返回三行,其值为 (1, 1)(1, 3) 你可能会得到一个实体列表,其值为 (1, 1) 三次。

原因是 EF 仅”看到”该对的第一部分 1 作为密钥。该键值被返回 3 次。因为 EF 仅将每个键的一个对象附加到上下文,所以第一行被具体化为具有键 1 的实体,但对于第二行和第三行,没有新实体被具体化。而是将第一行的已附加实体第二次和第三次添加到结果列表中。

长话短说:检查实体模型中定义的键是否与数据库模式中的键匹配。


原创文章,作者:ItWorker,如若转载,请注明出处:https://blog.ytso.com/269766.html

(0)
上一篇 2022年6月23日
下一篇 2022年6月23日

相关推荐

发表回复

登录后才能评论