CODEWITHSUNDEEP
c#linqentity
Metabie
Metabie

Reputation: 77

C# Entity LINQ returns wrong and duplicate values

I use the following code :

List<vw_GetIMeasurements> Imeasurements = context.vw_GetIMeasurements.Where(f => f.MEMBERID == userID).Distinct().ToList();

This returns a list with 12 Values like it should but these values are always the same(even though they aren't in the DB.(Even if they were distinct should solve this)

Visual of the issue

DB :

Database Image

The raw value that is returned is also wrong. What would be the cause of this issue and how can I resolve it?

Upvotes: 3

Views: 2881

Answers (4)

shawad
shawad

Reputation: 355

I had a similar issue - the database view looked fine in SQL server but when debugging, duplicate rows were appearing where rows shared a particular attribute value.

I found that one of the important unique keys was from a outer join table - therefore had the potential to be null - and the model was not assigning as unique.

I was able to fix this problem by setting this field to ISNULL(field, '') - ensuring that a value is always returned - and upon updating, the model correctly interprets the field as unique and as part of a compound key and the rows returned correctly.

I know that one might argue the view design is at fault (i.e. I should not be using an outer join), but in this instance, there was a specific need for the outer join. The fix works because the compound key is still unique even if "" is returned multiple times.

Upvotes: 2

Metabie
Metabie

Reputation: 77

The issue is infact what Richard described. However I was unable to choose a correct "Unique Key" so this caused more issue.

I solved my problem by creating a custom made class and creating this in SQL. I also limited my Select to 2 of the 3 Columns.

List<Measurement> result = (from f in context.vw_GetIMeasurements where f.MEMBERID == userID select new Measurement { Category = f.atcmCATEGORYCODEID, Size = f.imMeasurementNumber }).ToList();

Measurement is in the case the self made Class.

Upvotes: 0

Richard Friend
Richard Friend

Reputation: 16018

I have had this problem before - it turned out that the view did not have unique entity keys set - make sure your primary keys for your view that is auto created by entity framework is indeed unique...

Upvotes: 8

bAN
bAN

Reputation: 13825

Maybe try a more simple syntax like this

var measure = from f in context.vw_GetIMeasurements
              where  f.MEMBERID == userID
              select f;

This kind of thing is working for me..

Upvotes: 0

Related Questions