SqlFunctions -> System.NotSupportedException

[pfaustinopt]

Hello!

I'm facing a problem with Effort and methods from the SqlFunctions class. In my query repository I've the following expression on a Where clause:

Where( x => x.Weekday == SqlFunctions.DatePart("weekday",date))

When running my unit tests with effort, i get the following message:
System.NotSupportedException: The specified method 'System.Nullable1[System.Int32] DatePart(System.String, System.Nullable1[System.DateTime])' on the type 'System.Data.Entity.SqlServer.SqlFunctions' cannot be translated into a LINQ to Entities store expression.

Effort does not support the usage of methods from the SqlFunctions class?

[JonathanMagnan]

Hello @pfaustinopt ,

Do you think you could provide us a test project with this issue?

It will make easier/faster for my developer to getting started for investigating it.

We now always ask for a project since we found out that most issues are missing some essential information or are resolved by the requestor when creating it

(Even if the issue seem very easy to reproduce, by getting a test project, it allow us to give a faster support and better experience for the support of all our free libraries)

Best Regards,

Jonathan

[pfaustinopt]

All you have to do is run the test method I provide.
Let me know if you have any problems running this example.

UnitTestProject1.zip

[JonathanMagnan]

Thank you for the project @pfaustinopt ,

We can successfully reproduce it.

We started to investigate how it's working with EntityFunctions:

var find = context.MyEntities.Where(x => monday < EntityFunctions.AddDays(monday, 1)).FirstOrDefault();

and we will try to replicate the code with SqlFunctions and check if that could be supported using a similar code.

Best Regards,

Jonathan

[JonathanMagnan]

Hello @pfaustinopt ,

After looking at it, we faced some more issue with your UnitTest.

Such as the DayOfWeek.Monday in c# doesn't have the same value as the weekday in SQL.

So even if we make it work, that will in fact doesn't work since the C# value you will save (int)DayOfWeek.Monday will not be equal to the value when a SQL will be used.

Let me know if that's clear or you need a better explanation.

Best Regards,

Jonathan

[pfaustinopt]

I was aware of that, that's why on my code I end up using (int)DayOfWeek.Monday + 1.

[JonathanMagnan]

Great,

We will continue to investigate it more this week. Is seem harder than we first thought.

Best Regards,

Jonathan

[yvdorofeev]

Just curious if there was any update on this.

[laleksiunas]

If anybody still stumbles upon this issue, you can try to use Day property from the System.DateTime which seems to be translated to Sql in exactly the same way.

[pilarodriguez]

Any update on this? We found the same issue using SqlFunctions.DateAdd("day", 30, date)