Best way to filter query by navigation property(child objects) in LINQ to SQL

Question

I have two options to filter my query:

var users = query.Select(x => x.User).FindUsers(searchString);
query = query.Where(x => users.Contains(x.User));

or

var userIds = query.Select(x => x.User).FindUsers(searchString).Select(x => x.Id);
query = query.Where(x => userIds.Contains(x.UserId));

Description of FindUsers extension:

static IQueryable<User> FindUsers(this IQueryable<User> query, string searchString);

So what I will have in SQL finally? Which of these two requests are better for perfomance?
If someone has another suggestion write it in answer please.

Thanks in advance!

Answer 1

Both query are similar in EF v6 or higher; Contains condition will be translated to Sql EXISTS

Take a loot to the code below :

 using (var dbContext = new DbContextTest("DatabaseConnectionString"))
  {
    var users = dbContext.Users.Select(u => u).Where(x => x.UserId > 0);
    var query = dbContext.Users.Where(x => users.Contains(x));
    Console.WriteLine(query.ToList());
  }

  using (var dbContext = new DbContextTest("DatabaseConnectionString"))
  {
    var ids = dbContext.Users.Select(u => u.UserId).Where(x => x > 0);
    var query = dbContext.Users.Where(x => ids.Contains(x.UserId));
    Console.WriteLine(query.ToList());
  }

The output Sql queries are excatly the same (you can use profiler or EF logger to see that). One thing maybe is important, selecting only the Id's will be more agile for materialisation and cache.

Tip: If you add ToList() in the Ids query dbContext.Users.Select(u => u.UserId).Where(x => x > 0).ToList(); then this fix will imporve your result performance. The next select query will be translated with SQL "IN" instead of of "EXISTS"! Take a look to this link Difference between EXISTS and IN in SQL? and you can decide what is better for you.

Note: ToList() will materialize the Id's, that means you will work with another interface then IQueryable!