Can anyone convert this SQL query into a C# Linq statement?

Question

I have a SQL query which is as follows:

SELECT [ClientId]
  ,[LastDelivery]
  ,[LastRequisitionDate]
  ,Datediff (day, LastRequisitionDate, LastDelivery) as DiffDate
  FROM [dbo].[App_Client]
  where  (LastDelivery != '1900-01-01 00:00:00' 
  and LastRequisitionDate != '1900-01-01 00:00:00') 
  and Inactive = 0
  and (Datediff (day, LastRequisitionDate, LastDelivery) < 9)
  and (Datediff (day, LastRequisitionDate, LastDelivery) >= 0)

I have a list of clients, and I want all clients who received their delivery within 8 days of their requisition. The reason for the

LastDelivery != '1900-01-01 00:00:00' 
and LastRequisitionDate != '1900-01-01 00:00:00'

is because the way I have my c# query requires that there are no null fields in any of the date fields(those fields are nullable in the DB but they really shouldn't be). I have a DB containing 11838 clients, and this query returns 10404. My problem is I have been unable to duplicate this query with C# linq.

My C# query is as follows:

var clients = _clientService.GetAllClients().Where(x => 
(x.LastDelivery != Convert.ToDateTime("01/01/1900")
&& x.LastRequisitionDate != Convert.ToDateTime("01/01/1900"))
&& x.Inactive == 0
&& (((DateTime)x.LastDelivery - (DateTime)x.LastRequisitionDate).Days < 9)
&& (((DateTime)x.LastDelivery - (DateTime)x.LastRequisitionDate).Days >= 0)).ToList();

This query returns 10563 results, a difference of 159, and I cannot figure out at all where I'm going wrong. To me that C# query looks identical to the SQL one, but somewhere there is a discrepancy. I've been coding all day, so maybe I'm a bit burnt out and the solution is obvious, but I just can't see it. Can anyone help, or suggest what I may be doing wrong or overlooking?

Answer

As correctly pointed below by Matt Smith, it turns out both queries were correct - the discrepancy lay in the SQL DateDiff function, which measures 1 day as when the day passes midnight, which means comparing 01/01/2016 23:59:59 and 01/02/2016 00:00:01 gives a difference of one day, whereas in my C# query, it was comparing actual difference in days as a timespan (24hrs). Great find and important distinction to be made, thanks to Matt Smith.

Are you working with EntityFramework or some other similar IQueryable based system? If you are you may want to move this filter "lower on the call stack", you appear to be working with a IEnumerable because you do stuff that would not be allowed in EF, because of this your database is doing a select with no filter, returning all rows, then you are filtering in memory which is going to give you bad performance. — Scott Chamberlain
– Scott Chamberlain, Commented Mar 7, 2016 at 16:27
Do you have time components in your date values? If so, there are important differences between the way DateDiff works in SQL, where DateDiff(day, '03/06/2016 23:59:59', '03/07/2016 00:00:01') = 1, and .NET where ('03/07/2016 23:00:00' - '03/06/2016 23:59:00').Days = 0. DateDiff(day,x,y) measures the number of Day boundaries crossed. TimeSpan.Days returns the number of Days portion of the difference. — Matt Smith
– Matt Smith, Commented Mar 7, 2016 at 16:48
Okay, I've posted it as an answer. @ailinmcc666, care to check it off? — Matt Smith
– Matt Smith, Commented Mar 7, 2016 at 18:41

Matt Smith · Accepted Answer · 2016-03-08 20:48:07Z

2

Do you have time components in your date values? If so, there are important differences between the way DateDiff works in SQL, where DateDiff(day, '03/06/2016 23:59:59', '03/07/2016 00:00:01') = 1, and .NET where ('03/07/2016 23:00:00' - '03/06/2016 23:59:00').Days = 0. DateDiff(day,x,y) measures the number of Day boundaries crossed. In .NET, the subtraction of DateTimes returns a TimeSpan of days, hours, minutes, seconds, milliseconds, and TimeSpan.Days returns the number of days in the TimeSpan.

edited Mar 8, 2016 at 20:48

answered Mar 7, 2016 at 18:39

Matt Smith

6366 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jedediah · Accepted Answer · 2016-03-07 16:50:10Z

2

Since I don't have your data, I can't be sure this works, but give this a go. It should at least give you something to go with

    var clients = (from client in _clientService.GetAllClients()
                    let minDate = DateTime.MinValue
                    let lastRequisitionDate = (DateTime)client.LastRequisitionDate
                    let lastDeliveryDate = (DateTime)client.LastDeliveryDate
                    let lastDelivery = (DateTime)client.LastDelivery
                    where lastRequisitionDate != minDate && lastDelivery != minDate && client.Inactive != 0 && (lastDelivery - lastRequisitionDate).Days < 9 && (lastDelivery - lastRequisitionDate).Days >= 0
                    select client).ToList();

You can also potentially make things easier on yourself by changing your Client entity to allow for nullable dates:

public class Client
{
    public DateTime? LastRequisitionDate {get; set;}
}

edited Mar 7, 2016 at 16:50

answered Mar 7, 2016 at 16:38

Jedediah

1,94417 silver badges33 bronze badges

5 Comments

necrofish666 Over a year ago

Thanks for the response, tried this but it didn't work :(

Jedediah Over a year ago

As in threw an exception didn't work, or "worked" but still returning an incorrect number of clients?

necrofish666 Over a year ago

sorry - it worked, but still returned the incorrect amount.

Jedediah Over a year ago

Odd. If you're really sure the number is incorrect, as @ailinmcc666 mentioned, it would probably just save you more time to just use a stored procedure

necrofish666 Over a year ago

check out the comment above by Matt Smith, he seems to have hit upon what the problem may be.

AlirezaP · Accepted Answer · 2016-03-07 16:56:38Z

1

    var clients = (from a in _clientService.GetAllClients().ToList()
               where
               (a.LastDelivery != DateTime.Parse("1900-01-01 00:00:00")
               &&
               a.LastRequisitionDate != DateTime.Parse("1900-01-01 00:00:00"))
               && ((a.LastRequisitionDate - a.LastDelivery).Days < 9)
               && ((a.LastRequisitionDate - a.LastDelivery).Days >= 0)
               && a.Inactive == 0
               select a).ToList();

answered Mar 7, 2016 at 16:56

AlirezaP

242 bronze badges

Collectives™ on Stack Overflow

Can anyone convert this SQL query into a C# Linq statement?

Answer

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Answer

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related