Interpolated SQL to LINQ-query-CodePudding

How would the following SQL-query translate to 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()

I am selecting two random users who are near me and who have the same interests. The query above is executed with DbSet.FromSqlInterpolated. The userId-variable is my id and the locationSubString neighbours-array are geohashes.

I have the following, but I am not sure how I would match the interests:

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)

Can this be done in one query? Or do I need to query my own interests first and then compare it with that list like so (myInterests is a list):

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

CodePudding user response：

I have come up with the following:

await dbContext.Users
    .Where(u => 
        u.Location != null && (
            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)
    .Where(u => u.Interests
        .Any(i => applicationUser.Interests
            .Select(i => i.InterestId)
            .Contains(i.InterestId)
        )
    )
    .OrderBy(u => Guid.NewGuid())
    .Take(2)

I haven't found a way to get all the info in one query, so I looked up the user's interests before executing this query and then check all.

Not necessarily an answer to the question, but I also made Location a non-clustered index, to speed up the query.

CodePudding user response：

Here is the matching code I came up with:

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();

You can see it work on sample data here.