How to join in LINQ by multiple fields (2 TABLES 1 PK to 2 FK ) c# linq

Question

I have 2 tables:

Customers
ConnectedCustomers

Connected customers table holds 2 foreign keys to Customers table and basically it looks like this:

What I'm wondering here is how can I return both customers from related table?

I've tried something like this (NOT ALLOWED):

query = from c in _context.Customers join cc in _context.ConnectedCustomers
         on c.customerId equals cc.customer1_id OR c.Id equals cc.customer2.id
        select c;

And this is not allowed... so I googled little bit and I've found people are ussually working with anonymous types in this cases, so I've tried something like this:

This also did not work because I included twice c.customerId and it says anonymous type cannot have multiple properties with the same name.

query =  from c in _context.Customers join cc in _context.ConnectedCustomers
           on new { c.customerId, c.customerId } equals new { cc.customer1_id, cc.customer2 }
        select c;

So I've removed one of c.customerId from anonymous type and it looked like this:

query =  from c in _context.Customers join cc in _context.ConnectedCustomers
           on new { c.customerId } equals new { cc.customer1_id, cc.customer2 }
        select c;

But than I've received error on join which said: The type of one of the expressions in the join clausule is incorrect...

Thanks guys !

Cheers

can you try this on `new { c.customerId } equals cc.customer1_id || cc.customer2` ? — OMANSAK, Jul 31 '19 at 08:19
@OMANSAK Operator || cannot be applied to type long and long ... hmm — Roxy'Pro, Jul 31 '19 at 08:23
maybe you use concant and distict like as in https://stackoverflow.com/questions/1264993/linq-left-join-on-multiple-or-conditions — OMANSAK, Jul 31 '19 at 08:53

score 0 · Answer 1 · answered Jul 31 '19 at 08:20

sorry not entirely clear what the desired result is but I believe you want only the customers from Customers table that exist in ConnectedCustomers? If not comment to let me know and I can update, but here's what that would look like using fluent syntax:

var myCustomers = _context.Customers.Select(c => _context.ConnectedCustomers.Any(cc => cc.customer1_id.Equals(c.customerId) || cc.customer2_id.Equals(c.customerId)).ToList();

Chrᴉz remembers Monica · Answer 2 · 2019-07-31T09:20:53.180

You can either join it in 2 seperate queries and union/concat them or you can join it the "old fashiend way" by using the where condition(cross join + where condition, beware of duplicates):

var query = from cust in _context.Customers 
            join custcon in _context.ConnectedCustomers
            where cust.customer_id == custcon.customer1_id
              or cust.customer_id == custcon.customer2_id
            select cust;

If you want to "return both customers from the relation table" you can simply join the customer 2 times and return a anonymous object (or a new class of your choice) containing both customers:

var query = from custcon in _context.ConnectedCustomers
            join cust1 in _context.Customers on custcon.customer1_id equals cust1.customer_id
            join cust2 in _context.Customers on custcon.customer2_id equals cust1.customer_id
            select new { Customer1 = cust1, Customer2 = cust2};

hugo · Answer 3 · 2019-07-31T09:34:09.077

From: Perform custom join operations:

However, the join clause cannot be used in the following cases:

When the join is predicated on an expression of inequality (a non-equijoin).

When the join is predicated on more than one expression of equality or inequality.

When you have to introduce a temporary range variable for the right side (inner) sequence before the join operation.

Based on the second point, your case seems to qualify as a non-equijoint.

So you can do a cross-joint with a where clause (here the condition is an expression, so it can be as complex as you like):

var query  = from c in _context.Customers
        from cc in _context.ConnectedCustomers
        where c.customerId == cc.customer1_id || c.customerId == cc.customer2_id
        select c.customerId;

Or simply do two equi-joins and combine them:

var q1 = from c in _context.Customers
         join cc in _context.ConnectedCustomers
         on c.customerId equals cc.customer1_id
         select c.customerId;

var q2 = from c in _context.Customers
         join cc in _context.ConnectedCustomers
         on c.customerId equals cc.customer2_id
         select c.customerId;

var query = q1.Concat(q2);

EDIT: Fixed variable names and remove Distinct() clause since it's not specified whether you need it.

How to join in LINQ by multiple fields (2 TABLES 1 PK to 2 FK ) c# linq

3 Answers3