how to compare datetime on ET?

Question

It is a API for a saleregister web. I want to select the sales of the pastweek, with sql this work for me

SELECT total, date FROM sale WHERE sale.idUser = 3 AND sale.date > '2020/06/16 01:42:45.270' 

and that return some sales, but with EF i can't make it work(the date on the query lastweek, is the same on EF) I think that the problem is the

&& saleq.Date >lastweek

on EF, i tried to do it in diferent querys one to get the sales of a user, and another to take the sales of the past week with the value of the first EF query, but didn't work

DateTime lastweek = DateTime.Today;
lastweek = lastweek.AddDays(-7);
                        var query = from saleq in db.Sales
                                    where saleq.IdUser == 3 && saleq.Date >lastweek
                                    orderby saleq.Date descending
                                    select saleq;
                        var lst = query.ToList();

the model on the api is the same on the database, when I use it to ask for all sales (without the date) it works

Answer 1

@asifkhana14

SELECT total, date FROM sale WHERE sale.idUser = 3 AND sale.date > '2020/06/16 01:42:45.270'

You seem to be making the date comparison with string data.

So why can't you format the dates to string format and make the comparisons in using them in the Linq query?

lastweek = DateTime.Today();
lastweek = lastweek.AddDays(-7)

string strlastweek = lastweek.Tostring("yyyy/mm/ddHHmmss")

sb.Date.Tostring("yyyy/mm/ddHHmmss") > strlastdate ---in the Linq query.

https://www.c-sharpcorner.com/blogs/date-and-time-format-in-c-sharp-programming1
I

Answer 2

Seems like working solely with date without time would be the true test. The following is done with Entity Framework Core.

Table structure

CREATE TABLE [dbo].[Sales](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [SaleDate] [datetime2](7) NULL,
    [ShipCountry] [int] NULL,
 CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Sales] ADD  CONSTRAINT [DF_Sales_SaleDate]  DEFAULT (getdate()) FOR [SaleDate]

Test queries

SELECT Id, 
       SaleDate, 
       ShipCountry
FROM dbo.Sales;

DECLARE @LastWeek NVARCHAR(10)= '2021-06-18';

SELECT Id, 
       SaleDate, 
       ShipCountry
FROM dbo.Sales
WHERE CONVERT(DATE, SaleDate) > CAST(@LastWeek AS DATE)
      AND dbo.Sales.ShipCountry = 1;

Using the second query above coupled with a unit test you can validate the results. In this case two records are expected to be returned.

using System;
using System.Linq;
using EntityFrameworkLibrary.Data;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using SalesUnitTestProject.Base;

namespace SalesUnitTestProject
{
    [TestClass]
    public partial class MainTest : TestBase
    {
        [TestMethod]
        public void TestMethod1()
        {
            var saleDate = new DateTime(2021, 6, 25);
            var shipCountry = 1;
            var expectedCount = 2;

            saleDate = saleDate.AddDays(-7);

            using DatabaseContext context = new DatabaseContext();

            var results = context
                .Sales
                .Where(sales => sales.SaleDate.Value.Date > saleDate.Date && 
                                sales.ShipCountry == shipCountry)
                .ToList();

            Assert.AreEqual(results.Count, expectedCount);
        }

    }
}

All the above is very basic, we could get into mocking and using a class such as the following for the current date.

public class DateTimeProvider : IDisposable
{
    private static readonly AsyncLocal<DateTime?> _injectedDateTime = new();

    /// <summary>
    /// Gets DateTime now.
    /// </summary>
    /// <value>
    /// The DateTime now.
    /// </value>
    public static DateTime Now => _injectedDateTime.Value ?? DateTime.Now;

    /// <summary>
    /// Injects the actual date time.
    /// </summary>
    /// <param name="actualDateTime">The actual date time.</param>
    public static IDisposable InjectActualDateTime(DateTime actualDateTime)
    {
        _injectedDateTime.Value = actualDateTime;

        return new DateTimeProvider();
    }

    public void Dispose()
    {
        _injectedDateTime.Value = null;
    }
}