Inefficient 'ANY' LINQ clause
- by Focus
I have a query that pulls back a user's "feed" which is essentially all of their activity. If the user is logged in the query will be filtered so that the feed not only includes all of the specified user's data, but also any of their friends.
The database structure includes an Actions table that holds the user that created the action and a UserFriends table which holds any pairing of friends using a FrienderId and FriendeeId column which map to UserIds.
I have set up my LINQ query and it works fine to pull back the data I want, however, I noticed that the query gets turned into X number of CASE clauses in profiler where X is the number of total Actions in the database. This will obviously be horrible when the database has a user base larger than just me and 3 test users.
Here's the SQL query I'm trying to achieve:
select * from [Action] a
where a.UserId = 'GUID'
OR a.UserId in
(SELECT FriendeeId from UserFriends uf where uf.FrienderId = 'GUID')
OR a.UserId in
(SELECT FrienderId from UserFriends uf where uf.FriendeeId = 'GUID')
This is what I currently have as my LINQ query.
feed = feed.Where(o => o.User.UserKey == user.UserKey
|| db.Users.Any(u => u.UserFriends.Any(ufr => ufr.Friender.UserKey ==
user.UserKey && ufr.isApproved)
|| db.Users.Any(u2 => u2.UserFriends.Any(ufr => ufr.Friendee.UserKey ==
user.UserKey && ufr.isApproved)
)));
This query creates this:
http://pastebin.com/UQhT90wh
That shows up X times in the profile trace, once for each Action in the table. What am I doing wrong? Is there any way to clean this up?
