Entity Framework: query against view in EF (C# code) returns duplicate results

Question

I'm using EF to query against a DB View. the query can return correct number of records, but all the records are the same. However, when i run the raw query in sql SSMS, everything is fine.
Can somebody give some clue about the possible root cause?

The view definition is like:

 CREATE VIEW [dbo].[v_JobAEWeekly]
  AS
  SELECT        
  VCId, 
  JobRegistryId, 
  JobNamingId, 
  JobPrefix, 
  DATEADD(dd, DATEDIFF(week, CONVERT(DATETIME, '2013-01-01 00:00:00', 102), JobDate) * 7 - 2, CONVERT(DATETIME, '2013-01-01 00:00:00', 102)) AS JobDateWeekSeqStartDate,       
  COUNT(*) AS JobCounts,    
  FROM            dbo.HistoricalJobInfo
  WHERE        (JobStateId = 2) AND (TotalYieldTimeInMinutes = 0)
  GROUP BY VCId, JobRegistryId, JobNamingId, JobPrefix, DATEADD(dd, DATEDIFF(week, CONVERT(DATETIME, '2013-01-01 00:00:00', 102), JobDate) * 7 - 2, CONVERT(DATETIME, '2013-01-01 00:00:00', 102))

  GO

And the query is like:

//Problem:This query will return two records but the two records are the same. 
var jobAEWeeklyHistory = contextDjs.v_JobAEWeekly.Where(x => x.JobRegistryId == 11 && x.JobDateWeekSeqStartDate > date).ToList();

what is the code? Hard to determine the problem if we don't know what the query is or how it is being manipulated. — user1666620
– user1666620, Commented Dec 1, 2014 at 12:03
Are you using Database First? If so, you need to set the entity key on your view (inside the .edmx) — Aducci
– Aducci, Commented Dec 1, 2014 at 13:45

Community · Accepted Answer · 2017-05-23 12:32:53Z

2

Reverse Engineering Code First is actually similar to Database First in the sense that it generates models, mappings, and data context for you.

Most likely your problem is caused by the wrong entity key, as @Aducci suggested. Check out this question for detailed explanation.

edited May 23, 2017 at 12:32

CommunityBot

11 silver badge

answered Dec 2, 2014 at 16:21

Sergey Kolodiy

5,9491 gold badge38 silver badges59 bronze badges

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

Comments

dingx · Accepted Answer · 2014-12-03 03:00:51Z

1

The problem is about Primary key.
Each entity in EF should have an ID, and thus primary key. if the source table/view has no primary key, EF will try to inject itself. So here comes the problem, EF will make the combination of all non-nullable columns as the Key. And when EF materialize objects locally, it will only choose the first object with the same Key.

answered Dec 3, 2014 at 3:00

dingx

1,6913 gold badges20 silver badges42 bronze badges

Collectives™ on Stack Overflow

Entity Framework: query against view in EF (C# code) returns duplicate results

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related