Entity Framework Core in ASP.NET 5 web project - Autogenerate LINQ filters query

Question

I have .NET 5 MVC project (original ASP.NET 3.1 Core MVC), where I use EF Core 5 + React + Redux and MSSql.

I looking for a find way, how to send via REST GET request, data from React form (the form with, select, input, range boxes) into the backend and autogenerate WHERE SQL query from the sand filters, with selected values.

Is there any EF Core feature or I must generate this query for example in FOREACH like:

query = ...
foreach param_filter in
   query = query.Where(...)

I have an HTML form with filters and their values are generated from DB, and each website page has another count and types of filters, but each filter has a unique string ID

ADD INFO:

Input request:

{
    "name": "Application",
    "values": [
        {
            "name": "Smart Homes & Cities | Industrial | Automotive | Security | Consumer",
            "value": "Smart Homes & Cities | Industrial | Automotive | Security | Consumer"
        }
    ],
    "type": "select",
    "field": "ant_application",
    "sort": 4
}

I would like to get REST API request with filters array, and for each field type, generate something like:

foreach (var f in filters)
    query.Where(p => p.Field == p.field).AndValueNotIn(f.values);

Where p means products list, where each product has its own field values

Now I use in EF Core DB procedure call like:

.FromSqlInterpolated($"EXEC GetGroupFilters {groupId}").AsEnumerable()

MSSql procedure has the only SQL (join, select, union), and returns formatted rows from DB which I load into Object and use LINQ, I have 2 choices:

generate raw text from filters with SQL WHERE and send it into the procedure
generate LINQ .Where() etc in backend side with C#

I think then choice number 1 will be faster and better, but still, find the best solution

Is there any better solution?

This is really broad. Provides and specifications for the types and meanings of inputs. — Aluan Haddad
– Aluan Haddad, Commented Feb 2, 2021 at 17:54
You can simply chain Wheres: query.Where(...).Where(...).Where(...). There are many questions on how to compose filter queries at runtime. — Gert Arnold
– Gert Arnold, Commented Feb 2, 2021 at 20:38
Chaining Where statements is what you want. It's simple. You can build the predicates using a predicate builder. This is all stuff that has been covered many times before. — Gert Arnold
– Gert Arnold, Commented Feb 3, 2021 at 8:10

Ramin Azali · Accepted Answer · 2021-08-28 11:40:43Z

1

In fact, I have done such a thing

I Recommend You to Learn about Expression Trees in Linq.Expression as The Link Below I Implement this Before You can Find Your Questions Answers in this link

but for More Understading How can Implement This I Put Some Example Codes Below:

     // Comment   _Queryable.Where(x=>x.Something) | This Queryable from 
     // Type Of Generics (Where Any model With Any Field With This)
     var query = Expression.Equal(Expression.Property(keyParameter, filter.Key),
                        Expression.Constant(filter.Value));
                    MethodCallExpression whereExpression = Expression.Call(
                        typeof(Queryable),
                        "Where",
                        new Type[] {_queryable.ElementType},
                        _queryable.Expression,
                        Expression.Lambda<Func<T, bool>>(query, new 
                      ParameterExpression[] {keyParameter}));
                    _queryable = 
          _queryable.Provider.CreateQuery<T(whereExpression);

Sorry For My Bad English I Wish Its Help You

answered Aug 28, 2021 at 11:40

Ramin Azali

2931 silver badge14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ramin Azali Over a year ago

This sample code is not exactly what you want but it has the concepts you want.

Collectives™ on Stack Overflow

Entity Framework Core in ASP.NET 5 web project - Autogenerate LINQ filters query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related