LINQ to SQL query returning duplicates

Question

I have a Premiums table that I attempting to query using the following LINQ-to-SQL:

var premiums = sourcePolicyContext.Premiums.Where(prm => prm.Policy_Number == "07748106");

This runs the following SQL against the database:

exec sp_executesql N'SELECT [t0].[Policy Number] AS [Policy_Number], ' + 
                           '[t0].[PremiiumType] AS [Premiium_Type], [t0].[Number], ' +
                           '[t0].[Effective Date] AS [Effective_Date], ' + 
                           '[t0].[Entry Date] AS [Entry_Date], ' +
                           '[t0].[Collision Premium] AS [Collision_Premium], ' +
                         '[t0].[Non Collision Premium] AS [Non_Collision_Premium], ' +
                           '[t0].[Tow Premium] AS [Tow_Premium], ' +
                      '[t0].[Other Coverage1 Premium] AS [Other_Coverage1_Premium] ' +
                    'FROM [dbo].[Premium Table] AS [t0]' +
                    'WHERE [t0].[Policy Number] = @p0', 
                   N'@p0 nvarchar(4000)',
                     @p0=N'07748106'

This query returns two rows when run directly, as expected. It also results in two LINQ to SQL entities, however the data in the two entities is just duplicates of the first row in the SQL query results. Why might this be happening?

The code is just a test line. I have it breakpointed and force the evaluation of the query by expanding the results. I am testing it because I have code elsewhere in the system that iterates over a parent table to Premium, and iterates over each Premium in that parent table, then uses the premium values to perform a calculation. The calculation was producing invalid results due to this behavior. — Starr
– Starr, Commented Nov 2, 2011 at 21:42
Why is the query expected to return two rows when run directly? — Pieter Müller
– Pieter Müller, Commented Nov 2, 2011 at 21:47
Because the query select * from [dbo].[Premium Table] where [Policy Number] = '07748106' returns two rows. The two rows returned, by both that query and the stored procedure, are distinct from one another. Different premium values, different premium types, etc... However it seems that LINQ-to-SQL recognizes there are two results, but fills both resulting entities with the data from only the first returned row. — Starr
– Starr, Commented Nov 2, 2011 at 21:51

Pieter Müller · Accepted Answer · 2011-11-02 22:06:23Z

3

Not sure if this will help, but if you haven't already, try giving the table a dedicated unique identity column and set it as primary key. Make sure your LINQ-TO-SQL definitions know about the primary key column.

edited Nov 2, 2011 at 22:06

answered Nov 2, 2011 at 21:55

Pieter Müller

4,7036 gold badges41 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Starr Over a year ago

That did it. Not sure why it was necessary, the natural key I set up in the ORM designer was already unique, so I shouldn't have needed a surrogate key. Thanks!

Clockwork-Muse Over a year ago

@Starr You're right, that should have removed the necessity for the natural key. I'd check your Equals() method, and related definitions, just in case. Also, what about implementing IComparable?

DCShannon Over a year ago

We had this problem, and found that someone had specified the wrong column as the primary key in the mapping.

Collectives™ on Stack Overflow

LINQ to SQL query returning duplicates

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related