实体框架查询中令人难以置信的重复

My SQL query against a particular view returns me 3 different rows.

针对特定视图的SQL查询返回3个不同的行。

 select * from vwSummary
 where vidate >= '10-15-2010' and vidate <= '10-15-2010'
 and idno = '0330'
 order by viDate

But if i run the same query through my entity framework, I get 3 rows but all the 3 rows are same, equivalent to the third row.

但是如果我通过我的实体框架运行相同的查询，我得到3行，但所有3行都相同，相当于第三行。

        firstVisibleDate = new DateTime(2010, 10, 15);

        lastVisibleDate = new DateTime(2010, 10, 15);

var p1 = (from v in db.vwSummary
                     where v.viDate >= firstVisibleDate && v.viDate <= lastVisibleDate
                     && v.IDNo == "0330"
                          select v).ToList();

Can someone please help me to resolve this issue.

有人可以帮我解决这个问题。

EDIT: I changed my query like this and it works. But still I want to go back to the one shown above as I have to iterate again for more processing.

编辑：我改变了我的查询，这是有效的。但是我仍然想回到上面显示的那个，因为我必须再次迭代以进行更多处理。

List<objectName> p1 = (from v in db.vwSummary
                     where v.viDate >= firstVisibleDate && v.viDate <= lastVisibleDate
                     && v.IDNo == "0330"
                          select new <ObjectName>
{
a = v.a
b = v.b
}
).ToList<ObjectName>();

5 个解决方案

#1

I had a similar issue and I solved it by changing the merge option of the ObjectSet. Example:

我有一个类似的问题，我通过更改ObjectSet的合并选项解决了它。例：

    using (TargetDBDataContext db = new TargetDBDataContext())
    {
        db.SomeView.MergeOption = System.Data.Objects.MergeOption.NoTracking;
        return db. SomeView.ToList();
    }

It looks like entity framework(EF) doesn’t handle correctly views that have duplicated primary keys or no primary keys at all. So when there are two rows that EF is considering equal, EF will load first row as it should but will not load the second row because it will consider it’s already loaded.

看起来实体框架（EF）无法正确处理具有重复主键或根本没有主键的视图。因此，当有两行EF考虑相等时，EF会按原样加载第一行，但不会加载第二行，因为它会认为它已经加载了。

#2

Entity Framework exposes a number of performance tuning options to help you optimise the performance of your applications. One of these tuning options is .AsNoTracking(). This optimisation allows you to tell Entity Framework not to track the results of a query. This means that Entity Framework performs no additional processing or storage of the entities which are returned by the query. However it also means that you cant update these entities without reattaching them to the tracking graph.

实体框架提供了许多性能调优选项，可帮助您优化应用程序的性能。其中一个调整选项是.AsNoTracking（）。此优化允许您告知实体框架不跟踪查询的结果。这意味着实体框架不会执行查询返回的实体的其他处理或存储。但是，这也意味着您无法更新这些实体，而无需将它们重新连接到跟踪图。

You can set AsNoTracking option directly on your view to resolve this issue.

您可以直接在视图上设置AsNoTracking选项以解决此问题。

context.viewname.AsNoTracking().Where(x => x.ColumnName != null);

context.viewname.AsNoTracking（）。Where（x => x.ColumnName！= null）;

#3

Set entity key on the entity model of the view. This worked for me in two separate instances. You can use one or more properties in the key.

在视图的实体模型上设置实体键。这在两个不同的实例中对我有用。您可以在密钥中使用一个或多个属性。

#4

WORKAROUND: I changed my query like this and it works. But still I want to go back to the one shown above as I have to iterate again for more processing.

解决方法：我改变了我的查询，它可以工作。但是我仍然想回到上面显示的那个，因为我必须再次迭代以进行更多处理。

List<objectName> p1 = (from v in db.vwSummary
                     where v.viDate >= firstVisibleDate && v.viDate <= lastVisibleDate
                     && v.IDNo == "0330"
                          select new <ObjectName>
{
a = v.a
b = v.b
}
).ToList<ObjectName>();

I found the source of the problem from here and here. I guessed this should be an issue as I didnt have a very good key in my view as the view was more of a summary report. So I am sticking to the workaround I found in my other answer.

我从这里和这里找到了问题的根源。我猜这应该是一个问题，因为我的观点中没有一个非常好的关键，因为该视图更像是一个摘要报告。所以我坚持我在其他答案中找到的解决方法。

So if you find a similar issue, the problem is, add a proper primary key to your table or the view. If you cannot add one try something similar to the work around.

因此，如果您发现类似的问题，问题是，为您的表或视图添加正确的主键。如果你不能添加一个类似于解决方法的东西。

#5

I just experienced this issue and thought it was my implementation until I found this post. The only workaround I managed to get working was to actually run the sqlquery as follows:-

我刚刚遇到这个问题，并认为这是我的实现，直到我找到这篇文章。我设法工作的唯一解决方法是实际运行sqlquery，如下所示： -

using(var db = new Tpr.Models.MyContext())
{
    var model = _uow._context.Database.SqlQuery<MyTable>(string.Format("select * from MyTable where ID = '{0}'", "12345678"));

    Assert.IsNotNull(model);
}

#1