sql - Using a view with no primary key with Entity -

i started on project converting application raw ado.net , embedded sql entity. ran in problem 1 of views used application. view has no primary key , no column (or combination of columns) uniquely identifies row. here select view created with:

select filingmonth, ceil(filingmonth / 3), licnum, filingyear, decode(grouping(insurername), '1', '- insured -', insurername), insurername, policylinecode, linedescription, sum(nvl(grosspremium, 0)), sum(decode(taxexempt, 1, grosspremium, 0)), trunc( case when ( b.rsn not null or a.zeroreport = 1 ) , b.datereceived null a.datereceived else b.datereceived end), sum(aip.iscompanyadmitted(b.naiccocode, b.naicalienid)), a.insuredid aip.slbtransinsured left outer join aip.slbtransinsurer b on a.insuredid = b.insuredid left outer join aip.slblinecodes c on b.policylinecode = c.linecode a.submitted = 1 , a.entryincomplete = 0 group licnum, filingmonth, filingyear, trunc( case when ( b.rsn not null or a.zeroreport = 1 ) , b.datereceived null a.datereceived else b.datereceived end), rollup(insurername, aip.iscompanyadmitted(b.naiccocode, b.naicalienid), policylinecode, linedescription), a.insuredid; 

and here sample data showing there rows duplicated (rows 3 , 4):

filingmonth ceil(filingmonth/3) licnum filingyear decode(grouping(insurername),'1','-allinsured-',insurername) insurername policylinecode linedescription sum(nvl(grosspremium,0)) sum(decode(taxexempt,1,grosspremium,0)) trunc(casewhen(b.rsnisnotnullora.zeroreport=1)andb.datereceivedisnullthena.datereceivedelseb.datereceivedend) sum(aip.iscompanyadmitted(b.naiccocode,b.naicalienid)) insuredid 6 2 8150 2007 savers property , casualty insurance co savers property , casualty insurance co 17 other liability 721.25 0 18-jul-07 0 81 6 2 8150 2007 savers property , casualty insurance co savers property , casualty insurance co 17 721.25 0 18-jul-07 0 81 6 2 8150 2007 savers property , casualty insurance co savers property , casualty insurance co 721.25 0 18-jul-07 0 81 6 2 8150 2007 savers property , casualty insurance co savers property , casualty insurance co 721.25 0 18-jul-07 0 81 

insuredid pk aip.slbtransinsured table, rsn pk aip.slbtransinsurer , aip.slblinecodes.

is @ possible add view entity model without unique identifier? or there easy way add unique row identifier view? view read from, never written to.

is @ possible add view entity model without unique identifier?

if without primary key, no. result kind of error:

one or more validation errors detected during model generation:

system.data.edm.edmentitytype: : entitytype 'salesoneachcountry' has no key defined. define key entitytype. system.data.edm.edmentityset: entitytype: entityset salesoneachcountrylist based on type salesoneachcountry has no keys defined.

if without unique identifier, yes, albeit has non-desirable output. records same identifier reference same object, called identity map pattern

an example, if view produces these 2 rows:

country year totalsales philippines 2010 20.000000 philippines 2011 40.000000 

if map primary key on country field only, e.g.

public class salesoneachcountry { [key] public int countryid { get; set; } public string countryname { get; set; } public int oryear { get; set; } public long salescount { get; set; } public decimal totalsales { get; set; } } 

, view produces above 2 rows on oracle query editor, entity framework produces incorrect output:

country year totalsales philippines 2010 20.000000 philippines 2010 20.000000 

entity framework take second row same object first row.

to guarantee uniqueness, must identify columns makes each row unique. in above example, year must included primary key unique. i.e.

public class salesoneachcountry { [key, column(order=0)] public int countryid { get; set; } public string countryname { get; set; } [key, column(order=1)] public int oryear { get; set; } public long salescount { get; set; } public decimal totalsales { get; set; } } 

making primary key similar attributes above, entity framework can correctly map each view's row own objects. hence, entity framework can display same rows view have.

country year totalsales philippines 2010 20.000000 philippines 2011 40.000000 

full details here: http://www.ienablemuch.com/2011/06/mapping-class-to-database-view-with.html

---

then regarding views don't have columns make row unique, easiest way guarantee entity framework can map each of view's row own objects create separate column view's primary key, candidate create row number column on each row. e.g.

create view rownumberedview select row_number() over(order <columns of view sorting>) rn , * your_existing_view 

then assign [key] attribute on rn property of class rownumberedview