How to subtract two dates in LINQ?

Question

I have a problem with subtracting two dates in the MySQL database and comparing them to int numbers. I tried to use dbfunctions.diffdays however it is a conflict of types.

When I start the application I get a message:

System.ArgumentException: 'DbArithmeticExpression arguments must have a numeric common type.'

Below is my code i

    int intervalDay = 25;
    var dateCount = (from Rezerwacje in db.Rezerwacje
                     join rooms in db.rooms on Rezerwacje.PokojID equals rooms.id
                     where (rooms.floor > 0 && rooms.floor <= 2) &&(Rezerwacje.DataDo == dateTime) && ((Rezerwacje.DataDo - Rezerwacje.DataOd).Days > intervalDay)
                     select Rezerwacje).Count();
                     return dateCount;

This may be what you are after: https://stackoverflow.com/questions/9820401/dbarithmeticexpression-arguments-must-have-a-numeric-common-type — Andrew, Apr 16 '19 at 06:07
f I use Your code, I have message: System.Data.Entity.Core.EntityCommandExecutionException: 'An error occurred while executing the command definition. See the inner exception for details.' MySqlException: FUNCTION DatabaseName.DiffDays does not exist — Kamil M, Apr 16 '19 at 06:22

score 0 · Answer 1 · answered Apr 16 '19 at 06:07

0

You could use System.Data.Objects.EntityFucntions

and in the code:

where ... &&
      EntityFunctions.DiffDays(Rezerwacje.DataDo, Rezerwacje.DataOd) > intervalDay

answered Apr 16 '19 at 06:07

Arulkumar

12,966
14
47
68

If I use Your code, I have message: System.Data.Entity.Core.EntityCommandExecutionException: 'An error occurred while executing the command definition. See the inner exception for details.' MySqlException: FUNCTION DatabaseName.DiffDays does not exist – Kamil M Apr 16 '19 at 06:19
This is because EntityFunctions exposes Linq 2 Entities methods, not linq 2 sql. Use DbFunctions.DiffDays instead. – DevilSuichiro Apr 16 '19 at 07:45

score 0 · Answer 2 · answered Apr 16 '19 at 06:17

0

 using System.Data.Entity;

 //code in LINQ
 ... DbFunctions.DiffDays(Rezerwacje.DataDo, Rezerwacje.DataOd) >= intervalDay

answered Apr 16 '19 at 06:17

Seven

134
3

score 0 · Accepted Answer · answered Apr 16 '19 at 11:43

Ultimately I use List and foreach.

Downloading data from database:

public int DateCleanFloor1to2(DateTime dateTime)
{
            var dateList = (from Rezerwacje in db.Rezerwacje
                            join rooms in db.rooms on Rezerwacje.PokojID equals rooms.id
                            where (rooms.floor > 0 && rooms.floor <= 2) && (Rezerwacje.DataDo == dateTime)
                            select Rezerwacje).ToList();

            return SubtractDateToDateFrom(dateList, dateTime.AddDays(25));
 }

And method with foreach loop:

public int SubtractDateToDateFrom(List<Rezerwacje> dateList, DateTime dateTime)
{
            int count = 0;
            foreach (var item in dateList)
            {
                if (item.DataDo < dateTime)
                {
                    count++;
                }
            }

            return count;
}

@DevilSuichiro @Arulkumar @Seven

How to subtract two dates in LINQ?

3 Answers3