CODEWITHSUNDEEP
entity-frameworkduplicates
Eugene Gluhotorenko
Eugene Gluhotorenko

Reputation: 3164

Entity Framework. View return duplicate records

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.

Upvotes: 54

Views: 38727

Answers (5)

Rahul Garg
Rahul Garg

Reputation: 4339

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

Upvotes: 74

Juan Carlos Sánchez Robles
Juan Carlos Sánchez Robles

Reputation: 21

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.

Upvotes: 2

Rikin Patel
Rikin Patel

Reputation: 9383

If you don't want to update edmx and set any key to column &&

if you don't want to update view record (only for get record) then use below code its working for me. 

context.viewname.MergeOption = System.Data.Objects.MergeOption.NoTracking;

context.viewname.Where(x => x.columnname != null);

Upvotes: 1

Moons
Moons

Reputation: 3854

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.

enter image description here

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).

Upvotes: 94

Giovane
Giovane

Reputation: 121

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.

Upvotes: 12

Related Questions