Linq grouping by nullable datetime and using this as criteria

Question

I am struggling with a nullable datetime column [DateInsp] in an ASP.NET app which uses SubSonic3, Linq, MS SQL Server 2005.

I had this all working when the datetime column [DateInsp] did not allow nulls. A new requirement forced me to set the [DateInsp] column to allow nulls and now I am struggling getting this piece of functionality to work properly again.

Problem 1: I need to first render a dropdown list of the 7 most recent inspection dates for a given inspector (this is a grouped list of the 7 most recent dates for the inspector). Here is the TSQL that I need to convert to Linq syntax:

declare @InspectorID varchar(5)
set @InspectorID = 'GPA'

select top 7 convert(nvarchar(30), [DateInsp], 101) InspectedDate
from [dbo].[IncomingInspection]
group by [InspectorID], convert(nvarchar(30), [DateInsp], 101)
having [InspectorID] = @InspectorID
order by convert(nvarchar(30), [DateInsp], 101) desc

If I can't get this work properly using Linq, BUT I can/could build a stored proc to return the list of dates and throw that into a dropdown. Fair enough. I've been fighting with the Linq syntax so any help in this area is greatly appreciated.

Problem 2: I need to use the selected date in the dropdown mentioned above to pull the correct records for this inspector and the correct date. Again, this is a nullable datetime field and this is real sticking point for me.

Here was the original Linq syntax that accomplished the requirement before I had to change the datetime field to allow Nulls:

    Dim query = (From i In db.IncomingInspections _
        Where i.InspectorID = User.Identity.Name _
        Group By Key = New With {i.DateInsp} Into Group _
        Order By Key.DateInsp Descending _
        Select Key = New With {.DateInsp = Key.DateInsp.ToShortDateString}).Take(7)

This app is written in VB.NET and uses some Linq syntax which both make my head hurt. If you can offer C# syntax to accomplish this I can translate it to VB.NET myself.

EDIT:

For some reason I can't use the .Value property; I get: The member 'Value' is not supported

I get: "Incorrect syntax near '<'." if I try to add the date comparison to the where clause too.

END EDIT

Thanks Much,

Josh

Answer 1

Sorry to answer in C#, but here's my interpretation of your queries:

Problem #1.

You use having in the question, as well as convert the datetimes to strings. I didn't see a need for either of those operations.

string inspectorId = "GPA";

List<DateTime?> someDates = db.IncomingInspection
  .Where(insp => insp.InspectorId == inspectorId)
  .Select(insp => insp.InspectedDate)
  .Distinct()
  .OrderByDescending(d => d)
  .Take(7)
  .ToList();

Problem #2.

There's two cases: either you have a null from the user or you have a value. It is simple to write a query to target each case. So simple, that I feel like I've misread the intention here.

DateTime? selectedDate = control.SelectedValue;

List<IncomingInspection> someRecords = null;

if (selectedDate.HasValue())
{
  DateTime selectedDateValue = selectedDate.Value;
  someRecords = db.IncomingInspection
    .Where(insp => insp.InspectorId == inspectorId)
    .Where(insp => insp.InspectedDate == selectedDateValue)
    .ToList();
}
else
{
  someRecords = db.IncomingInspection
    .Where(insp => insp.InspectorId == inspectorId)
    .Where(insp => insp.InspectedDate == null)
    .ToList();
}

Answer 2

I went ahead and used 2 stored procedures to solve this problem eliminating the need for Linq syntax here:

Here is the TSQL that I used to get a list of the last 7 Inspection Dates (w/ only date portion of the datetime field:

select top 7 convert(nvarchar(30), [DateInsp], 101) InspectedDate
from [dbo].[IncomingInspection]
group by [InspectorID], convert(nvarchar(30), [DateInsp], 101)
having [InspectorID] = @InspectorID
order by convert(nvarchar(30), [DateInsp], 101) desc

Here is the tsql that I used to grab a filtered list of inspection records for the inspector and the inspection date selected in the dropdown (using only the date portion in the criteria statement):

select [ID]
,[InspectorID]
,[DateInsp]
-- (more fields here)
from [dbo].[IncomingInspection]
where [InspectorID] = @InspectorID and DATEADD(dd, 0, DATEDIFF(dd, 0, [DateInsp])) = DATEADD(dd, 0, DATEDIFF(dd, 0, @DateFilter))

Sometimes I just have to go with works rather than what is new and shiny. I have struggled with the simplest things in Linq on occasion and I think it is both because Linq is new to me and I beat my head on VB.NET syntax, especially when combining it with Linq.