c# - How to Create the Left Join by using the group by data -


i have 3 table 1 "allowance " ,"balance" , "timeoffrequests" in these 3 table common columns employeeid , timeofftypeid, need requested hours of 1 leave type grouping thier timeofftypeid , employeeid table "timeoffrequests" , , got "timeoffhours". wrote code like

var query = (from tr in timeoffrequests              tr.employeeid == 9              group tr new { tr.employeeid, tr.timeofftypeid } res              select new              {                  employeeid = res.key.employeeid,                  timeofftypeid = res.key.timeofftypeid,                  totalhours = res.sum(x => x.timeoffhours)              }).asenumerable();

enter image description here

now need join these results first table , have employees, , timeofftypes userallowance , corresponding timeoffhours grouped table. getting left joined query wrote below.

var requestresult = (from ua in userallowances                      join ub in userbalances on ua.employeeid equals ub.employeeid                      ua.timeofftypeid == ub.timeofftypeid && ua.employeeid == 9                       && ua.timeofftype.isdeductabletype == true      // leftjoin                       join rest in query on ua.employeeid equals rest.employeeid penidngrequst                      penreq in penidngrequst.defaultifempty()                      penreq.timeofftypeid == ua.timeofftypeid                       select new employeetimeoffbalancemodel                      {                          timeofftypeid = ua.timeofftypeid != null ? ua.timeofftypeid : 0,                          yearlyallowancehrs = (ua.yearlyallowancehrs != null) ? ua.yearlyallowancehrs : 0,                          balancehours = ub.balancehrs != null ? ub.balancehrs : 0,                          pendinghours = (decimal)((penreq != null) ? (penreq.totalhours) : 0),                          employeeid = ua != null ? ua.employeeid : 0,                        }).tolist().distinct();

it giving timeofftype containing in grouped data,even though wrote leftjoin query using "into" , defaultifempty() keywords. results becomes like:

enter image description here

and using "linqpad" editor found applying cross or outer join instead of "left join" reason.

if remove line of code " penreq.timeofftypeid == ua.timeofftypeid" showing timeofftypes cross join repeatation like

with repeated timeoffhours

how can achieve left join tables grouped data , showing null values if timeofftypes didn't having request?

you might want move clause the on equals clause shown below

join rest in query on new { ua.employeeid, ua.timeofftypeid } equals new { rest.employeeid, rest.timeofftypeid } penidngrequst penreq in penidngrequst.defaultifempty()

-

Comments

Post a Comment

Popular posts from this blog

php - regexp cyrillic filename not matches -

i take list of files php glob(."*.pdf") . files have cyrillic names. example: ООО «Рога и копыта» then load company names database , try find them in file names: preg_match("/$firm_name/ui", $file_name, $match); i have problem: firms if $firm_name visually similar $file_name , preg_match says there no match. mb_detect_encoding both variables return utf-8. there problem in file name, or maybe there encoding or something, can not understand, ask help. upd: $file_name='ООО «Рога и копыта»'; preg_match("/... .Рога и копыта./ui", $file_name, $match); as result, ОО «Рога и копыта» , 1 О missing
Read more

c# - OpenXML hanging while writing elements -

Image
i have program pulls data database, caches file , exports data multiple formats (excel, excel 2003, csv). i'm using openxml sdk 2.0 excel work. these export processes run in parallel (using parallel.foreach ), , amount of data can pretty large - e.g. csvs 800mb. during these larger exports, i've noticed writing of xml documents hang. instance, if have 8 exporting in parallel, @ point "pause". hang around same point: //this.writer openxmlwriter created worksheetpart. this.writer.writeelement(new cell() { cellvalue = new cellvalue(value), datatype = cellvalues.string }); when happens, pause debugger (vs2013 in case) , notice threads blocking around same portion of code - bit deeper in openxml sdk - stem call openxmlwriter.writeelement . i dug through source using justdecompile didn't find answers. appears there intermediary stream in use writing isolated storage , is, reason, blocking. underlying stream each of these filestream . here screenshot ...
Read more

python - Scapy Dot11ReassoReq -

what scapy's dot11reassoreq, , do? sound ideal purposes: want make tool (among other things) searches hidden aps. code far: def tickle(*args,**kwargs): packs1=[] packs2=[] client in appinstance.sframe.interior.clients.copy(): pkt=dot11(addr1="ff:ff:ff:ff:ff:ff",addr2=client,addr3=client)/\ dot11deauth() packs1.append(pkt) client in appinstance.sframe.interior.clients.copy(): pkt=dot11(addr1="ff:ff:ff:ff:ff:ff",addr2=client,addr3=client)/\ dot11reassoreq(current_ap=client) packs2.append(pkt) packs1*=32 shuffle(packs1) packs2*=32 shuffle(packs2) packs=packs1+packs2 appinstance.stopprogressbar() appinstance.startprogressbardet(len(packs), "trying expose hidden ap's") pkt in packs: send(pkt,verbose=0) appinstance.progressbar.step() appinstance.stopprogressbar() however, wireshark re...
Read more