Entity Framework returns incorrect data from SQL Server view

Question

I have a SQL Server database that contains a view.

When I execute this query:

select * 
from HistoryListingView 
order by RequestTime desc`

I obtain this result:

But, in my controller (in an ASP.NET MVC application), I only perform this code:

return Ok(_db.HistoryListingViews.OrderByDescending(r => r.RequestTime));

And the data received doesn't correspond; more precisely the column DataType is incorrect in row 2 and 4, all others columns seem to be correct.

[  
   {  
      "dataType":"Type de stationnement",
      "actionType":"Obtenir plusieurs entrées",
      "requestTime":"2017-10-26T23:06:43.81",
      "username":"admin",
      "jsonParameters":"[]",
      "error":null,
      "userSessionRequestErrorId":null
   },
   {  
      "dataType":"Type de stationnement",
      "actionType":"Obtenir plusieurs entrées",
      "requestTime":"2017-10-26T23:06:43.81",
      "username":"admin",
      "jsonParameters":"[]",
      "error":null,
      "userSessionRequestErrorId":null
   },
   {  
      "dataType":"Local",
      "actionType":"Obtenir plusieurs entrées",
      "requestTime":"2017-10-26T23:06:42.687",
      "username":"admin",
      "jsonParameters":"[]",
      "error":null,
      "userSessionRequestErrorId":null
   },
   {  
      "dataType":"Local",
      "actionType":"Obtenir plusieurs entrées",
      "requestTime":"2017-10-26T23:06:42.687",
      "username":"admin",
      "jsonParameters":"[]",
      "error":null,
      "userSessionRequestErrorId":null
   }
]

My context is configured with disabled lazyLoading and disabled ProxyCreation, and my JsonFormatter (Newtonsoft) is using basic settings:

CamelCasePropertyNamesContractResolver

and

ReferenceLoopHandling.Ignore

I can't see what can change the data. Is there something like records recycle to get better performance?

Answer 1

There is a subtle problem with views when used from Entity Framework.

If you have a table, do use it with EF, you need to have a primary key to uniquely identify each row. Typically, that's a single column, e.g. an ID or something like that.

With a view, you don't have the concept of a "primary key" - the view just contains some columns from some tables.

So when EF maps a view, it cannot find a primary key - and therefore, it will use all non-nullable columns from the view as "substitute" primary key.

I don't know what these are in your case - you should be able to tell from the .edmx model, or from the code class generated from the database.

Looking at your data, I assume that RequestTime is the only non-nullable column in your view. EF will now assume this is the "substitute" primary key" for this view. When EF goes to read the data, it will read the first line ("Type de stationnement") and create an object for that.

When EF reads the second line, the RequestTime is identical and therefore the substitute "primary key" (the RequestTime) is the same as before - so it doesn't bother creating a new object with those values read, but the primary key is the same, it hence must be the same object as it has already read before, so it uses that object instead.

So the problem really is that you can't have explicit primary keys on a view.

Either you can tweak your EF model to make it clear to EF what the primary key is (you need to make sure those columns are non-nullable) - or you need to add something like a "artificial" primary key to your view:

CREATE VIEW dbo.HistoryListingView 
AS
   SELECT 
       (all the columns you already have in your view),
       RowNum = ROW_NUMBER() OVER(ORDER BY SomeValue)
   FROM
       dbo.YourBaseTable

By adding this RowNum column to your view, which just numbers the rows 1, 2, ...., n, you get a new, non-nullable column which EF will include into the "substitute PK" and since those numbers are sequential, no two rows will have the same "PK" values and therefore none will erroneously be replaced by something that's been read from the database already.