c# - Poorly performing query needs rewriting -

c# - Poorly performing query needs rewriting -

i have piece of code that's performing badly, , need rewite introduce proper clause before starting .tolist however, that's i'm getting stuck.

currently code looks lke (roughly, i've taken of search criteria out create easier display)

var widgets = b in _caspentities.widgets.include("widgetregionlogs") .include("widgetstatuslogs").include("widgetvoltagetests") select b; ienumerable<widget> results = widgets.tolist(); if (comboboxregion.selectedvalue.tostring() != "0") { results = b in results b.currentregionlog != null && b.currentregionlog.regionid == int.parse(comboboxregion.selectedvalue.tostring()) select b; } if (comboboxstatus.selectedvalue != null) { results = b in results b.currentstatuslog != null && b.currentstatuslog.statusid == comboboxstatus.selectedvalue.tostring() select b; } if (txtcode.text.trim().length > 0) { results = b in results b.codenumber == txtcode.text.trim() select b; } datagridviewwidget.datasource = results.tolist();

i can write sql enough, model simple, have widget has regionlog , statuslog, both of store history. current part , status retrieved grouping widgetid , selecting recent date updated (and going off part , status tables actual value).

so, need translate linq, honest don't have clue ken , willing learn. in head, think need add together improve clauses, , widget.tolist after have applied clauses. i'm struggling currentregionlog , currentstatuslog concepts not populated until run ienumerable.

if can give pointers, i'd grateful, thanks

edit - added

public batteryregionlog currentregionlog { { homecoming _currentregionlog; } } private batteryregionlog _currentregionlog { { if (this.batteryregionlogs.count > 0) { batteryregionlog log = this.batteryregionlogs.orderbydescending(item => item.lastmodifieddate).first(); homecoming log; } else { homecoming null; } } }

you can compose query this:

if (comboboxregion.selectedvalue.tostring() != "0") { var id = int.parse(comboboxregion.selectedvalue.tostring() widgets = b in widgets allow currentregionlog = b.batteryregionlogs .orderbydescending(item => item.lastmodifieddate) .firstordefault() currentregionlog.regionid == id) select b; } ... // same other criteria. datagridviewwidget.datasource = widgets.tolist();

the whole query not executed before tolist(). translated sql don't need null check b.currentregionlog != null. sql evaluate b.currentregionlog.regionid == id fine when there no currentregionlog.

edit

since currentregionlog calculated property of widget class cannot translated sql. made effort incorporate code of calculated property query in way basic navigation property used, ef can translate sql again.

c# linq-to-entities