sql - Entity Framework 4: how to insert the next highest number without using an identity field? -

i have product table uses upc part of primary key. fine until product doesn't have upc , recommended way solve generate number between 8004 + identity number , 8005 + identity number.

i need generate unique upc if upc 0 while in transaction, able retrieve new upcs products had 0 upc value.

in sql, this:

 insert product (id, name)  select min(pivottable.value), 'new product' name  pivottable  not exists(       select null nothing       product      pivottable.value = product.id ) ,  pivottable.value > 8004000000 , pivottable.value < 8005000000   select id    product   name = 'new product' -- assuming name unique 

how in entity framework 4? separate concern under single transaction, assigning numbers of sets of missing upcs assign same upc new products.

edit:

i ended creating view looks next highest number, ef won't generate table in diagram because cannot determine primary key. if hack xml, works until update database, erases changes.

select min(id), 'new product' name ( select distinct id          product p1         p1.id > 8004000000 , p1.id < 8005000000        union        select distinct coalesce( id, 8004000000) id)       left outer join       ( select distinct id          product p2         p2.id > 8004000000 , p2.id < 8005000000        union        select distinct coalesce( id, 8004000000) id) b       on a.id + 1 = b.id b.id null 

so question same: how generate least highest available number in entity framework 4, i.e., how rewrite sql query above in linq entities, or how view show in entity framework 4 diagram without editing xml file tosses changes on refresh?

edit: seems generate next available using linq:

// setup our id list var prod = dc.products.where(p => p.id > 0 && p.id < 1000)     .select(p => p.id).distinct();  // compare list against itself, offset 1.  "nulls" // represent "next highest number doesn't exist" var q = (from p1 in prod          p2 in prod.where(a => == p1 + 1).defaultifempty() // left join          p2 == 0                            // 0 null in case          select p1).min(); var r = q + 1;   // 1 higher current didn't exist, that's answer 

not quite sure why need complex calculations. however, if need unique database-wide number take @ rowversion type in mssql tables. give unique number changed every time row record updated. , unique whole db.