c# - Linq to entities query optimizing -

c# - Linq to entities query optimizing -

i have 3 tables in db i'm working with:

theme [theme_id] themeworkplace [theme_id, workplace_id, themeworkplace_id] usertheme [user_id, theme_id, usertheme_id, usertheme_accesstype]

i need alter usertheme_accesstype usertheme.theme_id in current workplace themeworkplace.workplace_id = 2 , current user user_id = 1. if theme no row in usertheme such user , such theme - need create it.

i wrote such code, works long time:

var themelist = (from t in m_entities.theme (from tw in m_entities.themeworkplace tw.workplace.workplace_id == 2 select tw.theme.theme_id).contains(t.theme_id) select t) .tolist(); foreach (theme theme in themelist) { var oldusertheme = getbyusertheme(user/*user given*/, theme); if (oldusertheme == null) { /* create new user theme params, need*/ this.add(newusertheme, true); } else { /* here - changing found row */ oldusertheme.usertheme_accesstype = 2; } }

i understand code accesses database many times. want find way rid of:

var oldusertheme = getbyusertheme(user/*user given*/, theme);

in every foreach iteration. please help me?

adding code of getbyusertheme():

private usertheme getbyusertheme(user user, theme theme) { homecoming m_entities.usertheme.firstordefault(ut => ut.user.user_id == user.user_id && ut.theme.theme_id == theme.theme_id); }

first: changes entities have done in code pushed database in 1 batch command when phone call context.savechanges. have 1 request database select , 1 request update.

but in batch command many sql queries cause ef generate sql updates entities 1 1 (not in one).

if want update many records in database, should utilize sql script (call stored procedure or execute sqlquery) against using entityframework.

c# optimization linq-to-entities