$filter=contains(Property_Name, 'Value') doesn't work

[AndriiLesiuk]

I'm trying to send a request like this: https://localhost:44394/odata/NormChemicalCapacityByCompanyCapacityToProduceApiData?$filter=contains(REGION_NAME, 't')

But I get the following error:

System.InvalidCastException: Unable to cast object of type 'Microsoft.EntityFrameworkCore.Query.SqlExpressions.SqlParameterExpression' to type 'Microsoft.EntityFrameworkCore.Query.SqlExpressions.SqlConstantExpression'.
   at owo220m.m.Translate(SqlExpression, MethodInfo, IReadOnlyList`1, IDiagnosticsLogger`1)
   at Microsoft.EntityFrameworkCore.Query.RelationalMethodCallTranslatorProvider.<>c__DisplayClass7_0.<Translate>b__0(IMethodCallTranslator t)
   at System.Linq.Enumerable.SelectEnumerableIterator`2.MoveNext()
   at System.Linq.Enumerable.TryGetFirst[TSource](IEnumerable`1 source, Func`2 predicate, Boolean& found)
   at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source, Func`2 predicate)
   at Microsoft.EntityFrameworkCore.Query.RelationalMethodCallTranslatorProvider.Translate(IModel model, SqlExpression instance, MethodInfo method, IReadOnlyList`1 arguments, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateInternal(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateExpression(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateLambdaExpression(ShapedQueryExpression shapedQueryExpression, LambdaExpression lambdaExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateWhere(ShapedQueryExpression source, LambdaExpression predicate)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.System.Collections.IEnumerable.GetEnumerator()
   at Microsoft.AspNetCore.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteResourceSetAsync(IEnumerable enumerable, IEdmTypeReference resourceSetType, ODataWriter writer, ODataSerializerContext writeContext)
   at Microsoft.AspNetCore.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObjectInlineAsync(Object graph, IEdmTypeReference expectedType, ODataWriter writer, ODataSerializerContext writeContext)
   at Microsoft.AspNetCore.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObjectAsync(Object graph, Type type, ODataMessageWriter messageWriter, ODataSerializerContext writeContext)
   at Microsoft.AspNetCore.OData.Formatter.ODataOutputFormatterHelper.WriteToStreamAsync(Type type, Object value, IEdmModel model, ODataVersion version, Uri baseAddress, MediaTypeHeaderValue contentType, HttpRequest request, IHeaderDictionary requestHeaders, IODataSerializerProvider serializerProvider)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResultFilterAsync>g__Awaited|30_0[TFilter,TFilterAsync](ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResultExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.ResultNext[TFilter,TFilterAsync](State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeResultFilters()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.OData.Batch.ODataBatchMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.OData.Routing.ODataRouteDebugMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Watch.BrowserRefresh.BrowserRefreshMiddleware.InvokeAsync(HttpContext context)
   at Microsoft.AspNetCore.Server.IIS.Core.IISHttpContextOfT`1.ProcessRequestAsync()

The same goes for this "endswith" and "startswith" functionality: https://localhost:44394/odata/NormChemicalCapacityByCompanyCapacityToProduceApiData?$filter=endswith(REGION_NAME, 't') https://localhost:44394/odata/NormChemicalCapacityByCompanyCapacityToProduceApiData?$filter=startswith(REGION_NAME, 't')

System.InvalidOperationException: The LINQ expression 'DbSet<NormChemicalCapacityByCompanyCapacityToProduceApiData>()
    .Where(n => __TypedProperty_0 == "" || n.Region_Name != null && __TypedProperty_0 != null && n.Region_Name.EndsWith(__TypedProperty_0))' could not be translated. Additional information: Translation of method 'string.EndsWith' 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.
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|15_0(ShapedQueryExpression translated, <>c__DisplayClass15_0&)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.System.Collections.IEnumerable.GetEnumerator()
   at Microsoft.AspNetCore.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteResourceSetAsync(IEnumerable enumerable, IEdmTypeReference resourceSetType, ODataWriter writer, ODataSerializerContext writeContext)
   at Microsoft.AspNetCore.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObjectInlineAsync(Object graph, IEdmTypeReference expectedType, ODataWriter writer, ODataSerializerContext writeContext)
   at Microsoft.AspNetCore.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObjectAsync(Object graph, Type type, ODataMessageWriter messageWriter, ODataSerializerContext writeContext)
   at Microsoft.AspNetCore.OData.Formatter.ODataOutputFormatterHelper.WriteToStreamAsync(Type type, Object value, IEdmModel model, ODataVersion version, Uri baseAddress, MediaTypeHeaderValue contentType, HttpRequest request, IHeaderDictionary requestHeaders, IODataSerializerProvider serializerProvider)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResultFilterAsync>g__Awaited|30_0[TFilter,TFilterAsync](ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResultExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.ResultNext[TFilter,TFilterAsync](State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeResultFilters()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.OData.Batch.ODataBatchMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.OData.Routing.ODataRouteDebugMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Watch.BrowserRefresh.BrowserRefreshMiddleware.InvokeAsync(HttpContext context)
   at Microsoft.AspNetCore.Server.IIS.Core.IISHttpContextOfT`1.ProcessRequestAsync()

My Program class:

using CDataRelationalCrud.Helpers;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Versioning;
using Microsoft.AspNetCore.OData;
using Microsoft.EntityFrameworkCore;
using Microsoft.OData.ModelBuilder;
using OData.Swagger.Services;
using ODataPrePOC.Domain.Models;
using ODataPrePOC.Infrastructure.DatabaseContext;
using ODataPrePOC.Service.Interfaces;
using ODataPrePOC.Service.Services;
using Serilog;

var builder = WebApplication.CreateBuilder(args);
var modelBuilder = new ODataConventionModelBuilder();
modelBuilder.EntitySet<NormChemicalCapacityByCompanyCapacityToProduceApiData>("NormChemicalCapacityByCompanyCapacityToProduceApiData").HasCountRestrictions().IsCountable(true);

Log.Logger = new LoggerConfiguration().ReadFrom.Configuration(builder.Configuration).CreateLogger();
builder.WebHost.UseSerilog();

builder.Services.AddControllers()
    .AddOData(
    options => options
    .EnableQueryFeatures(1000)
    .Count()
    .AddRouteComponents(
                    routePrefix: "odata",
                    model: modelBuilder.GetEdmModel())
    .EnableAttributeRouting = false);

builder.Services.AddHealthChecks();

// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen((config) =>
{
    config.SwaggerDoc("v1", new Microsoft.OpenApi.Models.OpenApiInfo()
    {
        Title = "Swagger Odata Demo Api",
        Description = "Swagger Odata Demo",
        Version = "v1"
    });
});
builder.Services.AddApiVersioning(options =>
{
    options.AssumeDefaultVersionWhenUnspecified = true;
    options.DefaultApiVersion = ApiVersion.Default;
    options.ApiVersionReader = new HeaderApiVersionReader("Api-Version");
});
builder.Services.AddOdataSwaggerSupport();

builder.Services.AddScoped<INormChemicalCapacityByCompanyCapacityToProduceApiDataService, NormChemicalCapacityByCompanyCapacityToProduceApiDataService>();

builder.Configuration.AddEnvironmentVariables();

var snowflakeConnectionString = builder.Configuration.GetConnectionString("CDataSnowflake");
builder.Services.AddDbContext<DASchemaDbContext>(options =>
{
    options.UseSnowflake(snowflakeConnectionString);
});

var app = builder.Build();
app.UseODataRouteDebug();
app.UseODataBatching();
app.UseRouting();
app.UseAuthorization();
app.UseEndpoints(endpoints => endpoints.MapControllers());
// Configure the HTTP request pipeline.

app.UseSwagger();
app.UseSwaggerUI((config) =>
{
    config.SwaggerEndpoint("/swagger/v1/swagger.json", "Swagger Odata Demo Api");
});

// this working only for api/[Controller], but not for odata/[Controller]
// app.UseMiddleware<TimingMiddleware>();

app.UseMiddleware<ExceptionMiddleware>();

app.UseHttpsRedirection();

app.MapHealthChecks("/health");

//app.UseSerilogRequestLogging();

app.Run();

My controller class:

namespace CDataRelationalCrud.Controllers
{
    using Microsoft.AspNetCore.Mvc;
    using Microsoft.AspNetCore.OData.Formatter;
    using Microsoft.AspNetCore.OData.Query;
    using Microsoft.AspNetCore.OData.Routing.Controllers;
    using ODataPrePOC.Domain.Models;
    using ODataPrePOC.Service.Interfaces;

    [ApiVersion("1.0")]
    public class NormChemicalCapacityByCompanyCapacityToProduceApiDataController : ODataController
    {
        private readonly INormChemicalCapacityByCompanyCapacityToProduceApiDataService _service;

        public NormChemicalCapacityByCompanyCapacityToProduceApiDataController(INormChemicalCapacityByCompanyCapacityToProduceApiDataService service)
        {
            _service = service;
        }

        [HttpPost]
        public async Task<IActionResult> Post([FromBody] NormChemicalCapacityByCompanyCapacityToProduceApiData product)
        {
            await _service.Post(product);
            return Ok();
        }

        [HttpGet, EnableQuery]
        public async Task<IActionResult> Get(ODataQueryOptions<NormChemicalCapacityByCompanyCapacityToProduceApiData> queryOptions)
        {
            var result = await _service.Get();
            return Ok(result);
        }

and my Service Get method:

   public async Task<IQueryable<NormChemicalCapacityByCompanyCapacityToProduceApiData>> Get()
        {
            var result = _database.NormChemicalCapacityByCompanyCapacityToProduceApiData.AsQueryable();
            return result;
        }

The problem is solved by a change(ToList()) in the controller:

[HttpGet, EnableQuery]
        public async Task<IActionResult> Get(ODataQueryOptions<NormChemicalCapacityByCompanyCapacityToProduceApiData> queryOptions)
        {
            var result = await _service.Get();

            return Ok(result.ToList());
        }

But it does not suit me, because then all the data is loaded, and filtering will take place on the client side, not on the database side. I also found this article: https://github.com/DevExpress/DevExtreme.AspNet.Data/issues/428

I work with Snowflake via CData. I will be grateful for help or advice.

[habbes]

@AndriiLesiuk could you share your data model classes and IEdmModel configurations as well?

What version of EF Core are you using and what provider library are you using to connect EFCore to Snowflake (I'm not familiar with CData).

My guess is that either the Snowflake does not support starts/endswidth functions, or the specific DB or EF core driver you're using doesn't know how to translate these functions to the corresponding Snowflake functions.

[habbes]

So just checked the docs, Snowflake DOES support those functions:

• https://docs.snowflake.com/en/sql-reference/functions/endswith
• https://docs.snowflake.com/en/sql-reference/functions/startswith

What EF Core provider are you using?

[AndriiLesiuk]

@habbes Thanks for the reply!

My model class:

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace ODataPrePOC.Domain.Models
{
    [Table("NORM_CHEMICALCAPACITYBYCOMPANY_CAPACITYTOPRODUCE_API_DATA")]
    public class NormChemicalCapacityByCompanyCapacityToProduceApiData
    {
        [Key]
        [Column("SOURCE_ID")]
        public string? Source_Id { get; set; }

        [Column("ORIGIN_ID")]
        public string? Origin_Id { get; set; }

        [Column("CITY")]
        public string? City { get; set; }

        [Column("REGION_NAME")]
        public string? Region_Name { get; set; }

        [Column("REMARKS")]
        public string? Remarks { get; set; }

        [Column("START_DATE")]
        public string? Start_Date { get; set; }

        [Column("END_DATE")]
        public string? End_Date { get; set; }

        [Column("LAST_UPDATE_DATE")]
        public string? Last_Update_Date { get; set; }

        [Column("DATAGROUP")]
        public string? Data_Group { get; set; }

        [Column("CONCEPT")]
        public string? Concept { get; set; }

        [Column("PRODUCT")]
        public string? Product { get; set; }

        [Column("PROCESS")]
        public string? Process { get; set; }

        [Column("PRODUCER")]
        public string? Producer { get; set; }

        [Column("STATE")]
        public string? State { get; set; }

        [Column("UNIT")]
        public string? Unit { get; set; }

        [Column("FREQUENCY")]
        public string? Frequency { get; set; }

        [Column("OBSERVATIONS")]
        public string? Observations { get; set; }

        [Column("COUNTRY_TERRITORY")]
        public string? Country_Territory { get; set; }
    }
}

as EF Core provider (connector between EF and Snowflake) I use a paid solution from CData: https://www.cdata.com/kb/tech/snowflake-ado-codefirst.rst , because I couldn't find another working solution for EF and Snowflake.

My context class:

namespace ODataPrePOC.Infrastructure.DatabaseContext
{
    using Microsoft.EntityFrameworkCore;
    using ODataPrePOC.Domain.Models;
    using ODataPrePOC.Domain.Models.RAWModels;

    public class DEVELOPSchemaDbContext : DbContext
    {
        public DEVELOPSchemaDbContext(DbContextOptions<DEVELOPSchemaDbContext> options)
            : base(options)
        { }

        public DbSet<NormChemicalCapacityByCompanyCapacityToProduceApiData>? NormChemicalCapacityByCompanyCapacityToProduceApiData { get; set; }
    }
}

and I didn't do any additional settings related to IEdmModel.

Cheers

[AndriiLesiuk]

And I use Microsoft.EntityFreamworkCore(6.0.15) as well.

[habbes]

Maybe you could check with cdata whether they have support for contains, startswith and endswith functions. Worth noting that the exception thrown when using contains is different from the other methods.

I haven't tried this, but maybe it could provide worthwhile: what about trying to create custom function mappings and translations as suggested by the exception message.

Following this guide: https://learn.microsoft.com/en-us/ef/core/querying/user-defined-function-mapping you could create a custom startswith function and map it to the appropriate SQL or create a corresponding db procedure for it, then check if that helps.

[AndriiLesiuk]

I have no choice but to implement my own logic for building expressions:

    internal static class FilterHelper
    {
        public static (string fieldName, string fieldValue) GetFilterParts(string filter)
        {
            if (string.IsNullOrWhiteSpace(filter))
            {
                return (null, null);
            }

            var filterParts = filter.Split(new[] { "(", ",", ")", " " }, StringSplitOptions.RemoveEmptyEntries);

            if (filterParts.Length != 3 || filter.IndexOf("'") == -1)
            {
                return (null, null);
            }

            var fieldName = filterParts[1].Trim().ToLower();
            var fieldValue = filterParts[2].Trim('\'');
            fieldValue = fieldValue.TrimStart('\'');
            fieldValue = ProcessFieldValue(filter, fieldValue);
            return (fieldName, fieldValue);
        }

        public static Expression<Func<T, bool>> GetFilterPredicate<T>(string filter)
        {
            if (string.IsNullOrWhiteSpace(filter))
            {
                return null;
            }

            var filterParts = filter.Split(new[] { " and ", " AND ", "&&" }, StringSplitOptions.RemoveEmptyEntries);

            var parameterExpression = Expression.Parameter(typeof(T), "record");

            Expression<Func<T, bool>> predicate = null;

            foreach (var part in filterParts)
            {
                var (fieldName, fieldValue) = GetFilterParts(part);

                if (!string.IsNullOrWhiteSpace(fieldName) && !string.IsNullOrWhiteSpace(fieldValue))
                {
                    var memberExpression = Expression.Property(parameterExpression, fieldName);
                    var constantExpression = Expression.Constant(fieldValue, typeof(string));
                    var methodInfo = typeof(DbFunctionsExtensions).GetMethod(nameof(DbFunctionsExtensions.Like),
                        new[] { typeof(DbFunctions), typeof(string), typeof(string) });
                    var callExpression = Expression.Call(methodInfo, Expression.Constant(EF.Functions), memberExpression, constantExpression);

                    var expression = Expression.Lambda<Func<T, bool>>(callExpression, parameterExpression);

                    predicate = predicate == null ? expression : Expression.Lambda<Func<T, bool>>(Expression.AndAlso(predicate.Body, expression.Body), parameterExpression);
                }
            }

            return predicate;
        }

        public static string ProcessFieldValue(string filter, string fieldValue)
        {
            if (filter.IndexOf("contains", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                return $"%{fieldValue}%";
            }

            if (filter.IndexOf("startswith", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                return $"{fieldValue}%";
            }

            if (filter.IndexOf("endswith", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                return $"%{fieldValue}";
            }

            return fieldValue;
        }
    }

and my service class:

public async Task<IQueryable<NormChemicalCapacityByCompanyCapacityToProduceApiData>> Get(string? filter)
        {
            var predicate = FilterHelper.GetFilterPredicate<NormChemicalCapacityByCompanyCapacityToProduceApiData>(filter);
            if(predicate == null) return _database.NormChemicalCapacityByCompanyCapacityToProduceApiData.AsQueryable();

            var filteredData = await _database.NormChemicalCapacityByCompanyCapacityToProduceApiData.Where(predicate).ToListAsync();

            return filteredData.AsQueryable();
        }