LINQ-以OR条件加入 [英] 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; }
         (...)
    }

我想创建一个简单的查询，在SQL中看起来像这样:

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.

我无法做到这一点.我找到了一种解决方案，其中的一个可以用union创建两个单独的联接查询，并且可以工作-但对数据库创建这种查询效率不高.

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.

推荐答案

LINQ中的联接始终是等联接.基本上，您需要多个from子句和where子句:

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 ...;

现在，在LINQ to Objects中可能有更有效的方法-但我希望基于SQL的LINQ提供程序生成具有足够执行计划的查询.它可能实际上并未在SQL中创建JOIN，但我希望它与您所显示的联接是相同的执行计划.

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.

这篇关于LINQ-以OR条件加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！