how to do dynamic column and values in linq c# query

Question

Hi All, i need to filter the table values in linq with passing column name and values dynamically Name Age Address john 34 USA albert 35 UK david 32 Japan having dynamic class values like below {\"name\":{\"value\":john,\"matchMode\":\"contains\"} {\"age\":{\"value\":35,\"matchMode\":\"startsWith\"} above values are generated from primeng datatable need to filter above table based on above json values how can i write query in where clause dynamic column and values filter

Pranam Bhat · Answer

Use Eval Expression.NET which lets you evaluate dynamically c# code at runtime. using (var ctx = new TestContext()) { var query = ctx.Entity_Basics; var list = Eval.Execute(@" q.Where(x => x.ColumnInt new { x.ID, x.ColumnInt }) .ToList();", new { q = query }); } You can easily use even async method from Entity Framework. If you customize the select also (the return type) you may need to either get the async result using reflection or use ExecuteAsync instead with ToList(). public async Task > DynamicWhereAsync(CancellationToken cancellationToken = default(CancellationToken)) { // Register async extension method from entity framework (this should be done in the global.asax or STAThread method // Only Enumerable && Queryable extension methods exists by default EvalManager.DefaultContext.RegisterExtensionMethod(typeof(QueryableExtensions)); // GET your criteria var tuples = new List >(); tuples.Add(new Tuple ("Specialty", "Basket Weaving")); tuples.Add(new Tuple ("Rank", "12")); // BUILD your where clause var where = string.Join(" && ", tuples.Select(tuple => string.Concat("x.", tuple.Item1, " > p", tuple.Item1))); // BUILD your parameters var parameters = new Dictionary (); tuples.ForEach(x => parameters.Add("p" + x.Item1, x.Item2)); using (var ctx = new TestContext()) { var query = ctx.Entity_Basics; // ADD the current query && cancellationToken as parameter parameters.Add("q", query); parameters.Add("token", cancellationToken); // GET the task var task = (Task >)Eval.Execute("q.Where(x => " + where + ").ToListAsync(token)", parameters); // AWAIT the task var result = await task.ConfigureAwait(false); return result; } } If you're concerned with maintainability and that the table's columns are going to change often, then you may want to go back to using raw SQL via the SqlCommand class. That way, you can easily generate dynamic selects and where clauses. You could even query the list of columns on the table to dynamically determine which options are available for selecting/filtering.

how to do dynamic column and values in linq c# query

Answers (1)