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


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







All Articles