question

asifkhana14-0829 avatar image
0 Votes"
asifkhana14-0829 asked karenpayneoregon answered

how to compare datetime on ET?

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


sql-server-generaldotnet-entity-framework-coredotnet-entity-framework
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


There are several ways of checking the SQL statements that are generated by Entity Framework (EF): https://www.bing.com/search?q=entity+framework+show+generated+query. You can also use the SQL Server Profiler (from Tools menu of SQL Server Management Studio).

Did you check if the right queries are generated? You can also copy and execute them manually.


1 Vote 1 ·

but with EF i can't make it work

"Not working" means what in detail? No result or an error or ...?



0 Votes 0 ·
DuaneArnold-0443 avatar image
0 Votes"
DuaneArnold-0443 answered OlafHelper-2800 commented

@asifkhana14-0829

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

That's how it works in T-SQL, it don't support an other notation for date values then as a convertable string literal.

0 Votes 0 ·
karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.