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?

marc_s · Accepted Answer · 2017-10-27 04:49:09Z

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.

Mohamed Kamal · Accepted Answer · 2020-05-13 22:25:48Z

0

Thanks for @marc_s answer but

This didn't work for me as RowNum is still nullable however when I added ISNULL(ROW_NUMBER() OVER(ORDER BY SomeValue)) worked

answered May 13, 2020 at 22:25

Mohamed Kamal

14 bronze badges

Collectives™ on Stack Overflow

Entity Framework returns incorrect data from SQL Server view

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related