唯一索引的视图与空列

问题描述：

视图我已正确地返回唯一的结果，但由于视图的设置方式（Id字段是从我的EmployeeId表）实体框架试图缓存结果，并不读取场（从而错误，因为它认为它发现重复键）唯一索引的视图与空列

我有以下的表（此处简化）：

Employee 
    Id 
    Name 

Jobs 
    Id 
    Name 

EmployeeJobAssociations 
    Id 
    EmployeeId 
    JobId 
    StartDate 
    EndDate 

EmployeeJobsView 
    Id (This is actually EmployeeId) 
    AssociationId (This is EmployeeJobs's Id) 
    JobId (Job's table Id) 
    Name (EmployeeName) 
    JobName 
    StartDate 
    EndDate

视图代码如下：

SELECT  dbo.Employees.Id, dbo.EmployeeJobAssociations.Id AS AssociationId, 
       dbo.Jobs.Id as JobId, dbo.Employees.Name, dbo.EmployeeJobAssociations.StartDate, 
       dbo.EmployeeJobAssociations.EndDate, Job.Name AS JobName 
FROM   dbo.Jobs AS Job INNER JOIN 
       dbo.EmployeeJobAssociations ON Job.Id = dbo.EmployeeJobAssociations.JobId RIGHT OUTER JOIN 
       dbo.Employees ON dbo.EmployeeJobAssociations.EmployeeId = dbo.Employees.Id

，是因为我使用的雇员作为主要领域（我想员工没有JobAssociation谁在我的视图显示为好）实体框架认为，对某个员工的所有结果都是一样的：

Id  AssociationId  JobId ...  StartDate 
1234  1      1    10/10/10 
1234  2      2    11/11/10 // Unless I turn off tracking it thinks this is the same as the first row 
2222  null    null    null

尽管AssociationId可以为空（但每个EmployeeId只能为空）（ONCE），是否可以使表具有基于EmployeeId（Id）和AssociationId的索引？

答

我结束了各地使用这项工作现在：

设置AssociationId等于0，如果它空，然后创建关闭雇员和AssociationId

ISNULL(dbo.EmployeeJobAssociations.Id, 0) AS AssociationId

，并在实体框架的主键：

modelBuilder.Entity<EmployeeJobAssociationView>.HasKey(x => new { x.Id, x.AssociationId});

唯一索引的视图与空列

相关推荐