Using two Linq query in a single method

Question

As shown in the below code, the API will hit the database two times to perform two Linq Query. Can't I perform the action which I shown below by hitting the database only once?

var IsMailIdAlreadyExist = _Context.UserProfile.Any(e => e.Email == myModelUserProfile.Email);

var IsUserNameAlreadyExist = _Context.UserProfile.Any(x => x.Username == myModelUserProfile.Username);

Answer 1

In order to make one request to database you could first filter for only relevant values and then check again for specific values in the query result:

var selection = _Context.UserProfile
    .Where(e => e.Email == myModelUserProfile.Email || e.Username == myModelUserProfile.Username)
    .ToList();

var IsMailIdAlreadyExist = selection.Any(x => x.Email == myModelUserProfile.Email);
var IsUserNameAlreadyExist = selection.Any(x => x.Username == myModelUserProfile.Username);

The .ToList() call here will execute the query on database once and return relevant values

Answer 2

Start with

var matches = _Context
  .UserProfile
  .Where(e => e.Email == myModelUserProfile.Email)
  .Select(e => false)
  .Take(1)
  .Concat(
    _Context
      .UserProfile
      .Where(x => x.Username == myModelUserProfile.Username)
      .Select(e => true)
      .Take(1)
  ).ToList();

This gets enough information to distinguish between the four possibilities (no match, email match, username match, both match) with a single query that doesn't return more than two rows at most, and doesn't retrieve unused information. Hence about as small as such a query can be.

With this done:

bool isMailIdAlreadyExist = matches.Any(m => !m);
bool isUserNameAlreadyExist = matches.LastOrDefault();

Answer 3

You can do it all in one line, using ValueTuple and LINQ's .Aggregate() method:

(IsMailIdAlreadyExist, IsUserNameAlreadyExist) = _context.UserProfile.Aggregate((Email:false, Username:false), (n, o) => (n.Email || (o.Email == myModelUserProfile.Email ? true : false), n.Username || (o.Username == myModelUserProfile.Username ? true : false)));

Answer 4

It's possible with a little hack, which is grouping by a constant:

var presenceData = _Context.UserProfile.GroupBy(x => 0)
    .Select(g => new
    {
        IsMailIdAlreadyExist = g.Any(x => x.Email == myModelUserProfile.Email),
        IsUserNameAlreadyExist = g.Any(x => x.Username == myModelUserProfile.Username),
    }).First();

The grouping gives you access to 1 group containing all UserProfiles that you can access as often as you want in one query.

Not that I would recommend it just like that. The code is not self-explanatory and to me it seems a premature optimization.