sql server ce - SQL Compact Edition Batch Update -

sql server ce - SQL Compact Edition Batch Update -

i using entity framework , need update multitude of entities on regular basis.

what need add together journaling number, based on several different tables. using sql server, can utilize stored procedure, inner , outer joins, grouping , row_number, heavy lifting me , efficient.

see procedure code follows:

create procedure [dbo].[sp_updatenumber] @refid int begin update tablewithnumber set nmr = other.nmr ( select min(c.nmr) nmr, z_id ( select row_number() on (order d.tickscreatedon, d.z_id, d.b_id) nmr, d.* ( select s.id s_id, z.id z_id, b.id b_id, s.tickscreatedon childtable b inner bring together tablewithnumber z on z.childtablerefa_id = b.id inner bring together parenttable s on z.parenttable_id = s.id s.ref_id = @refid union select s.id s_id, z.id z_id, b.id b_id, s.tickscreatedon childtable b inner bring together tablewithnumber z on z.childtablerefb_id = b.id inner bring together parenttable s on z.parenttable_id = s.id s.ref_id = @refid) d ) c grouping c.z_id) other other.z_id = id , ref_id = @refid end

due stored procedures not beingness available in sql ce, , farther restrictions on how can utilize grouping in sub selects, have calculate journaling number in code. utilize sqlquery on context retrieve minimum required info set, grouping , begin update per entity.

select zid (select s.id [sid], z.id [zid], b.id [bid], s.tickscreatedon childtable b inner bring together tablewithnumber z on z.childtablerefa_id = b.id inner bring together parenttable s on z.parenttable_id = s.id s.ref_id = @refid union select s.id [sid], z.id [zid], b.id [bid], s.tickscreatedon childtable b inner bring together tablewithnumber z on z.childtablerefb_id = b.id inner bring together parenttable s on z.parenttable_id = s.id s.ref_id = @refid) d order tickscreatedon, zid, bid var result = database.sqlquery<numberupdate>(query, new sqlceparameter("@refid", 1)).tolist();

the code on application looks this

var grouped = result .select((x,y) => new numberupdate { zid = x.zid, number = ++y }) .groupby(x => x.zid); foreach (var grouping in grouped) { database.executesqlcommand("update tablewithnumber set number = @nr id = @id", new sqlceparameter("@nr", group.min(x => x.number)), new sqlceparameter("@id", group.key)); }

i wondering if there faster or more efficient way update list of entities in sql ce calling executesqlcommand in foreach loop?

if business logic allows update info in several tables "row row" (not complex sql command), may seek "table-direct" approach. involves next steps.

create sqlceconnection object connection string pointing sdf file. create sqlcecommand object connection command type commandtype.tabledirect. create sqlceresultset options resultsetoptions.updatable , resultsetoptions.scrollable.

now able update table rows using seek, read , update methods. approach perchance fastest way possible. requires write much more code traditional ef approach.

sql sql-server-ce entity-framework-6