What is an efficient Entity Framework query to check if users are friends?

Question

There is a table called UserFriends that holds records for users' friendships. For each friendship, there is just one record,

User1ID  User2ID IsConfirmed
1        2       true

which is equal in terms of business logic to

User1ID  User2ID IsConfirmed
2        1       true

but both can't happen for one pair.

What is the most efficient (yet readable and not involving plain SQL) Entity Framework query to determine if user A is a friend of user B, considering we don't know which of them is in first or second column?

My attempt is plain and obvious:

public bool AreFriends (int user1Id, int user2Id)
{
    return MyObjectContext.UserFriends
        .Any (uf => uf.IsConfirmed && (
                    (uf.UserID == user1Id && uf.FriendUserID == user2Id)
                    || (uf.UserID == user2Id && uf.FriendUserID == user1Id)
        ));
}

Is there a better way than || here?

Answer 1

I think if the User1ID and User2ID columns both are primary key columns this query cause an index seek and is so efficient. Tuning of a query when it is critical, without analysing the execution plan, is inefficient. For important queries, I suggest you use SQL Server (or any DBMS) to write and analyse your queries and then convert these queries to LINQ queries. It is not hard.