EF Core & LINQ - How to build a dynamic Where clause using a string variable at run time?

Question

I've read many different variations to this question and I cannot believe the solution I need is so complicated that warrants using additional libraries and crazy tricks, hopefully not!

At runtime, the LINQ query in my project needs to change dynamically depending on how many columns in the DB table the user wants to filter by. In my example, I first show a working LINQ Query which is hard coded. The next example uses a list that is built at runtime, all i need to figure out is how to insert the string variable (whereClause) into the LINQ Query without compile errors?

Working example (hard coded)

logs = _context.Logs.Where(s => s.Level == LogLevel & s.LogEventCategory == EventCategory)
               .Select(s => new Logs()
               {
                   TimeStamp = s.TimeStamp,
                   Level = s.Level,
                   Exception = s.Exception,
                   LogEventCategory = s.LogEventCategory,
                   LogEventType = s.LogEventType,
                   LogEventSource = s.LogEventSource,
                   LogEventName = s.LogEventName,
                   LogUserName = s.LogUserName,
                   LogForename = s.LogForename,
                   LogSurname = s.LogSurname,
                   LogData = s.LogData
               });

Example Two - The solution I want to fix and use...

First create a list, the contents of the list will change each time a new query is run, strings passed as variables through the parent OnGet method will either contain a value and be used in the string join concatenation, or will be null and therefore not added to the list and used in the concatenation.

Second example is where I get compilation errors.

var filtersList = new List<string>();
        if (LogLevel != null)
        {
            filtersList.Add("s.LogLevel == LogLevel");
        }
        if (EventCategory != null)
        {
            filtersList.Add("s.EventCategory == EventCategory");
        }

        var whereClause = string.Join(" & ", filtersList.ToArray());


logs = _context.Logs.Where(s => whereClause) // *HERE I WANT TO USE THE STRING VARIABLE! not working
                   .Select(s => new Logs()
                   {
                       TimeStamp = s.TimeStamp,
                       Level = s.Level,
                       Exception = s.Exception,
                       LogEventCategory = s.LogEventCategory,
                       LogEventType = s.LogEventType,
                       LogEventSource = s.LogEventSource,
                       LogEventName = s.LogEventName,
                       LogUserName = s.LogUserName,
                       LogForename = s.LogForename,
                       LogSurname = s.LogSurname,
                       LogData = s.LogData
                   });

The error I get says 'Cannot convert lambda expression to intended delegate type because some of the return types in the block are not implicitly convertible to the delegate return type' blah blah blah

Why not just use the `Expression>`? You can build filters dynamically with this — panoskarajohn, Dec 29 '19 at 19:04
Can you please help me with an example, no idea what you're talking about, thx — OJB1, Dec 29 '19 at 19:06
Although this example uses LinqKit you do not have to -> http://www.albahari.com/nutshell/linqkit.aspx — panoskarajohn, Dec 29 '19 at 19:06

score 4 · Accepted Answer · answered Dec 29 '19 at 19:09

4

You just add the .Where() calls to the end of your query, before materializing:

query = _context.Logs.Select(s => new ..);

if (EventCategory != null) query = query.Where(e => e.EventCategory == EventCategory);
if (LogLevel != null) query = query.Where(e => e.LogLevel == LogLevel);

var items = query.ToList();

answered Dec 29 '19 at 19:09

Milney

6,253
2
19
33

Are there any performance hits expected using multiple where clauses in this example rather than using multiple &s in a single where statement? I will be querying a large table, thx – OJB1 Dec 29 '19 at 19:16
1

No they *should* be converted to the exact same sql query. You can see your self by trying both and looking at the Log property on the datacontext or using SQL Profiler to check the actual SQL that is run – Milney Dec 29 '19 at 19:16
Not sure how the 'var items' is used in the last part of the execution (Logs = await PaginatedList.CreateAsync(logs.AsNoTracking(), pageIndex ?? 1, pageSize); – OJB1 Dec 29 '19 at 19:27
Got it now, brilliant many thanks for you help – OJB1 Dec 29 '19 at 19:53
This doesn't work for OR statements – joby-flick May 22 '20 at 12:50
You should try to avoid OR statements anyway as they tend to ruin your performance. Try doing a UNION between the two conditions – Milney May 22 '20 at 13:01

EF Core & LINQ - How to build a dynamic Where clause using a string variable at run time?

1 Answers1