F# LINQ deal with nullable value in tuple

Question

I am learning LINQ with F# 3.0. I want to know how to deal with nullable values.
I have created a simple data table in SQL Server 2008 R2, the database name is myDatabase.

CREATE TABLE [dbo].[Holidays] (
    [Id]           INT        NOT NULL,
    [DateOff]      [datetime] NOT NULL ,
    [HolidayName]  [nvarchar] (30) NOT NULL
);
USE MyDatabase

INSERT INTO Holidays (Id, DayOff, HolidayName)
VALUES(1, '1/2/2012', 'New Years Day')
INSERT INTO Holidays (Id, DayOff, HolidayName)
VALUES(2, '1/16/2012', 'Martin Luther King Day')
INSERT INTO Holidays (Id, DayOff, HolidayName)
VALUES(3, '2/20/2012', 'Washington Birthday')
INSERT INTO Holidays (Id, DayOff, HolidayName)
VALUES(4, '4/6/2012',  'Good Friday')
INSERT INTO Holidays (Id, DayOff, HolidayName)
VALUES(5, '5/28/2012', 'Memorial Day')
INSERT INTO Holidays (Id, DayOff, HolidayName)
VALUES(6, '7/4/2012', 'Independence Day')
INSERT INTO Holidays (Id, DayOff, HolidayName)
VALUES(7, '9/3/2012', 'Labor Day')
INSERT INTO Holidays (Id, DayOff, HolidayName)
VALUES(8, '11/22/2012', 'Thanksgiving Day')
INSERT INTO Holidays (Id, DayOff, HolidayName)
VALUES(9, '12/25/2012', 'Christmas')
GO

My code in F# to check if today is holiday and if so, return the holiday name.

#light
open Microsoft.FSharp.Data.TypeProviders
open Microsoft.FSharp.Linq
open System
open System.Collections.Generic
open System.Data
open System.Data.Linq
open System.Linq
#if DEBUG
System.Linq.Enumerable.Count([]) |> ignore
#endif

[<Literal>]
let connString = "Data Source=.;Initial Catalog=myDatabase;Integrated Security=True"
[<Generate>]
type dbSchema = SqlDataConnection<connString>
let db = dbSchema.GetDataContext()

let dbHoliday(checkDate: DateTime) =
    let mutable holidayName = ""
    let dayOff, dayName =
                  query {
                        for day in db.Holiday do
                        where (day.DateOff = checkDate)
                        select (day.DateOff, day.HolidayName)
                        lastOrDefault
                        }
    if (dayOff <> DateTime.MinValue) then
        holidayName <- dayName
        (true, holidayName)
    else
        (false, holidayName)

let todayOff = dbHoliday(DateTime.Today)

Yesterday, my program worked, as it was Feb. 20, 2012, which was Washington Birthday
But today, it doesnot work.
I got error:
System.NullReferenceException was unhandled
  Message=Object reference not set to an instance of an object.
  Source=mscorlib
  StackTrace:
       at System.Tuple`2.get_Item1()
       at Program.dbHoliday(DateTime checkDate)

I want to return a tuple of 2 elements to the caller.
Like (true, "Washington Birthday") or (false, "")
I think another way could work:
let dbHoliday(checkDate: DateTime) =
    let mutable holidayName = ""
    let dayOffCount =
              query {
                    for day in db.Holiday do
                    where (day.DateOff = checkDate)
                    count
                    }
    if (dayOffCount = 1) then
        let dayOff, dayName =
                      query {
                            for day in db.Holiday do
                            where (day.DateOff = checkDate)
                            select (day.DateOff, day.HolidayName)
                            lastOrDefault
                            }

But this way seems to be not elegant.
Anyone has a better idea?
Thanks!

Answer 1

The problem you are facing comes from the fact that 'null' is not a proper value for tuples.

The solution you suggest aside from not being "elegant" also suffers from the fact that you end up making 2 trips to the databased and enumerating twice, which may not be critical in your simple case, but for sure something to keep in mind in real world code.

Have you considered something like this?

open System.Linq

let dbHoliday(checkDate: DateTime) =
    // Build a query: note that there's not trip to the DB by doing this
    let intq =
        query {
            for day in db.Holidays do
            where (day.DateOff = checkDate)
            select (day.DateOff, day.HolidayName)
        }

    // The code below enumerates the query we wrote above.
    // If the query above is empty, then enumerate over the sigleton 
    // sequence that contains our own 'default' value: this way we
    // stay away from 'null' which is not a proper value for tuples.
    // Note: you need to open System.Linq to get .DefaultIfEmpty()
    let dayOff, dayName =
        query { for x in intq.DefaultIfEmpty((DateTime.MinValue, "")) do 
                    lastOrDefault }

    // Finally return the tuple based on our current business logic
    (dayOff <> DateTime.MinValue, dayName)

Hope it helps.
-Matteo

---

-Matteo

Answer 2

Hello,

Thank you very very much.

Not only did you offer good code, but also you provided excellent explanations, which makes me understand the issue much better than before.

Thanks again!

John