实体框架：查询EF（C＃代码）中的视图返回重复的结果 [英] Entity Framework: query against view in EF (C# code) returns duplicate results

问题描述

我正在使用EF来查询数据库视图。查询可以返回正确的记录数，但所有的记录都是一样的。但是，当我在sql SSMS中运行原始查询时，一切都很好。

有人可以给一些关于可能根本原因的线索？

视图定义如下：

  CREATE VIEW [dbo]。[v_JobAEWeekly] 
 AS 
 SELECT 
 VCId，
 JobRegistryId，
 JobNamingId，
 JobPrefix，
 DATEADD （dd，DATEDIFF（周，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（周，CONVERT（DATETIME，'2013-01-01 00:00:00'，102），JobDate）* 7  -  2，CONVERT（DATETIME，'2013-01- 01 00:00:00'，102））
 
 GO 
  

查询如下：

  //问题：此查询将返回两条记录，但两条记录是相同的。 
 var jobAEWeeklyHistory = contextDjs.v_JobAEWeekly.Where（x => x.JobRegistryId == 11&& x.JobDateWeekSeqStartDate> date）.ToList（）; 
  

解决方案

逆向工程代码首先实际上类似于Database First它为您生成模型，映射和数据上下文的感觉。

像@Aducci建议的那样，很可能您的问题是由错误的实体键引起的。详细了解此问题说明。

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

解决方案

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.

这篇关于实体框架：查询EF（C＃代码）中的视图返回重复的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！