Format for Dynamic IQueryable with Strings and Dates

Question

I'm unsure of the correct format to use string and date as variables in a dynamic IQueryable. With numbers, even though they are passed as strings, it works correctly. However, if I use strings or dates, it breaks the query without errors; it just hangs the application. I'm sure I'm missing quotes, but I can't get the format correctly.

This is my code:

string userFilter = string.Empty;
string var1 = "3";
string var2 = "54";
string var3 = "Doe, John";
DateTime var4 = DateTime.Now();

IQueryable<MyClass> query = await _context.Table.GetData();

if (!string.IsNullOrEmpty(var1)) 
{
    userFilter = " column1 == " + var1 + " &&"; 
}; //This works

if (!string.IsNullOrEmpty(var2)) 
{
    userFilter = userFilter + " column2 == " + var2 + " &&"; 
}; //This works

if (!string.IsNullOrEmpty(var3)) 
{
    userFilter = userFilter + " column3 == \"" + var3 + "\" &&"; 
}; //Works

if (!string.IsNullOrEmpty(var4)) 
{
    userFilter = userFilter + " column4 == " + var4 + " &&"; 
}; //Doesn't work

userFilter = userFilter.TrimEnd('&');
query = query.Where(userFilter);

var myData = query.ToList();  

The first two 'if' work correctly, or any with numbers as strings, but actual strings or dates do not. What am I missing?

Thank you!

Answer 1

The System.Linq.Dynamic.Core library uses its own syntax for DateTime values that resembles ctor calls, like so:

context.Customers.Where("DateTimeProperty >= DateTime(2024,8,16,18,33,0)").ToList();

...but it's a better idea to use parameters, which have the form @<n>, where <n> is an integer:

DateTime now = DateTime.UtcNow;
context.Customers.Where("DateTimeProperty >= @0", now).ToList();

---

// Protip: For greater readability, define this extension method:
public static class StringExtensions
{
    public static Boolean IsSet( [NotNullWhen(true)] this String? s ) => !String.IsNullOrWhiteSpace( s );
}

//

String   var1 = "3";
String   var2 = "54";
String   var3 = "Doe, John";
DateTime var4 = DateTime.UtcNow();

IQueryable<MyClass> query = await _context.Table.GetData();

////////
if( var1.IsSet() ) query = query.Where( "column1 == @0", var1 );
if( var2.IsSet() ) query = query.Where( "column2 == @0", var2 );
if( var3.IsSet() ) query = query.Where( "column3 == @0", var3 );
                   query = query.Where( "column4 == @0", var4 );
////////

var myData = query.ToList();

The above query = query.Where(...) statements will be combined as AND terms. Whereas if you want OR logic you can't use PredicateBuilder.Or because these aren't Func<T,Boolean> or Expression<Func<>> methods, but we can hack-it with .Union:

(Untested, YMMV)

String   var1 = "3";
String   var2 = "54";
String   var3 = "Doe, John";
DateTime var4 = DateTime.UtcNow();

IQueryable<MyClass> q = await _context.Table.GetData();

IQueryable<MyClass> u = q.Where( "1 = 0" ); // <-- This always-false term makes it less-awkward.

////////
if( var1.IsSet() ) u = u.Union( q.Where( "column1 == @0", var1 ) );
if( var2.IsSet() ) u = u.Union( q.Where( "column2 == @0", var2 ) );
if( var3.IsSet() ) u = u.Union( q.Where( "column3 == @0", var3 ) );
                   u = u.Union( q.Where( "column4 == @0", var4 ) );
////////

var myData = u.ToList();