CODEWITHSUNDEEP
c#linqentity-framework-5
SeToY
SeToY

Reputation: 5895

Return one element from where-clause or the first one

I'm trying to query my database with Entity Framework 5 in the following manner:

userProfile = context.UserProfiles.SingleOrDefault(up => up.UserId == userId && up.IsActive);

Now this clearly throws an exception when more than one user profile is present (due to the SingleOrDefault).

How can I construct my query so I get the active userprofile from the current user (up.UserId == userId) or the first one when no userprofile is active?

I totally know how to do this in two queries, but I think that would be too much overhead and this should be possible with just one query:

userProfile = context.UserProfiles.Single(up => up.UserId == userId && up.IsActive); // throws exception when not exactly one

if (userProfile == null)
{
    userProfile = context.UserProfiles.FirstOrDefault(up => up.UserId == userId);
}

Edit:

I'm now using this as my parameter and works flawlessly with the IsActive property: context.UserProfiles.Where(up => up.UserId == userId).OrderByDescending(up => up.IsActive).FirstOrDefault();

Now when there is no IsActive == true row, I'd like to order them by their LastUsed date and select the one that's lately being used:

context.UserProfiles.Where(up => up.UserId == userId).OrderByDescending(up => up.IsActive).OrderByDescending(up => up.LastUsed).FirstOrDefault();

Sadly this always returns the latest one, even when there's a row containing IsActive == true

Upvotes: 2

Views: 149

Answers (2)

Eren Ersönmez
Eren Ersönmez

Reputation: 39085

Your second OrderByDescending ignores any ordering done by the previous one. You need to use ThenByDescending instead:

context.UserProfiles
    .Where(up => up.UserId == userId)
    .OrderByDescending(up => up.IsActive)
    .ThenByDescending(up => up.LastUsed)
    .FirstOrDefault();

Upvotes: 1

Marc Gravell
Marc Gravell

Reputation: 1063499

How about:

var userProfile = context.UserProfiles.Where(up => up.UserId == userId)
                       .OrderByDescending(up => up.IsActive).FirstOrDefault();

Upvotes: 2

Related Questions