CODEWITHSUNDEEP
linqentity-framework
Nevararn
Nevararn

Reputation: 71

LINQ - join with OR condition

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.

Upvotes: 7

Views: 3595

Answers (2)

Magnus
Magnus

Reputation: 47036

Simply 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};

Upvotes: 2

Jon Skeet
Jon Skeet

Reputation: 1504062

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.

Upvotes: 6

Related Questions