3

I am using a view to return a complex search query. When I usin linq to query against EF it is returning the same row 3 times(the actual rowcount is correct).

using LinqPad I have run the same linq against my ef entity and the actual database view.

ReadmitPatientList
    .AsQueryable()
    .Where("PatientLastName.StartsWith(\"cooper\")")
    .OrderBy (rpl => rpl.PatientLastName)
    .Dump();

That is the linq I am using for both.

linqpad shows the lambda as this: EF:

ReadmitPatientList.MergeAs (AppendOnly)
   .Where ( => .PatientLastName.StartsWith ("cooper"))
   .OrderBy (rpl => rpl.PatientLastName)

DB

ReadmitPatientList
   .Where ( => .PatientLastName.StartsWith ("cooper"))
   .OrderBy (rpl => rpl.PatientLastName)

I cannot post the results...but EF returns three rows of the same record. DB returns 3 rows of individual records. As does my sql query.

What about my EF LINQ do I need to change to make it work correctly?

The sql code that is generated by the EF Linq query Actually returns the correct results if run in SQL explorer.

ecathell
  • 1,016
  • 13
  • 25
  • I'm not sure I understand. Is the rowcount correct as in 3 or 1? You may try .Where("it.PatientLastName LIKE 'cooper%'"). – Fabian Nicollier May 20 '11 at 20:50
  • the rowcount is supposed to be 3 unique records for the same person. (ie three distinct visits) the EF query is returning the same record 3 times. the direct db query is returning correctly – ecathell May 20 '11 at 22:23
  • @ours I tried it that way with the same result agains EF, it would not work against the Straight DB. – ecathell May 20 '11 at 22:36

1 Answers1

4

This happens if Patient entity doesn't have primary key or columns inferred as primary key are same across multiple records in the result set. EF uses internally identity map which requires that each uniquely identified record must reuse the same instance of the entity. So if you return three records from the database which have same unique identification for EF will return enumeration of three same instances representing the first record from the result set (more about identity map also here). The same behaviour is in Linq-to-sql's DataContext.

Community
  • 1
  • 1
Ladislav Mrnka
  • 349,807
  • 56
  • 643
  • 654
  • GAH...you know what, I think i DID for get to add a key attribute to the ROWID field...let me try that and get back to you. – ecathell May 21 '11 at 15:59
  • ok adding key attribute to the RowID property did not work. RowID is a GUID, so it will be unique. How do I fix this since I am using a view, even a sproc would have this problem I assume. – ecathell May 21 '11 at 16:15
  • erm..just to clarify I added the key to my DomainService metadata file. Which I dont know how to make linq use, but my application is still showing the same behavior after it was recompiled. – ecathell May 21 '11 at 16:16
  • ok, in my model diagram, i can see that it appears that it has inferred that patientID is the keyvalue, which is not correct. – ecathell May 21 '11 at 16:18
  • I think i found how to fix it, I edited the entity in the edmx and changed the inferred key to the rowid I wanted Testing now. – ecathell May 21 '11 at 16:22
  • This helped me to find out that I had put Key attribute on wrong property. Thanks. – Maheep Jan 16 '16 at 14:51