Cross context joins in .NET This is how I made a cross context join.

What do you think?

How would you do this better?

using (var context = ContextFactory.Create())
{
    var users =  await context
                         .Users.AsNoTracking()
                         .OrderBy(user =&gt; user.DisplayName)
                         .Where(user =&gt; user.AccountName.Contains(query))
                         .Select(user =&gt; new UserProfileDTO
                         {
                             Id = user.Id,
                             DisplayName = user.DisplayName,
                         })
                         .ToListAsync().ConfigureAwait(false);

    foreach (var user in users)
    {
        var entity = await context.UserInfos.FindAsync(user.Id).ConfigureAwait(false);
        if (entity != null)
            user.HasPicture = entity.HasPicture == true ? true : false;
    }

    return users;
}
You can get the results with a single query like this one:

var result = await
    (from u in context.Users
     join ui in context.UserInfos on u.Id equals ui.UserId
     where u.AccountName.Contains(query) &amp;&amp; ui.HasPicture
     select new UserProfileDTO
     {
         Id = u.Id,
         DisplayName = u.DisplayName,
     }
    ).ToListAsync().ConfigureAwait(false);


This would select all users with a picture in their user-info.

I assumed the UserInfo table has a foreign-key UserId that you can use for joining both tables.



This by the way


user.HasPicture = entity.HasPicture == true ? true : false;



is the same as:

user.HasPicture = entity.HasPicture;