以下 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-variable 是我的 id，locationSubString neighbours-array 是 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)))
uj5u.com熱心網友回復：
這是我想出的匹配代碼：
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();
您可以在此處看到它適用于示例資料。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/463234.html
下一篇：使用LINQ創建特定資料集