How to create an expression from a parameterized function which is compatible with EFCore?-CodePudding

I want to filter a certain entity by Name. But "Name" is ambigous, we could have a UPN, a logon name, a display name, a first name, a last name. so I want to extract the lengthy Expression to a Function that takes the search string and passes it to the Expression like this:

public Func<string, Expression<Func<AuthenticatedUserModel, bool>>> MatchByName =>
        filter => x =>
            x.StatusId != StatusId.Deleted
            && x.StatusId != StatusId.Unprepped
            && SupportedRecordTypes.Contains(x.RecordType)
            && x.LogonName.ToLower().Contains(filter.ToLower())
            && x.DisplayName.ToLower().Contains(filter.ToLower())
            && x.Email.ToLower().Contains(filter.ToLower())
            && x.Upn.ToLower().Contains(filter.ToLower());

now if i evaluate this query against EFCore almost everything works fine, but my filter parameter is an empty string!

it then gets called like this

public IQueryable<UsernameResponse> GetNamesLike(string filter)
    {
        return _dbContext.AuthenticatedUsers
            .Where(MatchByName(filter));
    }

how do i make this example work?

genrated code looks like this

DECLARE @__p_1 int = 50;
DECLARE @__ToLower_0 nvarchar(4000) = N'1027';

SELECT TOP(@__p_1) [w].[LogonName], [w].[DisplayName], [w].[Id]
FROM [dbo].[WebUserAuthenticatedUsersView] AS [w]
WHERE ((((([w].[StatusId] NOT IN ('139ed76b-a8bc-4f46-91aa-63e6efd4be56', '14d5a731-d03b-414f-890c-7fab2fedea6a') AND [w].[RecordType] IN (1, 0, 4)) AND ((@__ToLower_0 LIKE N'') OR (CHARINDEX(@__ToLower_0, LOWER([w].[LogonName])) > 0))) AND ((@__ToLower_0 LIKE N'') OR (CHARINDEX(@__ToLower_0, LOWER([w].[DisplayName])) > 0))) AND ((@__ToLower_0 LIKE N'') OR (CHARINDEX(@__ToLower_0, LOWER([w].[Email])) > 0))) AND ((@__ToLower_0 LIKE N'') OR (CHARINDEX(@__ToLower_0, LOWER([w].[Upn])) > 0))) AND ([w].[RecordType] <> 4)

Edit, this questions was stupid and i'm sorry. the filter arguemnt was never passed as empty string, it had the correct value from the start and i was just misreading the SQL it produced. I was looking for the search string in the actual query but it was passed as a parameter and all i read in the mody was N''

thanks for all who put effort in this question

CodePudding user response：

Try it as a function:

public Expression<Func<AuthenticatedUserModel, bool>> MatchByName(string filter) =>
    x =>
        x.StatusId != StatusId.Deleted
        && x.StatusId != StatusId.Unprepped
        && SupportedRecordTypes.Contains(x.RecordType)
        && x.LogonName.ToLower().Contains(filter.ToLower())
        && x.DisplayName.ToLower().Contains(filter.ToLower())
        && x.Email.ToLower().Contains(filter.ToLower())
        && x.Upn.ToLower().Contains(filter.ToLower());