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?

From the name of the classes, are you using the *llblgen* library? — xanatos, May 18 '15 at 07:22
maybe adding brackets will help in this case. Like: where (WeekOf(i.StartDate) == weeknr) — madoxdev, May 18 '15 at 07:28

xanatos · Accepted Answer · 2015-05-18T09:08:55.037

3

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.

edited May 18 '15 at 09:08

answered May 18 '15 at 07:26

xanatos

109,618
12
197
280

xanatos, yep I also think the problem is there ... And, now, I'm going back to my initial problem: how to get using LINQ, the week number of a certain given datetime? – Florin M. May 18 '15 at 07:29
Could I use: SqlFunctions.DatePart ? But I can't even resolve this method, adding some certain namespace... – Florin M. May 18 '15 at 07:39
@FlorinM. If you need other SQL functions, you can easily adapt that code to the other SQL functions. The `1,` is the number of parameters that will be passed (so if you want to have two parameters in the function, you put `2`) – xanatos May 18 '15 at 09:11
Thanks for your great tips and informations. I really hope this question/answer will help many others – Florin M. May 18 '15 at 09:13
If I want the week to begin from sunday ( not monday ) how to modify it ? – Florin M. May 27 '15 at 12:34
@FlorinM. Don't know... Best thing is that you ask a new question on how to do it in the SQL dialect that you are using, and then adapt the response to the `SQLFunctionMappings` (that is quite generic) – xanatos May 27 '15 at 12:37
@FlorinM. For MySQL it is easy: use `"WEEK({0}, 4)"`. For SQL Server I don't know – xanatos May 27 '15 at 12:39

score 0 · Answer 2 · answered May 18 '15 at 07:52

you can try to Make the method you have WeekOf takes string instead of Datetime?

public static int WeekOf(String dateAsString)
            {
               //if (!string.IsNullOrEmpty(dateAsString))
                if (!dateAsString.equals(string.empty))
                {
                    GregorianCalendar gCalendar = new GregorianCalendar();
                    int WeekNumber = gCalendar.GetWeekOfYear(date.Value,  CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);
                    return WeekNumber;
                }
                else
                    return 0;
            }

And then you use the above below 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 i.startDate != null && WeekOf(i.StartDate.tostring()) == weeknr
                         select new ExpressionListDictionary()
                             {                                 
                                {"SomeId", i.Id}
                             }
                );
                return q.ToList();
            }
        }

i afraid i don't understand, no need to change the weeknr to string as you are comparing weeknr(int) with weekof( function return int). — Hussein Khalil, May 18 '15 at 07:59

score 0 · Answer 3 · answered May 18 '15 at 07:57

Try something like this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Globalization;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            MyMethod(5);
        }
        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;
        }
        public static List<ExpressionListDictionary> MyMethod(int weeknr)
        {
            using (DataAccessAdapter adapter = CreateAdapter())
            {
                LinqMetaData meta = new LinqMetaData(adapter);
                List<ExpressionListDictionary> q = (from i in meta.Test
                         where WeekOf(i.StartDate) == weeknr
                         select new ExpressionListDictionary()
                         {
                                Id = "SomeId"
                         }
                ).ToList();
                return q;
            }
        }
        public static DataAccessAdapter CreateAdapter()
        {
            return new DataAccessAdapter();
        }
    }
    public class ExpressionListDictionary
    {
        public string Id { get; set; }
    }
    public class LinqMetaData
    {
        public List<LinqMetaData> Test {get;set;}
        public DateTime StartDate {get;set;}
        public int Id { get; set; }
        public LinqMetaData(DataAccessAdapter adapter)
        {
        }
    }
    public class DataAccessAdapter : IDisposable
    {

        public void Dispose()
        {
        }
    }


}

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

3 Answers3