Entity Framework. View return duplicate records

Question

I use Entity Framework that contains view. And I have query:

var data = this.context.vwRevenues
    .Where(x => x.revenue >= 0);
    .OrderByDescending(x => x.year)
    .ThenByDescending(x => x.month)
    .Take(10)
    .ToList();

This query returns set of entities, but 1st entity equals 5th.

data[0] == data[4] // true

I take sql script for this query from sql tracer and run it into SQL Management Studio, it returns different records.

Answer 1

As per @Giovane Answer's

We had the same problem in our system with Entity Framework dealing with views. Try using ROW_NUMBER () OVER () SQL to create a column with unique values​​, but did not work.

I did the same thing but to make it work i need to open the EDMX model and then select a this column as an Entity Key.

Then it will work

There is a very good article on this

Duplicate Records

The articles most important line is:

When including a view in your Entity Model, the model seems to simply use the first not-nullable columns as primary key (as all columns used in the primary key should be non-nullable).

Answer 2

You only need to do: context.viewname.AsNoTracking().Where(x => x.ColumnName != null);

Answer 3

We had the same problem in our system with Entity Framework dealing with views. Try using ROW_NUMBER () OVER () SQL to create a column with unique values​​, but did not work.

We need to insert a field more, an FK for another table in the view so that it could add as an additional training for mebro EntityKeyMembers Elimite and so the problem of repetition.

Thus, if the problem persists in this kind of situation, the solution is to insert a FK column for it to be MEMBERS of the fields that form the EntityKey of the table.

Answer 4

In the view try converting the first record to a not null value, like this:

isnull(ROW_NUMBER() OVER (ORDER BY "Column"), 0) AS Row

It indicates to the Entity Framework that can be the primary key automatically.