Unbelievable duplicate in an Entity Framework Query

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

 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.

        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>();

Answers


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

    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.


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.

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

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


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.


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.


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:-

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);
}

Need Your Help

Do I need to restart Apache after changing the php.ini file?

php apache

If I make a change to a setting in the php.ini file - do I need to restart Apache in order for it to take effect?