CODEWITHSUNDEEP
c#sql-serverentity-framework
dingx
dingx

Reputation: 1671

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

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();

Upvotes: 3

Views: 1167

Answers (2)

dingx
dingx

Reputation: 1671

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.

Upvotes: 1

Sergey Kolodiy
Sergey Kolodiy

Reputation: 5899

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.

Upvotes: 2

Related Questions