If your querying a SQL Server view in entity framework it will attempt to use the first non-nullable column as the primary key. If this column is unique, all good, however if it is not unique, entity framework will return duplicate rows whereby all rows with the same ‘key’ will have the data from the first occurance (the first row) of that ‘key’. This is most likely not the behaviour you want, but its easily fixed:
Preventing entity framework returning duplicate rows from SQL Server views
- If your underlying data has a primary key be sure its selected first in the view.
- Add an artificial key just for this scenario using ROW_NUMBER() and return it first in the view.
- Use AsNoTracking() in your LINQ statement to advise EF to ignore key definitions and just return the data as is. If your data doesn’t have a primary key, AsNoTracking() is the simplest way to resolve this and you don’t lose anything as since you don’t update view records you don’t need EF to ever track this data. Usage is simple:
dbcontext.view.AsNoTracking().Where(x => …)
Hi, thank you so much for this. This happened to me and I was stuck for hours until I found this. THANK YOU!
Also want to ask if you happen to know how to delete one column in this scenario, because when I try to, it gives me an error saying something about all the rows being effected.