How not to calculate the databases UTC offset using LINQ to SQL

Spent a bit of time debugging a subtle issue with calculating the UTC offset for datetimes stored in a SQL server. The code in question needed to determine the timezone difference between the database server and UTC.

Problematic code that should be avoided when calculating the UTC offset

using (DatabaseDataContext db = new DatabaseDataContext())
{
 DateTime current = db.ExecuteQuery("SELECT GETDATE()").First();
 DateTime utcCurrent = db.ExecuteQuery("SELECT GETUTCDATE()").First();

 TimeSpan differece = utcCurrent - current;

 double diff = differece.Hours;

 logger.DebugFormat("current:{0}, utcCurrent:{1}, differece:{2}, diff:{3}", current, utcCurrent, differece, diff);
}

This produces the following. Notice the diff toggling between -11 and -12 based on the timing of the calls.
DEBUG - current:05/05/2011 11:32:06, utcCurrent:05/04/2011 23:32:06, differece:-11:59:59.9970000, diff:-11
DEBUG - current:05/05/2011 11:32:06, utcCurrent:05/04/2011 23:32:06, differece:-12:00:00, diff:-12
DEBUG - current:05/05/2011 11:32:06, utcCurrent:05/04/2011 23:32:06, differece:-11:59:59.9830000, diff:-11
DEBUG - current:05/05/2011 11:32:06, utcCurrent:05/04/2011 23:32:06, differece:-11:59:59.9900000, diff:-11
DEBUG - current:05/05/2011 11:32:06, utcCurrent:05/04/2011 23:32:06, differece:-11:59:59.9930000, diff:-11

Fixed query to get the offset in a single call

 ExecuteQuery("SELECT DATEDIFF(hour,GETDATE(),GETUTCDATE())").First();

Note that this doesn't handle the case where the offset may include partial hours.

See Also:

• Stack Overflow: Can you round a .NET TimeSpan object?

LINQ to SQL schema issues

The following is a collection of links and information for resolving issues I've encountered when using LINQ to SQL.

• Stack Overflow:SqlMetal wrongly generates the return type of my stored proc (LINQ)
• MSDN: SqlMetal.exe (Code Generation Tool)
• S is for… sqlmetal
• SqlMetal Include
• LINQ To SQL - "String must be exactly one character long.."
• Disabling a Trigger for a LINQ to SQL DataContext
• LINQ to SQL "in" support in Transact-SQL

LINQ To SQL - "String must be exactly one character long.."

I used LINQ to SQL to throw together a quick data access layer to a third party database. In theory in will only be a temporary measure.

When trying to retrieve a record I got the following error:

System.FormatException: String must be exactly one character long..
System.Convert.ToChar(String value, IFormatProvider provider)
System.String.System.IConvertible.ToChar(IFormatProvider provider)
System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
System.Data.Linq.DBConvert.ChangeType(Object value, Type type)
Read_Name(ObjectMaterializer`1 )
MoveNext()
...

Turns out the issue is related to a VarChar(1) NOT NULL column on the table. The Code Generation has interpreted this as char (System.Char). Changing this to string (System.String) got the system going again. There is some risk now of assigning a value longer than one charater but I can live with that for a temporary solution.

Disabling a Trigger for a LINQ to SQL DataContext

I have a trigger on a table that fires on updates and adds a record to another table indicating that it needs to be rolled up to an external system. In a few cases I need to dynamically suppress this trigger to prevent circular updates between the two systems. My data access is via LINQ to SQL for the system in question.

The article Disabling a Trigger for a Specific SQL Statement or Session deals with the same problem.

using(DbDataContext dc = new DbDataContext())
{
 //Update fields etc...

 dc.Connection.Open();
 //Set the context_info here within the open connection so that it is in-scope 
 //for the triggers
 dc.ExecuteCommand("SET Context_Info 0x55555");
 dc.SubmitChanges();
 dc.Connection.Close();
}

 CREATE TRIGGER TR_Mock ON dbo.TableForTrigger FOR UPDATE
 AS 
 DECLARE @Cinfo VARBINARY(128) 
 SELECT @Cinfo = Context_Info() 
 IF @Cinfo = 0x55555 
 RETURN 
 PRINT 'Trigger Executed' 
 -- Actual code goes here
GO  

See Also:

• Stack Overflow: Why is my CONTEXT_INFO() empty?

LINQ to SQL "in" support in Transact-SQL

See Stack Overflow: Does LINQ to SQL support the t-sql “in” statement