c# - Fill fields using value from another subquery in a LINQ -

i have following linq, can use linq values ispremiumuser , islawfirm on go or there other way this?

what need values of ispremiumuser , islawfirm based on supplier id. dont want add foreach work, can use linq these values

var supplierlistquery =      (from sup in db.ppsuppliers      select new searchresultsupplierviewmodel      {          supplierid = sup.ppsupplierid,          supplierlocation = sup.locationsfordisplay,          createddate = (datetime)sup.createddate,          ppmemberid = sup.ppmemberid,          ispremiumuser =              (from u in db.ppsubscriptions               join s in db.ppsubscriptionplans on u.subscriptionplanid equals s.subscriptionplanid               u.ppmemberid == sup.ppmemberid && u.subscriptionenddate >= system.datetime.now               orderby u.subscriptionstartdate descending               select s).firstordefault().ispremium,          islawfirm =               (from l in db.ppsuppliersuppliertypes                join f in db.ppsuppliertypes on l.ppsuppliertypeid equals f.ppsuppliertypeid               l.ppsupplierid == sup.ppsupplierid && f.ppsuppliertypeid == 1               select l).any()      }); 

i put profiler see sql ef generating make decision: if query seems veeeery complicated , generate performance issues, try other things:

• use "let" keyword

• try load subqueries data in intermediate data structures simmilar hashtable has on key ppsuppliers id , on value, whatever data need distribute, single foreach loop on suppliers peek these intermediate structures in o ( 1) additional informations need add.

• use stored procedure :)