Binary expression cannot be converted to predicate expression in LINQ

Question

I want to get the week number of a specific 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. View the stack trace for more information about the error and its occurrence in the code.

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

I get a header error when returning q.ToList () ;, How can I achieve this?

Answer 1

I have never used the LLBLGen library / structure ... But this is probably the same problem that occurs with Entity Framework / LINQ-to-SQL: you cannot use C # query methods: the query should 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 **WeekOf(i.StartDate)** == weeknr part of the code (this is the only BinaryExpression your request)

The exception that you posted is very clear that the point of error is the one I proposed. 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 will use it like:

Where there is a line with LinqMetaData meta = new LinqMetaData(adapter) , add:

 meta.CustomFunctionMappings = new SQLFunctionMappings(); 

and change where :

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

Here is a list of functions already displayed by llblgen, and how to map other functions.

Answer 2

can you try to make the method that you have WeekOf accept 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 following:

 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(); } } 

Answer 3

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() { } } }​