How do I use a regular expression in Entity Framework?

Question

For the method:

public static IQueryable<ClaimIdentityUser> FindClaimsRegExAsync(this UserDbContext dbContext,
    string searchEmail, string searchClaimValue)
{

    // if both search terms are empty, return all claims
    if (string.IsNullOrEmpty(searchEmail) && string.IsNullOrEmpty(searchClaimValue))
        return ClaimUserJoin(dbContext);

    var query = ClaimUserJoin(dbContext);
    if (!string.IsNullOrEmpty(searchEmail))
        query = query.Where(ciu => Regex.IsMatch(ciu.Email, searchEmail));
    if (!string.IsNullOrEmpty(searchClaimValue))
        query = query.Where(ciu => Regex.IsMatch(ciu.ClaimValue ?? string.Empty, searchClaimValue));
    return query;
}

	public class ClaimIdentityUser : IdentityUserClaim<string>
	{
		/// <summary>
		/// The IdentityUser.Email for the User that this claim is assigned to.
		/// </summary>
		public string Email { get; set; } = default!;
	}

When I call ToList() on it:

list = context.FindClaimsRegExAsync("", "Dave .* Gov").ToList();

I get:

System.InvalidOperationException
The LINQ expression 'DbSet<IdentityUserClaim<string>>()
    .Join(
        inner: DbSet<IdentityUser>(), 
        outerKeySelector: i => i.UserId, 
        innerKeySelector: i0 => i0.Id, 
        resultSelector: (i, i0) => new TransparentIdentifier<IdentityUserClaim<string>, IdentityUser>(
            Outer = i, 
            Inner = i0
        ))
    .Where(ti => Regex.IsMatch(
        input: ti.Outer.ClaimValue ?? "", 
        pattern: __searchClaimValue_0))' could not be translated. Additional information: Translation of method 'System.Text.RegularExpressions.Regex.IsMatch' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. 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.

How can I use a regular expression in a query?

thanks - dave

Answer 1

SQL does not have regex support only the wildcard support of the like and pattern match. you use the ColumnName.Contains("") to use the like pattern matching:

query = query.Where(ciu => ciu.Email.Contains(searchEmail));

see pattern match syntax:

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-ver16

if you need real regex support, you would create a user defined function that implements regex, then map the function to EF:

https://learn.microsoft.com/en-us/ef/core/querying/user-defined-function-mapping