LINQ in .NET returning duplicate rows from a working SQL view


The Problem

For a little background, the view is performing a JOIN on another table, then grouping the results by an ID and summing another column to create and ID,SUM pair of columns. It looks something like the following:

SELECT b.Id, COUNT(DISTINCT a.UserId) AS users FROM table1 AS a INNER JOIN table2 AS b ON a.Id = b.Id GROUP BY b.Id, a.OtherId HAVING (COUNT(DISTINCT a.UserId) > 1)

The Cause

The reason this is happening is that the Entity Framework has attempted to identify a unique primary key on the view when you added the view object to your .edmx file. Since no primary key is set on a view, it simply took the first column in the table. The problem here should now be obvious, the first column is clearly not unique.

Since EF thinks the first column is unique, it does some internal data building on the result set to build out the entity object by pairing the rest of the data columns to its matching record based on this “primary” key value. The end result is that the same record is duplicated for every data row since the same value is always assigned to the identical primary key.

The Solution

To solve this problem there are two straightforward options.

The first is that you can simply add a column to your view select statement which is unique. You don’t have to use that column in your program but it will allow EF to properly build each object for you. Of course, base on your query and groupings this may not be possible.

The second option is to add a pseudo key to your view. This option should work in basically every case and is the option that we went with. The new column will create an incrementing integer which is basically a pointer to the row number of the results. To do this in MS SQL Server, add the following to the beginning of your column selection:

ROW_NUMBER() OVER( ORDER BY b.Id) as NID

which alters the example view to:

SELECT ROW_NUMBER() OVER( ORDER BY b.Id) as NID, b.Id, COUNT(DISTINCT a.UserId) AS users FROM table1 AS a INNER JOIN table2 AS b ON a.Id = b.Id GROUP BY b.Id, a.OtherId HAVING (COUNT(DISTINCT a.UserId) > 1)

====================================================================

If you have View in database and you are writing LINQ query on EF (which is referencing that VIEW), you might get same rows multiple times.  That is because of lack of primary key in view.
Now In order to make row unique in View , you may create cluster index view but instead of that , you can create one column which has unique row id.
to do that in Sql Server : ROW_NUMBER() OVER( ORDER BY b.Id) as NID

Now in your edmx file (EF file) , you might have to remove entity key from other columns in view. To do that, right click on column name in edmx , uncheck entity key if its selected. Only above created column NID should have this entity key checked.

Hope this helps someone as I spend entire day for this 🙂

happy coding…..

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s