-1

I have an error in my query using Entity Framework 6.0

I cannot calcule the difference between two dates.

You can see my following inner exception

FUNCTION MyDataBase.DiffDays does not exist

 using (var ctx = new xxx_Context())
 {
          query = ctx.packagings
                  .Where(p => p.supplier == idSupplier)
                  .Select(p => new Order
                  {
                       description = p.description,
                       qty = DbFunctions.DiffDays(p.date, today).Value,
                       id = p.idpackaging
                   }).ToList();      
 }
Cantinou
  • 126
  • 5
  • 21
  • It is strange enough. DiffDays(p.date, today) must be converted by EF to datediff(d, p.date, today) on server side, but error tells us that EF not mapped this functions together. From what namespace you take this function and what DBMS(MS SQL, MySQL) you use? – Slava Utesinov May 10 '16 at 07:16
  • So, you can try to create DiffDays function manually on server side and simply invoke DATEDIFF inside it. – Slava Utesinov May 10 '16 at 08:05
  • yeah, ok. I will see what is the simplest. Thanks – Cantinou May 10 '16 at 08:14
  • Maybe this can help http://stackoverflow.com/a/29539227/1236044 – jbl May 10 '16 at 16:25

2 Answers2

4

The message of the inner exception you got tells you clearly that there is no function called DiffDays in you database.

enter image description here

Hence, you should chose you database on MySQL Workbench (if you are using MySQL) and create a function called DiffDays using SQL code.

enter image description here

When you finish writing, click Apply button to add the function to your database.

On Visual Studio, keep using DbFunctions.DiffDays(dateTime1, dateTime2) as usual.

So when you run your application, YourDatabase.DiffDays will be found and used.

Sibeesh Venu
  • 18,755
  • 12
  • 103
  • 140
Sonnenuntergang
  • 51
  • 3
  • Not sure why people downvoted this solution. This is exactly what I did and it works. I am not willing to put direct SQL into EF...it's a strict policy to reduce risk of SQL injection. With that restriction, this is the best way to get aggregate data of days between dates. – Kevin Nelson Sep 12 '18 at 13:13
0

I solved my problem using Database.SqlQuery<>

DbRawSqlQuery<Order> orders;

var query = "SELECT packaging.description as description, packaging.ref as reference, DATEDIFF(@p0,CURDATE())), ...";

orders = ctx.Database.SqlQuery<Order>(query, Date, Supplier);

http://www.binaryintellect.net/articles/fbc96859-8a31-4735-baeb-7adcbc521b30.aspx

Cantinou
  • 126
  • 5
  • 21