It only takes a minute to sign up. I'm developing an application using ASP. In that application I have a lot of paged lists. Users can filter and sort these lists. This results in code like the one below.
Raw SQL Queries
I'm not really happy with this code. Is there a better way to do the filtering and sorting with Entity Framework? Some of you might suggest putting that code into a service class and not in the controller, but that would just move the ugly code somewhere else.
Instead of ugly code in a controller, I'd end up with ugly code in a service. I know this is old, but thought it may be helpful for anyone reading this. If you want to clean up the code, you can always refactor it. EDIT: I apologize from my earlier sample that didn't quite compile.
I've fixed it and added a more complete example. You could associate each condition with a strategy for changing the query. Each strategy called SearchFieldMutator in this example will hold two things:. If it return truethe strategy is applied. If it returns falseit's not applied. This is the type of the delegate:.
The second part is the strategy itself. It is supposed to 'mutate' the query by applying a LINQ operator to it, but really just returns a new query with the added operator, and the caller of it should discard the old query and keep the new one.
So it's not really mutation, but it has the same effect. I've created a new delegate type for it, so its usage is clear:.
NOTE: I've defined both the item type and the search data as generic types as TItem and TSearchrespectively so this code is usable in multiple locations in your code. Now that we have defined the two types of the strategy, we can create a class that holds them both, and also does the simulated mutation:.
Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information. I want to write a where LINQ query which will filter the table based on those dynamic filtering options. The following code is not working properly. I'm looking for something similar that can solve this issue:.
I would appreciate suggestions on how I can get through this and manage the filtering dynamically. If you have only AND conditions you can do it with just chaining Where clauses:. Also as Lajos Arpad said maybe you need to consider combining fields null checks i. Learn more. Asked 4 days ago. Active 4 days ago. Viewed 24 times. I'm developing an application. Net Core 3. I would like to filter a table with several filtering options. The twist is I need to manage the filtering with many options and combinations.
You can filter for the marketcountryvendor and the rest of the filter options will be null. You would like to filter for country and vendor then the market will be null and also the rest of the filter options.
I'm querying a huge table, so it is important to write a query which translates fully into SQL. Contains x. Market : false. ToList ; I would appreciate suggestions on how I can get through this and manage the filtering dynamically. Peter Csala 1, 5 5 silver badges 14 14 bronze badges.
New contributor. Active Oldest Votes. ToList ; Also as Lajos Arpad said maybe you need to consider combining fields null checks i. GetAll ; if filters. ToList .I'm trying to make some article-filtering based on properties that are stored in another dbset.
I'm using some classes:. I don't know why Dynamic LINQ has its own expression language. All queryable extensions define single scoped parameter called itwhich can be omitted.
Shortly, Dynamic LINQ is not well suitable for complex queries with nested lambda expression accessing outer lambda parameters. The goal can be achieved relatively easy with the combination of compile time and runtime expressions.
The idea is simple. First you create a compile time lambda expression with additional parameters which serve as placeholders. Then you replace the placeholders with actual expressions using the following simple expression visitor:. Pretty much like string.
Formatbut with expressions. Then you can use Expression. AndAlso and Expression. Dynamic Linq with ".
Question I'm trying to make some article-filtering based on properties that are stored in another dbset. Where query. Bulk Delete. Bulk Update.
Bulk Merge. Where query ; foreach Target : base. Constant filter. Constant filterDetail. Aggregate Expression. Ivan Stoev. View more on Stack Overflow. Prime Library. Not affiliated with Stack Overflow.GitHub is home to over 50 million developers working together to host and review code, manage projects, and build software together. If nothing happens, download GitHub Desktop and try again. If nothing happens, download Xcode and try again. If nothing happens, download the GitHub extension for Visual Studio and try again.
This library is powered by Entity Framework Extensions. Create global and scoped filters for Entity Framework queries.
Dynamic Linq with ".Any" in Where clausule (C# / .Net Core / EF Core)
Access to DynamicFilters is done via extension methods in the EntityFramework. The package is also available on NuGet: EntityFramework. This will create a filter to restrict queries on the Notes table to only those records made by people in the current users Organization. The filter will only be enabled if the current user is not an Administrator. And both expressions access properties in the current DbContext instance.
Filters should always follow any other model configuration - including the call to the base. OnModelCreating method. It is best to make the filter definitions the final step of OnModelCreating to make sure that they are not in effect until the entire model is fully configured.
All filters have global scope and will be used by all DbContexts. Each DbContext can also choose to provide a "scoped" filter value or can disable the filter via the DisableFilter extension method. Scoped parameter changes and filter disabling will apply only to that DbContext and do not affect any existing or future DbContexts. Filters can be defined on a specific entity class or an interface. Below is an example of a "soft delete" filter created on an ISoftDelete interface.
This can allow you to vary the parameter value dynamically.When you use Entity Framework for data access and you need to retrieve data from data store, you usually write LINQ expression. LINQ provides fluent syntax for expressing what data we are interested in.
And since syntax is the same for querying in-memory collection or external data store, I never really look into what the difference is and how queries are actually constructed. Until the day that I was designing API which allowed client to sort on any field.
And there is a better way. Let us start with simple LINQ query which has order by clause applied. This might look something like this:. What we are interested in is definition and implementation of System. OrderBy method:. There are two important things to see here. What this means is that compiler generates expression tree automatically for given argument instead of compiling it to executable code. This way developer does not have to generate expression trees by hand.
But they can still be created in code if desired and we will use this with dynamic order by statement. Second important thing to notice is that this method creates new expression tree out of existing expression to which OrderBy call is applied. This expression tree is then used as argument to System. We can reuse this logic in our function for dynamic order by.
If we analyze lambda expression that is being passed to OrderBy function, we see that it is constructed from:. All work of constructing possible property accessors is done inside static constructor which is executed only once for each type T.
We store constructed accessors into a dictionary so that we can access them by property name. In above helper this access is case sensitive — if you wish to have case insensitive API, then specify StringComparer. OrdinalIgnoreCase as argument to dictionary constructor.
Now that we have accessor expressions for all properties we can write extension method for applying order by statement by property name:. Since property type is not known at compile time, we have to use another overload for Expression. Call where we let factory method to locate appropriate method by explicitely providing type arguments. If this code is frequently executed it may be better to find System.
MethodInfo explicitely and cache it for later use. Dynamic filtering is more complicated than order by statement since we have multiple possible operators and we have to bring parameter value from outside.
In this example I will present solution using equality operator.I've taken a look at this answer on how to dynamically create an OrderBy expression in Entity Framework. But I'd like to also build a dynamic Where expression. Something along the lines of this:. Passing in the property to filter by and a value as ab object.
Queryable has two methods for Where that both take two parameters, so I am not even sure which is the proper one. And it's at this point I get a little more lost. I know what I have here is completely wrong. I'm not sure what to do with it.
Ideally, I'd want to extend this even more by providing a collection of objects that could be used to build a query with ands and or operators. I've tried to write it in such a way so you can step over the code in order to understand what it does.
The only line that might need some explanation is:. This is a simple way of handling nested properties like obj. Prop2 etc. If you don't need such capability, you can simply use this instead:. I didn't need nested properties yet. I modified your code slightly and have this that is working:. How to construct Where Expression dynamically in Entity Framework? Question I've taken a look at this answer on how to dynamically create an OrderBy expression in Entity Framework. WhereByProperty filterBy, value ; foreach var item in query.
Greg Kopp. Bulk Delete. Bulk Update.
Entity Framework (EF) Core Cache
Bulk Merge. Accepted Answer Queryable has two methods for Where that both take two parameters, so I am not even sure which is the proper one. Split '.
Aggregate Expression item, Expression. Type; if value!Entity Framework Core allows you to drop down to raw SQL queries when working with a relational database. Raw SQL queries can return regular entity types or keyless entity types that are part of your model. You can view this article's sample on GitHub.
In addition to validating that such values don't contain invalid characters, always use parameterization which sends the values separate from the SQL text. The following example passes a single parameter to a stored procedure by including a parameter placeholder in the SQL query string and providing an additional argument. While this syntax may look like String. As with the previous example, the value is converted to a DbParameter and isn't vulnerable to SQL injection.
Prior to version 3. For more information, see the previous versions section. You can also construct a DbParameter and supply it as a parameter value. FromSqlRaw allows you to use named parameters in the SQL query string, which is useful when a stored procedure has optional parameters:. EF Core will treat it as subquery and compose over it in the database.
And then composes on it using LINQ to do filtering and sorting. Further, SQL passed shouldn't contain any characters or options that aren't valid on a subquery, such as:. SQL Server doesn't allow composing over stored procedure calls, so any attempt to apply additional query operators to such a call will result in invalid SQL.
For example, if the query projects entity types, the results will be tracked by default. EF Core version 2. It was easy to accidentally call the raw string method when the intent was to call the interpolated string method, and the other way around.
Calling wrong overload accidentally could result in queries not being parameterized when they should have been. Submit and view feedback for.Change the connection string dynamically for edmx Entity during run time
Skip to main content. Contents Exit focus mode. Tip You can view this article's sample on GitHub. Note Prior to version 3.