the binary expression cannot be converted to a predicate expression in LINQ

Question

I want to get the week number of a certain given DateTime.

public static int WeekOf(DateTime? date)
            {
                if (date.HasValue)
                {
                    GregorianCalendar gCalendar = new GregorianCalendar();
                    int WeekNumber = gCalendar.GetWeekOfYear(date.Value, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);
                    return WeekNumber;
                }
                else
                    return 0;
            }

And then I use the above method in:

public static List<ExpressionListDictionary> MyMethod(int weeknr)
        {
            using (DataAccessAdapter adapter = CreateAdapter())
            {
                LinqMetaData meta = new LinqMetaData(adapter);
                var q = (from i in meta.Test
                         where WeekOf(i.StartDate) == weeknr
                         select new ExpressionListDictionary()
                             {                                 
                                {"SomeId", i.Id}
                             }
                );
                return q.ToList();
            }
        }

And finally:

    List<ExpressionListDictionary> someIDs =  MyMethod(weeknr);
/* weeknr = 19 -> step by step debugging */

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException: The binary expression '(WeekOf(Convert(EntityField(LPLA_1.StartDate AS StartDate))) == 19)' can't be converted to a predicate expression.

I do get the title error at return q.ToList(); . How can I achieve this?

Answer 1

I haven't ever used the LLBLGen library/framework... But probably this is the same problem that happens with Entity Framework/LINQ-to-SQL: you can't put in a query C# methods: the query must be executed by your db server, not locally, and your db server doesn't know how to execute C# code. So the problem would be in the **WeekOf(i.StartDate)** == weeknr part of code (that is the only BinaryExpression of your query)

The exception you have posted is quite clear that the point of the error is the one that I have suggested. Then the reason is probably the one I gave you.

Taken from https://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=22861

If you are using SQL Server, that supports DATEPART(isowk, ...) (or if you have MySQL, that supports WEEK(...))

public class SQLFunctionMappings : FunctionMappingStore
{
    public SQLFunctionMappings()
    {
        Add(new FunctionMapping(
            typeof(SQLFunctions),
            "WeekOf",
            1,
            "DATEPART(isowk, {0})") // For SQL Server
            // "WEEK({0}, 1)") For MySQL
        );
    }
}

public class SQLFunctions
{
    public static int? WeekOf(DateTime? date)
    {
        return null;
    }
}

and then you would use it like:

Where there is the row with LinqMetaData meta = new LinqMetaData(adapter), add:

meta.CustomFunctionMappings = new SQLFunctionMappings();

and change the where:

where SQLFunctions.WeekOf(i.StartDate) == weeknr

Here there is the list of the functions already mapped by llblgen, and how to map other functions.