LINQ - join with OR condition

Question

I've got two entities, Users and Friendships which look like:

    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 simple query which in SQL would look like:

    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 friends of the user.

And I can't accomplish that. I've found solution where one creates two separate join queries with union and it works - but it's not efficient to create such query to db.

Answer 1

Joins in LINQ are always equijoins. Basically you need multiple 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 ...;

Now in LINQ to Objects there are probably more efficient ways of doing this - but I'd expect a SQL-based LINQ provider to generate a query which has a good enough execution plan. It may not actually create a JOIN in the SQL, but I'd expect it to be the same execution plan as the join you've shown.