DeeTee DeeTee - 3 months ago 35
C# Question

C# Linq statement to join two tables and multiple columns

I have two tables; EndToEnd and PartPort. I'd like to get the PartPortA and PartportB data from the same row in EndToEnd, and query Partport with them and get their corresponding PartGid from Partport which could be on any row in the Partport table. So far, I'm able to do this, but I have to do two different LINQ calls but I'd like to reduce it down to one. Here is my code:

// this demonstrates how to join two tables, however only works for one AssetportGid at a time
var part_portGid_a_results = (from icp in entities.EndToEnd
where icp.IntertPortGidA != null &&
icp.IntertPortGidB != null
join ica in entities.PartPort
on icp.PartPortA equals ica.PortGid
select new { icp.PartPortA, ica.PartGid, }).ToList();

var part_portGid_b_results = (from icp in entities.EndToEnd
where icp.IntertPortGidA != null &&
icp.IntertPortGidB != null
join ica in entities.PartPort
on icp.PartPortB equals ica.PortGid
select new { icp.PartPortA, ica.PartGid, }).ToList();



return Json(part_portGid_a_results, JsonRequestBehavior.AllowGet);


What i'd like to do, and I have already tried but got an error is this:

var part_portGid_a_results = (from icp in entities.EndToEnd
where icp.IntertPortGidA != null &&
icp.IntertPortGidB != null
join ica in entities.PartPort
on icp.PartPortA && icp.PartPortB equals ica.PortGid
select new { icp.PartPortA, ica.PartGid, }).ToList();


The error i get is:

Guid? EndToEnd.PartPortB

Error:
Operator '&&' cannot be applied to operands of type 'System.Guid' and 'System.Guid?'

Answer

You don't have to use join. If you want to join with a "complex" comparison, just make a Cartesian product (from ... from) and link the rows by a where clause

var part_portGid_results = (from icp in entities.EndToEnd
                            where icp.IntertPortGidA != null &&
                            icp.IntertPortGidB != null
                            from ica in entities.PartPort
                            where icp.PartPortA == ica.PortGid
                               || icp.PartPortB == ica.PortGid
                            select new { icp.PartPortA, ica.PartGid, }).ToList();