Entity Framework 4.0, adding a SQL Server view that does not contain a primary key

Question

I am using Entity Framework 4.0, C # 4.0, .Net 2010 and SQL Server 2008 R2. In my SQL Server database, I created the following 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 

I want to add this view to the entity infrastructure. However, there is no primary information in it. Although the following two fields can form a composite primary key (second and third columns).

• email.Communication_Point_Id id1
• member.Membership_Number Number

I do not know how to add them as part of the Entity Framework. Even I tried to add row_number() (the first column) as an additional column, believing that it would act as a kind of primary key, but not use it. Entity Framework Designer does not add this view to the .edmx model file.

I tried this by completely deleting the .edmx file and into a new Entity Framework-only project, but no luck. Can someone please provide me a solution to this problem.

Answer 1

I found the perfect answer from a question from Entity Framework and Sql Server . In accordance with the answer to this question, the above SQL query (view without primary key) should be changed as follows.

 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 isnull(member.Membership_Number,-1) Number makes this column the primary key, and we can get rid of row_number() over (order by member.Membership_Number) ID or that we do not want to be part of the primary key.

This works well in my case.