将SQL内插到LINQ查询

Question

下面的SQL查询将如何转换为LINQ：

SELECT TOP 2 A.* FROM UserInterests AS UIa
INNER JOIN UserInterests AS UIb ON UIb.InterestId = UIa.InterestId
INNER JOIN AspNetUsers AS A ON A.Id = UIb.ApplicationUserId
WHERE UIa.ApplicationUserId = {userId}
AND NOT UIb.ApplicationUserId = {userId}
AND (
    A.Location LIKE {locationSubString + "%"} OR
    A.Location LIKE {neighbours[0] + "%"} OR
    A.Location LIKE {neighbours[1] + "%"} OR
    A.Location LIKE {neighbours[2] + "%"} OR
    A.Location LIKE {neighbours[3] + "%"} OR
    A.Location LIKE {neighbours[4] + "%"} OR
    A.Location LIKE {neighbours[5] + "%"} OR
    A.Location LIKE {neighbours[6] + "%"} OR
    A.Location LIKE {neighbours[7] + "%"}
)
ORDER BY NEWID()

我选择两个随机的用户谁在我附近，谁有相同的兴趣。上面的查询是用DbSet.FromSqlInterpolated执行的。userId-变量是我的id，locationSubString +邻居数组是geohashes。

我有以下几点，但我不知道如何与利益相匹配：

dbContext.Users.Where(u => (
    u.Location.StartsWith(locationSubString) ||
    u.Location.StartsWith(neighbours[0]) ||
    u.Location.StartsWith(neighbours[1]) ||
    u.Location.StartsWith(neighbours[2]) ||
    u.Location.StartsWith(neighbours[3]) ||
    u.Location.StartsWith(neighbours[4]) ||
    u.Location.StartsWith(neighbours[5]) ||
    u.Location.StartsWith(neighbours[6]) ||
    u.Location.StartsWith(neighbours[7])
) && u.Id != userId
).Include(u => u.Interests)

这能在一个查询中完成吗？还是我需要先询问自己的兴趣，然后将其与这样的列表进行比较(myInterests是一个列表)：

...
.Include(u => u.Interests)
.Where(u => u.Interests.Any(i => myInterests.Contains(i)))

Answer 1

下面是我想出的匹配代码：

var currentPersonId = 1;
var data = context.UserInterests
  .Join(context.UserInterests, x => x.InterestID, x => x.InterestID, (a, b) => new { CurrentInterest = a, MatchedInterest = b })
  .Where(x => x.CurrentInterest.UserID == currentPersonId && x.MatchedInterest.UserID != currentPersonId)
  .Select(x => x.MatchedInterest.User)
  .Distinct()
  .Where(x => x.Location.StartsWith("a"))
  .OrderBy(u => Guid.NewGuid())
  .Take(2)
  .ToArray();

您可以看到它在示例数据这里上工作。