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

sql - Select Query has unexpected multiple records (MS Access) -

i may tired, can't seem figure out happening. have 2 queries. query 1 has 10 fields. query 2 has 5 fields. fields 1 through 3 same in both queries. trying write select query , simple add field 5 query2 query1 result should query1 additional field (query2.field5). i join first 3 fields both queries , choose select records in query1 , in query2 match. don't know sql, use query designer. expect same number of records have query 1 instead have 3 times or more. can tell me doing wrong. tried reversing joins still seem same records. thanks in advance. here query: the fields in common divisionname , supplierid = vendorid , commodityname . select [part revenue exposed query p1].divisionname, [part revenue exposed query p1].commodityname, [part revenue exposed query p1].suppliername, [part revenue exposed query p1].partnumber, [part revenue exposed query p1].partdescription, [part revenue exposed query p1].bucode, ...
Read more