C# Linq statement to join two tables and multiple columns

Question

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?'

"on icp.PartPortA && icp.PartPortB equals ica.PortGid" is not correct, it's not a valid boolean expression, since icp.PartPortA is a Guid, and not a boolean. Are you trying to join on both columns? Then try this instead: "on icp.PartPortA equals ica.PortGid && icp.PartPortB equals ica.PortGid" — HaukurHaf
– HaukurHaf, Commented Aug 23, 2016 at 19:16
Probably because one is a nullable guid and the other is not ... (Use .Value nullable guid) — HaukurHaf
– HaukurHaf, Commented Aug 23, 2016 at 19:33

Gert Arnold · Accepted Answer · 2016-08-23 19:29:07Z

2

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();

answered Aug 23, 2016 at 19:29

Gert Arnold

110k36 gold badges219 silver badges319 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

C# Linq statement to join two tables and multiple columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related