Entity Framework 4.0, adding SQL Server view which contains no primary key

Question

I am using Entity Framework 4.0, C# 4.0, .Net 2010 and SQL Server 2008 R2. I have created the following view in my SQL Server database:

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

In want to add this view in entity framework. However it has contain no primary in it. Though following two fields can form a composite primary key (second and third columns).

email.Communication_Point_Id id1
member.Membership_Number Number

I don't know how to add them as part of Entity Framework. Even I have tried adding row_number() (first column) as an additional column by thinking it will act as a kind of primary key but no use. Entity Framework designer is not adding this view in .edmx model file.

I have tried this by entirely removing the .edmx file and in new project only for Entity Framework but no luck. Could someone please provide me a solution to this problem.

What's the purpose of distinct when you're using ROW_NUMBER()? All the rows will be distinct anyway. — Damien_The_Unbeliever, Feb 16 '11 at 15:31
@Damien_The_Unbeliever, I added distinct even before I add the row_number(). I thought row_number() would be work around for the Entity Framework's primary key requirement. But as I said, no use of it. — manu, Feb 16 '11 at 15:35
possible duplicate of [Entity Framework and Sql Server view question](http://stackoverflow.com/questions/1013333/entity-framework-and-sql-server-view-question) — Michael Maddox, Sep 15 '11 at 14:44

score 3 · Accepted Answer · edited May 23 '17 at 12:01

I found a perfect answer from the Entity Framework and Sql Server view question. According to the answer given to that question the above SQL Query (view without primary key) has to be changed like following.

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

The thrid line isnull(member.Membership_Number,-1) Number makes that column as primary key and we can get rid of row_number() over (order by member.Membership_Number) ID or whatever we don't want to be as part of primary key.

This works well in my case.

This is not working for me on EF 5.0 but not sure if my code or EF 5.0 changes. — Dave, Aug 25 '13 at 18:22
@Dave, I am using entity framework 5 and it is working well. However if you want to test it refresh the entities in entity framework. If possible try to simulate the same behavior in a completely new project. I hope that will work it out. — manu, Aug 27 '13 at 15:23
Elangesh - are your other columns besides "Membership_Number" nullable? If I add only the "isnull" to the column I want as the PK, all my columns are still added to the generated key I think because they are also not null. — Dave, Aug 28 '13 at 16:10
@Dave, in that case you need to give NULLIF to the forcefully disable the primary key. I think following link might help you. http://stackoverflow.com/questions/1013333/entity-framework-and-sql-server-view — manu, Aug 29 '13 at 14:01
I was afraid of that. Makes for ugly SQL since a large view. Thanks! — Dave, Aug 30 '13 at 18:19

Entity Framework 4.0, adding SQL Server view which contains no primary key

1 Answers1