C# Entity Framework Core Sql Server - Query fields dynamically

I keep my question simple instead of explaining my complete situation.
Is there a possibility to build a query in Entity Framework Core by providing the query field names of the table from a variable?

Let's assume this table

Table "product"

• id bigint
• name string
• status int

The users can filter individually by choosing and combining different filters. So I have a list of filters which should be applied to the query.

So if a user wants to filter only by id, I need a query which looks like this, but with the product.id filled by a variable from my filter list (filter.fieldName)

using(Database.DBContext context = new Database.DBContext())
{
    var query = context.ProductsTable;
    foreach(Models.Filter filter in filterList)
    {
        query.Where((product) => product.id == filter.value); // <-- product.id to be replaced by variable (filter.fieldName)?
    }
    var results = query.ToList();
}

Or is there another way to achieve this without using Where. I know I could also use LINQ, but I think there is the same issue.

To preserve the mapping layer, you can work with Entities + Expressions.

using(Database.DBContext context = new Database.DBContext())
{
    var query = context.ProductsTable;
    foreach(Models.Filter filter in filterList)
    {
        var lambdaParameter = Expression.Parameter(typeof(ProductsTable));
        var lambdaPropertyAccessor = Expression.Property(lambdaParameter, filter.FieldName);

        var comparisonBody = Expression.Equal(lambdaPropertyAccessor, Expression.Constant(filter.Value));
        var comparisonLambda = Expression.Lambda<Func<ProductsTable, bool>>(comparisonBody, lambdaParameter);

        query = query.Where(comparisonLambda);
    }
    var results = query.ToList();
}

You can use dynamic SQL queries in EFCore using FromSqlRaw - but be very careful when using it - you'll have to protect yourself from possible SQL Injection attacks.
FromSqlRaw allows you to use string interpolation to build the SQL query - so in your case it would be something like this:

using(Database.DBContext context = new Database.DBContext())
{
    var query = context.ProductsTable;
    foreach(Models.Filter filter in filterList)
    {
        var filterValue = new SqlParameter("filterValue ", filter.value);
        query.FromSqlRaw(
            $"SELECT * FROM Products WHERE {filter.fieldName}=@filterValue",
            filterValue); 
    }
    var results = query.ToList();
}

Important: the filter value is provided to the database via an sql parameter so it's SQL injection safe, but the column name is not safe - and you should protect yourself from SQL injection using this method.

If your fieldName property value comes from a closed list of options in your own code, and all the user can do is choose from it, then you're safe.
However, if the user can enter the value of fieldName as free text, you will have to verify that it's a valid name before attempting to run the query.
You do that by white-listing the value - meaning compare the entered value to the actual column name in the database (Note: The name of the property in the model isn't required by EF to be the same as the name of the column in the database).