With the public announcement of the .NET 3.5 SP1 Beta, users of LINQ to SQL must be wondering what it has to offer. In addition to fixes for some of the issues identified by consumers of this technology since it was first released last year, the primary new feature area added to LINQ to SQL is support for some of the new data types in SQL Server 2008. In this blog, I will outline with examples how to use the new data types in LINQ to SQL SP1.

 

New Data Types in SQL Server 2008

SQL Server 2008 introduces several new data types, including Date, Time, DateTime2, DateTimeOffset, and some others. Check out http://technet.microsoft.com/en-us/magazine/cc434692.aspx and http://msdn.microsoft.com/en-us/library/ms186724(SQL.100).aspx for additional information.

LINQ to SQL introduces support for the following new data types:

·         Date

·         Time

·         DateTime2

·         DateTimeOffset

·         Filestream

Support for Filestream is limited, in that it is mapped as a byte array without any streaming facility, and for the remainder of this blog, I’m going to focus on the Date/Time types and how you can use them.

The code fragments that follow were tested against CTP6 of SQL Server 2008.

Mapping the new Date/Time Types

By default, the DateTime CLR type in an entity class will map to a column of the SQL type DATETIME, just as it does against SQL Server 2005. However, you can specify the DbType attribute argument in the Column attribute to override the default.

The Time data type is more interesting. LINQ to SQL maps the difference between two DateTime values to a CLR TimeSpan type. For example:

   DateTime dt = new DateTime(1960, 01, 01);

   var query = from e in db.Employees select e.BirthDate.Value - dt;

   List<TimeSpan> list = query.ToList();

 

However, if you have a column of type TIME in your SQL Server 2008 database, you can explicitly map it to a TimeSpan member of an entity class using the DbType attribute argument.

Here’s a class with the new Date/Time types mapped to columns in the database. Note that the CLR DateTime can accommodate the entire range of values allowed in the new SQL DateTime2 type. Also note that there is already a first-class mapping for the SQL DateTimeOffset available in the CLR.

 

    [Table]

    public class DateTimeTypes

    {

        [Column(IsPrimaryKey=true, CanBeNull=false)]

        public int Col_Id;

 

        [Column(CanBeNull=false, DbType = "DATE")]

        public DateTime Col_Date;

 

        [Column(CanBeNull = false, DbType = "TIME(7) NOT NULL")]

        public TimeSpan Col_Time;

 

        [Column(CanBeNull=false)]

        public DateTime Col_DateTime2;

 

        [Column(CanBeNull=false)]

        public DateTimeOffset Col_DateTimeOffset;

 

        [Column(CanBeNull = true, DbType = "DATE")]

        public DateTime? Col_Nullable_Date;

 

        [Column(CanBeNull = true, DbType = "TIME(7) NULL")]

        public TimeSpan? Col_Nullable_Time;

 

        [Column(CanBeNull=true)]

        public DateTime? Col_Nullable_DateTime2;

 

        [Column(CanBeNull=true)]

        public DateTimeOffset? Col_Nullable_DateTimeOffset;

    }

 

If you have a TIME column mapped to a TimeSpan object, you can invoke the usual TimeSpan properties and expect the appropriate results:

 

    List<long> ticks = db.DateTimeTypes.Select(c => c.Col_Time.Ticks).ToList();

 

    List<int> days = db.DateTimeTypes.Select(c => c.Col_Time.Days).ToList();

 

    List<int> hours = db.DateTimeTypes.Select(c => c.Col_Time.Hours).ToList();

 

    List<int> mins = db.DateTimeTypes.Select(c => c.Col_Time.Minutes).ToList();

 

    List<int> sec = db.DateTimeTypes.Select(c => c.Col_Time.Seconds).ToList();

 

    List<int> millisec = db.DateTimeTypes.Select(c => c.Col_Time.Milliseconds).ToList();

 

The corresponding TimeSpan.TotalXXX properties also work as expected.

Aggregate operators are also supported, as the example below illustrates:

   DateTimeOffset max1 = db.DateTimeTypes.Select(c => c.Col_DateTimeOffset).Max();

   DateTimeOffset max2 = db.DateTimeTypes.Max(c => c.Col_DateTimeOffset);

 

   Assert.IsTrue(max1 == max2);

 

In general, we have added support for “natural” use of the new Date/Time types, so that you can write queries like the following:

   var query = from x in db.Table_With_DateTime2_Column

               where x.Col_DateTime2.Year > 0

               group x by x.Col_DateTime2 into c

               select c.Key;

 

   foreach (DateTime dt in query)

                // ...

 

Refer to the documentation accompanying the SP1 Beta for details on the new data types in LINQ to SQL.

New SqlMethods Members

The SqlMethods class in the System.Data.Linq.SqlClient namespace contains some static methods that allow access to SQL Server functionality that is not otherwise available. For example, the methods DateDiffDay(), DateDiffMonth(), and DateDiffYear() correspond to the DATEDIFF() function in T-SQL.

In SP1, new overloads of these methods have been introduced. The methods can now accept DateTimeOffset as arguments:

  public static int DateDiffYear(DateTimeOffset startDate, DateTimeOffset endDate);

  public static int? DateDiffYear(DateTimeOffset? startDate, DateTimeOffset? endDate);

   // and others…

Here’s an example of how you’d use these methods:

    var query = from d in db.DateTimeOffsets

                where Math.Abs(SqlMethods.DateDiffMinute(d.Startdate, d.EndDate.Value)) <= 1

                select new {

                  start = d.Startdate,

                  end = d.EndDate,

                  diff = SqlMethods.DateDiffSecond(d.Startdate, d.EndDate)

               };

 

Additionally, now that SQL Server has support for higher-precision time components (e.g., DateTime2 and DateTimeOffset offer values to within an accuracy of 100 nanoseconds), new members were added to the SqlMethods class:

   public static int DateDiffNanosecond(DateTime startDate, DateTime endDate);

   public static int? DateDiffNanosecond(DateTime? startDate, DateTime? endDate);

   public static int DateDiffNanosecond(DateTimeOffset startDate, DateTimeOffset endDate);

   public static int? DateDiffNanosecond(DateTimeOffset? startDate, DateTimeOffset? endDate);

 

Needless to say, the new Nanoseconds methods are for use only against store types that offer the corresponding precision.

Other Improvements

In addition to support for the new data types, SP1 also brings fixes for some bugs and performance issues identified since its release last year. Most of these changes were made in direct response to feedback we have received via several channels, including the forums, the Connect site, and other means. We are pleased to see the adoption of the technology on the rise and will continue to be responsive to the users’ needs.

Feedback

I welcome your feedback on anything and everything related to LINQ to SQL and the Entity Framework. Several members of my team and I read the MSDN Forums and attempt to answer questions and concerns raised by users of this technology. Your comments really do matter…keep them coming!

Thank you.

Samir Bajaj

Software Design Engineer

Entity Framework/LINQ to SQL