Clever Geek Handbook

Linq quick query with connection and counter

For me this SQL query works quickly if executed from TOAD in an Oracle database where the Job table contains over 2 million records.

select * from Technicians  A
left join  
  (select TechnicianCode,count(*)JobCount from Jobs 
   where TxnCode in ('Jc','Jcd') group by TechnicianCode) B
on B.TechnicianCode =A.TechnicianCode

There is no way to translate it to linq in an efficient way. Can anyone help with this?

+3


source to share


4 answers


You can try this

var txnDetails =  from job in jobs
                  where job.TxnCode == "Jc" || job.TxnCode == "Jcd"
                  group job by job.TechnicianCode into g
                  select new { TechnicianCode = g.Key, count = g.Count() };


Then you can just make your normal selection with the join

 from technician in Technicians
 join txn in txnDetails on technician.TechnicianCode equals 
   txn.TechnicianCode into tg
 from t in tg.DefaultIfEmpty()
 select new {Count = t==null? 0: t.count, ...}
+2


source


To make a left join, change @SAJ like so:



var result =  from technician in Technicians
   join txn in txnDetails on technician.TechnicianCode equals 
    txn.TechnicianCode
   into j 
   from lj in j.DefaultIfEmpty()
 select lj;
+1


source


Are you looking for this?

var result = from technician in Technicians
                join job in Jobs on technician.TechnicianCode equals job.TechnicianCode into A
                from job in A.DefaultIfEmpty()
                where job.TxnCode == "Jc" || job.TxnCode == "Jcd"
                select new 
                {
                    Tech = technician,
                    Job = job
                };

To get an invoice, you can simply use:

var count = result.Where(r => r.Job != null).Count();
0


source


Try the code:

var result= (from  p in Technicians            
             join q in Jobs.where(c=>c.TxnCode=="JC" || c.TxnCode==c."Jcd").GroupBy(c=>c.TechnicianCode)
             .Select(m => new { jobcount = m.Count(), TechnicianCode = m.FirstOrDefault().TechnicianCode,  TxnCode=m.FirstOrDefault().TxnCode}).ToList()             
             on p.TechnicianCode equals q.TechnicianCode into jj from kk in jj.DefaultIfEmpty() 
             select new {    
                          jobcount=kk.jobcount,  
                          TxnCode=kk.TxnCode, 
                          TechnicianCode=p.TechnicianCode 
                        }).ToList();
-1


source






More articles:


All Articles