c# - Entity Framework 4.0，添加不包含主键的SQL Server View

Question

我正在使用 Entity Framework 4.0、C# 4.0、.Net 2010 和 SQL Server 2008 R2。我在我的 SQL Server 数据库中创建了以下 View :

create view viewGetMember 
as 
    select distinct
        row_number() over (order by member.Membership_Number) ID,
        email.Communication_Point_Id id1,
        member.Membership_Number Number,
        dvt.Default_Name ParticipationStatus,
        person.Given_Name GivenName,
        person.Last_Name LastName,
        adrs.House_Number HouseNumber,
        adrs.Street Street,
        adrs.Post_Code PostCode,
        email.Email_Address EmailAddress
    from 
        Participation member
    inner join 
        Party_Participation pp on member.Participation_Id = pp.Participation_Id
    inner join 
        Party party on pp.Party_Id = party.Party_Id
    inner join 
        Individual person on party.Party_Id = person.Party_Id
    inner join 
        Domain_Value_t9n dvt on member.Participation_Status = dvt.Domain_Value_Id
    inner join 
        Communication_Point cpadrs on party.Party_Id = cpadrs.Party_Id 
    inner join 
        Communication_Point cpemail on party.Party_Id = cpemail.Party_Id
    inner join 
        [Address] adrs on cpadrs.Communication_Point_Id = adrs.Communication_Point_Id
    inner join 
        Email email on cpemail.Communication_Point_Id = email.Communication_Point_Id
    where 
        member.Membership_Number is not null 
go

select * from viewGetMember

想要在 Entity Framework 中添加这个 View 。但是它没有包含主要内容。虽然下面两个字段可以组成一个复合主键(第二列和第三列)。

• email.Communication_Point_Id id1
• member.Membership_Number 编号

我不知道如何将它们添加为 Entity Framework 的一部分。甚至我也曾尝试将 row_number() (第一列)添加为附加列，认为它将充当一种主键但没有用。 Entity Framework 设计器未在 .edmx 模型文件中添加此 View 。

我已经通过完全删除 .edmx 文件并在新项目中仅针对 Entity Framework 进行了尝试，但没有成功。有人可以为我提供这个问题的解决方案吗。

Answer 1

我从 Entity Framework and Sql Server view question 中找到了一个完美的答案.根据对该问题的回答，上述 SQL 查询(没有主键的 View )必须更改如下。

create view viewGetMember as 
select distinct
isnull(member.Membership_Number,-1) Number,
dvt.Default_Name ParticipationStatus,
person.Given_Name GivenName,
person.Last_Name LastName,
adrs.House_Number HouseNumber,
adrs.Street Street,
adrs.Post_Code PostCode,
email.Email_Address EmailAddress
from Participation member
inner join Party_Participation pp on member.Participation_Id = pp.Participation_Id
inner join Party party on pp.Party_Id = party.Party_Id
inner join Individual person on party.Party_Id = person.Party_Id
inner join Domain_Value_t9n dvt on member.Participation_Status = dvt.Domain_Value_Id
inner join Communication_Point cpadrs on party.Party_Id = cpadrs.Party_Id 
and cpadrs.Communication_Point_Type in 
(select dv.Domain_Value_Id from Domain_Value dv where dv.Short_Code = 'ADDRESS') 
inner join Communication_Point cpemail on party.Party_Id = cpemail.Party_Id 
and cpemail.Communication_Point_Type in 
(select dv.Domain_Value_Id from Domain_Value dv where dv.Short_Code = 'EMAIL') 
inner join Address adrs on cpadrs.Communication_Point_Id = adrs.Communication_Point_Id
inner join Email email on cpemail.Communication_Point_Id = email.Communication_Point_Id and cpemail.Is_Preferred = 1
where 
member.Membership_Number is not null
go
select * from viewGetMember
go

第三行 isnull(member.Membership_Number,-1) Number 将该列作为主键，我们可以去掉 row_number() over (order by member.Membership_Number) ID 或任何我们不想作为主键一部分的内容。

这对我来说效果很好。