c# - How to make left join, group by and select with the single query? -

assume have following data:

var workers = new[] {     new {  name = "john",  id = 1 },     new {  name = "greg",  id = 2 },      new {  name = "jack",  id = 3 },      new {  name = "josh",  id = 4 },     new {  name = "jill",  id = 5 },     new {  name = "jane",  id = 6 } };  var contracts = new[] {     new {  contractnumber="1",  workerid=1, contractdate = new datetime(2017,6,30) },     new {  contractnumber="2",  workerid=2, contractdate = new datetime(2017,7,10) },     new {  contractnumber="3",  workerid=2, contractdate = new datetime(2017,7,15) },     new {  contractnumber="4",  workerid=5, contractdate = new datetime(2017,7,20) },     new {  contractnumber="5",  workerid=1, contractdate = new datetime(2017,7,25) } }; 

what need select first worker has minimum quantity of contracts contract date greater or equals to:

var fromdate = new datetime(2017, 7, 1); 

excluding workers following id:

int[] exceptworkerids = new int[] {1, 4}; 

if several workers have similar minimum quantity of contracts select worker first name in alphabetical order.

i resolved task following way.

firstly, each worker left join contracts. if contract exists helper property contractexists = 1, if not 0.

var query =  w in workers.where(x => !exceptworkerids.contains(x.id)) join c in contracts.where(x => x.contractdate >= fromdate)      on w.id equals c.workerid workercontracts wc in workercontracts.defaultifempty() select new {workerid = w.id, workername = w.name, contractexists = wc == null ? 0: 1}; 

this query gives me following result:

secondly, group obtained results workerid, workername getting sum of contracts , order data sum , worker name:

var result =  (from q in query group q.contractexists new {q.workerid, q.workername} g orderby g.sum(), g.key.workername select new  {     workerid = g.key.workerid,      workername = g.key.workername,      workercontractscount = g.sum() }).tolist().take(1); 

take(1) gives me top 1 of resulted data:

the question: there way query or simpler or elegant manner did? if yes, boost productivity of query execution?

rather doing join (which multiplies data) followed group by use group join (what query using before from wc in workercontracts.defaultifempty()).

the other logic pretty same - workercontracts.count() gives desired quantity of contracts, apply desired order, take first , done:

var result =     (from w in workers.where(x => !exceptworkerids.contains(x.id))      join c in contracts.where(x => x.contractdate >= fromdate)          on w.id equals c.workerid workercontracts      let workercontractscount = workercontracts.count()      orderby workercontractscount, w.name      select new      {          workerid = w.id,          workername = w.name,          workercontractscount = workercontractscount      })     .firstordefault();