c# - Select multiple child columns with the same filter -

i have linq lambda expression generates abnormally complex sql select database. somehow possibility simplify it?

var devices = db.devices                         .where(a => a.active == true)                         .select(a => new devicetodisplay                         {                             id = a.id,                             serialnumber = a.serialnumber,                                                            deviceregion = a.deviceregion,                             activeip = a.ipaddresses.where(b => b.active == true).select(b => b.ipaddress1).firstordefault(),                             wip = a.ipaddresses.where(b => b.active == true).select(b => b.w_ip).firstordefault(),                             sip = a.ipaddresses.where(b => b.active == true).select(b => b.s_ip).firstordefault(),                             model = a.spdatas.where(c => c.model != "").orderbydescending(c => c.collectiondate).select(c => c.model).firstordefault(),                             firmware = a.spdatas.where(c => c.model != "").orderbydescending(c => c.collectiondate).select(c => c.firmware).firstordefault(),                                                            lastmpteamactivity = a.activitylogs.orderbydescending(c => c.updateddate).select(c => c.updateddate).firstordefault(),                                                            country = a.mppinformations.select(c => c.country).firstordefault()                                                         }); 

for start, linq query looks complicated. imagine how implement writing sql query example.

a suggestion: writing things like:

 a.ipaddresses.where(b => b.active == true). 

and

  a.spdatas.where(c => c.model != "").orderbydescending(c => c.collectiondate). 

in multiple places.

instead create anonymous type. example,

var foo = x in sb.devices.where(a=> a.active)           select new { id = x.id,                         ipaddress = a.ipaddresses.where(b => b.active), ... } 

you can use foo create devices object.