强制EF返回视图中的所有行的最佳方式是什么？ [英] What's the best way to force EF to return all rows in a view?

问题描述

我有一个视图，其中一个连接的列可以为空，但通常是两行之间唯一的区别项。我看到EF在视图中的所有不可空项目中构建了一个主键。我注意到，当我从视图中拉出时，这个可空的列并不总是被正确地返回，我读到它与它映射到键的方式有关，如果看到键，它将返回相同的行已经存在。

理想情况下，最好的解决方案是使我的列不能为空，但是我不能这样做，而不会导致更大的问题。

$ b另一个想法是使用 ROW_NUMBER（）来创建一个主键。我不确定这是否可能会引起类似的问题（如果上下文在调用之间没有刷新，那么它完全是完全关闭的，还是足够聪明才能意识到查询不同？）我也担心需要一个code> ORDER BY 的功能，以及如何影响行的动态排序。

什么是确保所有我的行完全按照SQL查询的顺序返回，最不受打击的性能？

谢谢..

示例：

 视图：一个int，B int，C int？ 
 
 SQL结果：
 1，2，null 
 1，3，10 
 1，3，11 
  / pre> 
 
 
 EF将返回以下内容：
  1，2 ，null 
 1，3，10 
 1，3，10 
  
 I 
解决方案
这是由于身份映射模式发生的。默认情况下，EF跟踪已加载的实体（由实体密钥标识） - 如果结果集包含重复的实体密钥，则EF认为它与已加载的实体相同，并且不为这些实体创建新的实体实例重复记录 - 而是使用该密钥为第一条记录创建的实例。这对于更改跟踪和将更改保存到数据库的能力是必需的。 
 
 
 
在您的情况下，您很可能不想将更改保存回数据库，因为这些记录不会提供必要的信息，以便能够执行此操作。所以加载没有变化跟踪的记录，它应该跳过身份映射模式，并为结果集中的每个记录生成新的实体实例：
  context.YourEntitySet.MergeOption = MergeOption.NoTracking; 
 //现在执行你的查询
  
 
I have a view where one of the joined columns is nullable but is often the only distinguishing item between two rows.  I see that EF built a primary key out of all the non-nullable items in the view.  I've noticed that when I pull from the view, this nullable column does not always get returned correctly, and I read that it has to do with the way it maps to the key, and will return the same row if it sees the key already exists.

Ideally the best solution would be to make my column not-nullable, but I can't do that without causing larger problems.

The other idea was to use ROW_NUMBER() to make a primary key. I am unsure whether that may cause similar issues (if the context isn't refreshed between calls, would it go solely off that or it is smart enough to realize that the queries are different?)  I also worry about performance of needing an ORDER BY for the function and how that would affect dynamic ordering of the rows.

What is the best way to ensure all my rows are returned exactly as they appear through the SQL query with the least hit to performance?

Thank you..

Example:
view: A int, B int, C int?

SQL Results:
1, 2, null
1, 3, 10
1, 3, 11
EF will return something like :
1, 2, null
1, 3, 10
1, 3, 10
I need to get that 11, too.
解决方案
This happens due to identity map pattern. By default EF keep track of already loaded entities (identified by the entity key) - if the result set contains repeating entity key EF thinks that it is the same entity as the one already loaded and it doesn't create a new entity instance for those repeating records - instead it uses the instance created for the first record with that key. This is necessary for change tracking and for ability to save changes back to the database. 

In your case you most probably don't want to save changes back to database because these records don't give you necessary information to be able to do that. So load records without change tracking and it should skip identity map pattern and generate new entity instance for every record in the result set:
context.YourEntitySet.MergeOption = MergeOption.NoTracking; 
// Now execute your query


                        
这篇关于强制EF返回视图中的所有行的最佳方式是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！