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

score 4 · Answer 1 · edited Jan 21 '19 at 12:18

4

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

edited Jan 21 '19 at 12:18

Tim Schmelter

450,073
74
686
939

answered Jan 21 '19 at 11:49

Fabjan

13,506
4
25
52

but am not able to use selection.Any – Chandan Y S Jan 21 '19 at 11:55
This is probably safe enough if `Email` and `Username` are both distinct (though it retrieves unused columns), but in more general cases where there could be many matching cases it could be much slower than the two queries of the original. – Jon Hanna Jan 21 '19 at 12:18
@Fabjan I forgot to use .ToList() – Chandan Y S Jan 21 '19 at 12:25

score 1 · Answer 2 · answered Jan 21 '19 at 12:17

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

score 1 · Answer 3 · answered Jan 21 '19 at 21:06

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.

If you will be using this more often, consider creating a LINQ extension method with a `Func, TResult> resultSelector` as parameter. This way you hide the quirky GroupBy and First — Harald Coppoolse, Jan 22 '19 at 14:29

score 0 · Answer 4 · answered Jan 21 '19 at 14:40

0

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

answered Jan 21 '19 at 14:40

Philip Atz

886
1
10
26

The seems to be about LINQ to a SQL backend (because of _Context) so maybe Aggregate is not supported. – Gert Arnold Jan 22 '19 at 09:18

Using two Linq query in a single method

4 Answers4