Doing 2 left matches on one table with one call
I have the following tables:
Users
-
userId
,userFirstName
,userLastName
.
holdBilling:
-
bEntityID
,CarrierOID
,PayerOID
,holdTYPE
,createUserID
.
carrier:
-
CarrierOID
,carrierName
.
payer:
-
PayerOID
,payerName
.
I want the code to be saved in a new object
holdBilling => new { FirstName, LastName, CarrierName, PayerName }
One of these objects has either a payer or a carrier value (cannot have both). Basically I want to make 2 left joins in one table with one call. This will be a SQL query that will work for me.
SELECT TOP 1000 [ID]
,[bEntityID]
,c.carrierID
,c.carrierName
,p.payerID
,p.payerName
,[holdType] ( this is "C" for carrier and "P" for payer )
FROM .[dbo].[holdBilling] hb
left join dbo.payer p on hb.payerID = p.payerID
left join dbo.carrier c on hb.carrierID = c.carrierID
where [bEntityID] = 378
The workaround I found is getting a list of all media
var listC = (from hold in holdBilling
join u in Users on hold.createUserID equals u.userID
join c in carrier.DefaultIfEmpty() on hold.carrierID equals c.carrierID
select new
{
Elem = hold,
FName = u.userFirstName,
LName = u.userLastName,
Carrier = c.carrierName,
Payer = ""
}).ToList();
and one for all payers
select new
{
Elem = hold,
FName = u.userFirstName,
LName = u.userLastName,
Carrier = "",
Payer = p.payerName
}).ToList();
and merging the two, I'm sure there should be a solution for both in one query.
+3
source to share
2 answers
Something like this could be:
var listC = (
from hb in holdBilling
from p in payer.Where(a=>a.payerID==hb.payerID).DefaultIfEmpty()
from c in carrier.Where(a=>a.carrierID=hb.carrierID).DefaultIfEmpty()
where hb.bEntityID==378
select new
{
hb.bEntityID,
c.carrierID,
c.carrierName,
p.payerID,
p.payerName,
holdType=(payer==null?"P":"C")
}
).Take(1000)
.ToList();
+1
source to share
You need to use DefaultIfEmpty to make a left join
var listC = (from hold in holdBilling
from u in Users.Where(x => hold.createUserID == x.userID).DefaultIfEmpty()
from c in carrier.Where(x => hold.carrierID == x.carrierID).DefaultIfEmpty()
select new
{
Elem = hold,
FName = u.userFirstName,
LName = u.userLastName,
Carrier = c.carrierName,
Payer = ""
}).ToList();
+1
source to share