Duplicate rows returned when querying views with entity framework

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

  1. If your underlying data has a primary key be sure its selected first in the view.
  2. Add an artificial key just for this scenario using ROW_NUMBER() and return it first in the view.
  3. 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 => …)

Leave a Reply

Your email address will not be published. Required fields are marked *