求助：EF动态查询中DayOfWeek.ToString()引发的查询问题

Hey there! Let's break down why your current code is causing problems and how to fix it.

The Root Problem #

Your dynamic query uses ClosureDate.Value.DayOfWeek.ToString().ToUpper().Contains(...), but Entity Framework (EF) can't translate the .ToString() method (a client-side C# method) into valid SQL. Databases don't understand how to call C# enum ToString(), so this will throw a runtime error when EF tries to build the SQL query.

Solution 1: Use Database-Native Functions in Dynamic Query #

Instead of relying on C# methods, use your database's built-in functions to get the weekday name, convert it to uppercase, and perform a fuzzy match. Here's how to adjust your code for common databases:

For SQL Server:

ArrayList paramList = new ArrayList();
int paramCount = 0;
StringBuilder queryString = new StringBuilder();

if (!string.IsNullOrEmpty(keyword))
{
    // Use DATENAME to get weekday name, UPPER for case insensitivity, LIKE for contains match
    queryString.Append(" and ClosureDate IS NOT NULL and UPPER(DATENAME(weekday, ClosureDate)) LIKE '%' + @" + paramCount + " + '%'");
    paramList.Add(this.Keyword.ToUpper());
    paramCount++;
}

var abc = _context.ABC
    .Where(t => t.RecordStatus == 1)
    .OrderByDescending(t => t.Created)
    .AsQueryable();

abc = abc.Where(queryString.ToString(), paramList.ToArray());

For MySQL:

Replace the query string line with:

queryString.Append(" and ClosureDate IS NOT NULL and UPPER(DAYNAME(ClosureDate)) LIKE '%' + @" + paramCount + " + '%'");

For PostgreSQL:

Replace the query string line with:

queryString.Append(" and ClosureDate IS NOT NULL and UPPER(TO_CHAR(ClosureDate, 'Day')) LIKE '%' || @" + paramCount + " || '%'");

Solution 2: Type-Safe Query with Expression Trees (Avoid Dynamic Strings) #

If you want to avoid manual SQL string concatenation (and reduce the risk of syntax errors), use EF Core's built-in functions to build a type-safe query:

var abc = _context.ABC
    .Where(t => t.RecordStatus == 1)
    .OrderByDescending(t => t.Created)
    .AsQueryable();

if (!string.IsNullOrEmpty(keyword))
{
    var upperKeyword = "%" + keyword.ToUpper() + "%";
    // Use EF.Functions.DateName (SQL Server) - adjust for other databases if needed
    abc = abc.Where(t => 
        t.ClosureDate.HasValue && 
        EF.Functions.Like(EF.Functions.DateName("weekday", t.ClosureDate.Value).ToUpper(), upperKeyword));
}

This approach lets EF handle the SQL translation automatically, and it's easier to maintain.

Key Notes #

• Always check if ClosureDate is not null (using ClosureDate IS NOT NULL in SQL or t.ClosureDate.HasValue in LINQ) to avoid runtime errors from null values.
• Adjust the database function based on your provider (SQL Server, MySQL, etc.) since date functions vary across databases.

内容的提问来源于stack exchange，提问作者Amrendra