LINQ - join with condition OR

Question

I have two objects: Users and Friends, which look like this:

public class User { public int UserId { get; set; } (...) } public class Friendship { public int SenderId { get; set; } public int ReceiverId { get; set; } (...) } 

And I would like to create a simple query that in SQL would look like this:

  SELECT * FROM Users as U INNER JOIN Friendships as F ON U.UserId = F.ReceiverId OR U.UserId = F.SenderId Where U.Nick != VARIABLE 

In other words, I would like to select all the friends of the user.

And I can’t do it. I found a solution where you create two separate join requests with a join and it works, but it is inefficient to create such a request for db.

Answer 1

Connections in LINQ are always equal. Basically you need some from clauses and a where clause:

 var query = from u in db.Users where u.Nick != variable from f in db.Friendships where u.UserId == f.ReceiveId || u.UserId == f.SenderId select ...; 

LINQ to Objects now has probably more efficient ways to do this, but I expect the SQL-based LINQ provider to generate a query that has a reasonably good execution plan. It actually cannot create a JOIN in SQL, but I expect it to be the same execution plan as the connection you specified.

Answer 2

Just write:

 from U in db.Users from F in Friendships.Where(x => U.UserId == F.ReceiverId || U.UserId == F.SenderId) where U.Nick != VARIABLE select new {u, f};