CODEWITHSUNDEEP
c#linq
Roxy'Pro
Roxy'Pro

Reputation: 4444

How to get data from a Table if they exist in another Table C# LINQ

I have a two tables Customers, and ConnectedCustomers. That means customers can connect with each other.

My question is how can I get all Customers which are related to Chris (id = 3).

I should as a results get Bob and John..

I've tried something like this:

 query = _context.Customers.Where(c =>_context.ConnectedCustomers.Any(cc => cc.Connected_Customer1.Equals(3) || cc.Connected_Customer2.Equals(3)));

But this is not working it returns too many rows..

Expected result for id 3 is BOB AND JOHN because they are connected with id 3.

Thanks guys Cheers

Upvotes: 0

Views: 741

Answers (4)

ArslanIqbal
ArslanIqbal

Reputation: 629

The problem in your query is you are querying from Customer table. When you are querying through Customer, what it's doing is getting all the possible combinations of Fk with Connected_Customer1 and Connected_Customer2 which satisfies the condition. What you should be doing is query from ConnectedCustomers. Try this:

var result = _context.ConnectedCustomers
  .Where(x => x.Connected_Customer1.Equals(3) || x.Connected_Customer2.Equals(3))
  .Select(x=>x.Customers)
  .ToList();

Upvotes: 1

Slava Utesinov
Slava Utesinov

Reputation: 13488

var answer = (from cc in _context.ConnectedCustomers
              join c1 in _context.Customers on cc.Connected_Customer1 equals c1.id 
              join c2 in _context.Customers on cc.Connected_Customer2 equals c2.id 
              where c1.id == 3 || c2.id == 3
              select c1.id == 3 ? c2 : c1
              ).ToList();

Upvotes: 1

lollmbaowtfidgafgtfoohwtbs
lollmbaowtfidgafgtfoohwtbs

Reputation: 181

_context.Customers.Where(c => c.Id == _context.ConnectedCustomers.Where(cc => cc.Connected_Customer1 == 3).Select(cc => cc.Connected_Customer2))

Try that.

Upvotes: 0

Guilhem Prev
Guilhem Prev

Reputation: 979

You never use the id of the user to check (c), so you have to add a condition like this :

var query = _context.Customers.Where(customer => _context.ConnectedCustomers.Any(cc =>
    cc.Connected_Customer1.Equals(customer.Id) && cc.Connected_Customer2.Equals(3) ||
    cc.Connected_Customer1.Equals(3) && cc.Connected_Customer2.Equals(customer.Id))).ToList();

Upvotes: 0

Related Questions