How to specify an LINQ query using an enum in the DefaultIfEmpty cause?

> If I specify ".DefaultIfEmpty((int)0)" 

Yes, because you're working with a queryable list of enum values, and if you specify (int)0 as the default value, you're trying to tell .NET "You're trying to assign the right Functionality value, so if the resulting list is empty, assign the integer 0" and .NET is telling you, "Umm... I can't do that because integer 0 isn't a Functionality value."

The way you have it with   .DefaultIfEmpty(Functionality.NONE) should work fine, so I'm not sure why you'd be assigning an integer? However, if you want to look up the Functionality value corresponding to value 0, then just swap out "int" for "Functionality":

.DefaultIfEmpty((Functionality)0)

By the way, I threw together a simple reproducible code example that hopefully replicated what you have, and it works for me:

    internal class EEQuestion_29248353
    {
        public EEQuestion_29248353()
        {
            var mContext = new MContext();
            var userID = 0;
            var UserFunctionality = mContext.Permissions
                             .Where(perm => perm.UserID.Equals(userID))
                             .Select(perm => perm.FunctionalityID)
                             .DefaultIfEmpty(Functionality.NONE)
                             .Max();
        }
    }


    internal class MContext
    {
        public List<Permission> Permissions = new List<Permission>() { };
    }


    internal class Permission
    {
        public enum Functionality { NONE, SOME, ALL }
        public int UserID = 0;
        public Functionality FunctionalityID;
    }

Select allOpen in new window

When I use ".DefaultIfEmpty(Functionality.NONE)" I get the following error during execution:

An exception of type 'System.InvalidOperationException' occurred in Microsoft.EntityFrameworkCore.dll but was not handled in user code

The LINQ expression 'DbSet<Permission>()

    .Where(perm => perm.UserID.Equals(__userID_0))

    .Select(perm => perm.FunctionalityID)

    .DefaultIfEmpty(NONE)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

. 

Ah, ok - "DbSet" - if this is a database-driven EF LINQ query, then there are a few limitations.

Supported and Unsupported LINQ Methods (LINQ to Entities) - ADO.NET | Microsoft Learn

I'm not in front of an environment where I can replicate, but I know equality comparers sometimes have problems, so the issue might be here:

   .Where(perm => perm.UserID.Equals(userID)) 

Try changing that to:

   .Where(perm => perm.UserID == userID) 

I tried your change and I'm getting the same error.

Okay, then the next step would be to break it out into separate steps instead of chaining them together, to verify where it fails. So change this:

UserFunctionality   = mContext.Permissions
                  .Where(perm => perm.UserID.Equals(userID))
                  .Select(perm => perm.FunctionalityID)
                  .DefaultIfEmpty(Functionality.NONE)
                  .Max(),

Select allOpen in new window

to:

UserFunctionality   = getUserFunctionality(mContext);

Select allOpen in new window

and add a method called GetUserFunctionality where you break down the LINQ query into individual steps:

private xyz getUserFunctionality(xyz mContext) // <-- replace "xyz" with the appropriate types
{
    var step1 = mContext.Permissions.Where(perm => perm.UserID.Equals(userID));
    var step2 = step1.Select(perm => perm.FunctionalityID);
    var step3 = step2.DefaultIfEmpty(Functionality.NONE);
    var step4 = step3.Max();
    return step4;
}

Select allOpen in new window

Then step through each line with a debugger.

Same basic error, just presented a little differently:

The LINQ expression 'DbSet<Permission>()

    .Where(perm => perm.UserID.Equals(__userID_0))

    .Select(perm => perm.FunctionalityID)

    .DefaultIfEmpty(__p_1)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

The error you're showing indicates it's still chained together instead of separated into steps.

VS indicates to error was with step4

Thanks, the AsEnumerable() finally solved the problem:

var step3 = step2.AsEnumerable().DefaultIfEmpty(Functionality.NONE);

Just to make sure you understand -why- that worked, the LINQ expressions in this case were working against a database entity, so they were trying to execute the desired logic on the server side. 

Basically imagine that those LINQ expressions are trying to be translated into a database query that matches your expressions. However the DefaultIfEmpty was referring to an enum that wasn't defined on the server side, but rather on the client side.

By adding the AsEnumerable, you were ending the server-side execution and turning the results into a client-side enumerable, and then the DefaultIfEmpty was able to then run client-side successfully.

Does that make sense?

Thanks for the explanation and your time, that does make sense.