Technically Speaking

Wednesday, May 14, 2008 10:26 AM

What’s New in LINQ to SQL SP1

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

Posted by Samir Bajaj | 2 Comments

Filed under: LINQ to SQL, SQL Server 2008

Wednesday, April 02, 2008 10:18 AM

TPT with LINQ to SQL

LINQ to SQL has been gaining popularity since its release last year, and if blogs and forum discussions are any indicator, developers are really pleased with this technology and have been putting it to use in novel ways.

At the same time, I’ve seen questions around features and functionality that LINQ to SQL doesn’t offer—one of them being support for inheritance models other than TPH (Table per Hierarchy). Before I discuss that further, I will reiterate some of my comments from an earlier post, specifically, around how LINQ to SQL compares with the Entity Framework.

You can find a fair amount of information on the capabilities of LINQ to SQL versus those of the Entity Framework, but let me just say that each technology has specific strengths that make it more appealing in some scenarios over the other. For example, LINQ to SQL would be your best bet in a RAD-like environment with SQL Server as the storage back-end. In Enterprise scenarios that demand more flexibility, the Entity Framework would be the technology of choice.

Inheritance Hierarchy and O/R Mapping

As you may already know, there are three primary strategies for mapping object hierarchies to tables in a relational store.

TPH

In a Table per Hierarchy model, an entire class hierarchy is mapped to one table that contains the union of the attributes from all classes. A discriminator column identifies the object type. Advantages of this approach include simplicity and ease of querying, given that all data resides in one table. On the other hand, this approach does not work well when the classes in the hierarchy change frequently, requiring corresponding changes to the table.

TPT

The Table per Type approach, each class (including abstract classes) is mapped to a table in the database. Data integrity is maintained by introducing foreign keys – often a primary key which is also a foreign key. While this approach most closely resembles the object hierarchy, it has the disadvantage that you have to join several tables for querying and updates.

TPCT

In a Table per Concrete Type strategy, a table is created for each concrete class, with columns corresponding to the attributes of the class as well as those inherited from any abstract bases. I like to think of it as the hybrid approach, where you get some of the advantages of TPH (easier querying) but have to deal with having to update multiple tables corresponding to subclasses of a class that needs to be changed frequently.

If you find yourself interested in more on O/R mapping, here’s an excellent article I can recommend: http://www.ibm.com/developerworks/library/ws-mapping-to-rdb/.

LINQ to SQL and the Entity Framework

While LINQ to SQL only supports TPH, the Entity Framework offers all three mapping approaches, and should be the framework of choice when it comes to developing enterprise applications with flexible mapping requirements.

Having said that, I wrote the sample application that demonstrates how one can model TPT entities in a LINQ to SQL context using the built-in TPH support and a bit of T-SQL code. The sample is an exercise in exploring different options, and not necessarily a design recommendation. I hope, though, that some of you will find it useful, if only in a didactic sense.

Overview of the Application

First, an aside (and a shameless plug): The sample app I wrote is inspired by an application I used to work on—it ships as part of Microsoft Office, and you can check it out at http://www.ideawins.com.

Consider a hierarchy of classes captured by the following UML diagram.

Note that only the leaf classes are concrete—the base classes Account, FinancialAccount, and BankAccount are abstract. Now let us assume we wish to reflect this hierarchy in the database. How would we do it? A logical structure in the spirit of TPT would look like the following.

Note here that the primary keys in the child classes also serve as the foreign keys into parent classes. In fact, there really is only one key per row in each “leaf table.” This schema is about as close as you can get to the original class hierarchy.

Bringing it all together

The glue that will allow us to access a TPT-style logical layer with a LINQ to SQL DataContext is a view. The view definition will be a union of rows from the two tables corresponding to the concrete classes, joined to the base tables for the inherited attributes.

CREATE VIEW dbo.ACCOUNTSVIEW AS
SELECT [t0].ID,
[t0].ACCOUNTNUMBER,
[t0].ACCOUNTNAME,
[t1].BALANCE,
[t2].BANKNAME,
[t2].ACCOUNTHOLDER,
NULL AS CREDITLIMIT,
'H' AS Tag
FROM ACCOUNT AS [t0]
INNER JOIN FINANCIALACCOUNT AS [t1] ON [t0].ID = [t1].ID
INNER JOIN BANKACCOUNT AS [t2] ON [t0].ID = [t2].ID
INNER JOIN CHECKINGACCOUNT AS [t3] ON [t0].ID = [t3].ID
UNION
SELECT [t0].ID,
[t0].ACCOUNTNUMBER,
[t0].ACCOUNTNAME,
[t1].BALANCE,
[t2].BANKNAME,
[t2].ACCOUNTHOLDER,
[t3].CREDITLIMIT,
'C' AS Tag
FROM ACCOUNT AS [t0]
INNER JOIN FINANCIALACCOUNT AS [t1] ON [t0].ID = [t1].ID
INNER JOIN BANKACCOUNT AS [t2] ON [t0].ID = [t2].ID
INNER JOIN CREDITCARDACCOUNT AS [t3] ON [t0].ID = [t3].ID

Note that the view includes a discriminator column (‘Tag’) that doesn’t exist in any base table. Now it’s a straightforward matter of mapping this view as a TPH entity to the Account class hierarchy in the application. Once you do that, you can query the database and see that the objects are materialized as expected.

Updates

But we’re not done yet…what about updates? If you try to create new CheckingAccount or CreditCardAccount entities and submit them, SQL Server will complain saying that the view is not updatable.

This is the second piece of the puzzle we must resolve, and this is where you roll up your sleeves and write some T-SQL code to work around the limitation. Specifically, an INSTEAD OF INSERT trigger (included in the SQL script with the project) will intercept attempts to insert rows against the view and redirect them to the appropriate base tables. Please refer to the sample code for details.

Prerequisites: Building and Running the Sample

This sample is available with complete source code—I’d encourage you to make changes and play with the application. The app was built using Visual Studio 2008 and SQL Server 2005 Express Edition.

The Visual Studio solution for the sample contains:

TPT: This is the (main) console application project.
Accounts.bak: A backup of the database that you can restore on your SQL Server installation—it contains some minimal seed data.
Accounts.sql: The SQL code used to create the database, the various tables, the view and the trigger.

The connection string for the database is stored in the application configuration file—you’ll have to edit it in accordance with your server installation. You can download the sample from http://code.msdn.com/TPT.

Limitations

While the technique illustrated in the sample application is interesting, there are some drawbacks. First, it won’t work in an environment where the DBA locks down the database and developers aren’t allowed to make any changes to the schema. So if you cannot create views and triggers, this technique is of no use. Secondly, since SQL Server CE does not support views and triggers, this mechanism cannot be employed on that platform.

Feedback

I’d be interested in finding out what kind of samples and or documentation you’d like to see, both for the Entity Framework as well as LINQ to SQL. If you have ideas, drop me a line at linq@live.com.

Posted by Samir Bajaj | 1 Comments

Filed under: LINQ to SQL Entity Framework

Friday, March 14, 2008 12:00 AM

SketchPad: A Simple Drawing Editor

Aren’t you tired of samples and demos that deal with Customers, Orders, Products, and Categories? Admit it…you’d love to see something different, you’d welcome a fresh perspective, and I know you’ve been waiting patiently. My name is Samir Bajaj; I’m a developer in the Data Programmability team, and I’m here to tell you that the wait is over.

I’ve wanted to post on this blog for the longest time, and I’ve also known exactly what I’d be writing about, but for one reason or another I’ve not been able to find time to actually do it. I’m sure many of you have been in this situation—you’re so engrossed in working on cool and exciting technology, and with great people, that writing about it often takes a back seat. Well, I finally decided to make time for this endeavor, not only because I’ve been putting it off for too long, but because I think this is truly novel and enabling technology.

There is another reason—but first, a brief digression. A few months ago, I wrote a Drawing Editor application based on the Entity Framework. Having worked at a major CAD company before moving to Microsoft, I realized that a Graphics application built on the Entity Framework and backed by an industrial strength relational database like SQL Server would make a compelling demo of the functionality that the framework has to offer. Later, as I began to get familiar with LINQ to SQL, I figured that one way to showcase these two technologies would be to build an application that can switch between the two frameworks while working against the same back end.

A Rudimentary Spatial Database

By itself, an application like a Drawing Editor would not be terribly exciting. Sure, it would give you yet another example of how good the Entity Framework is at bridging the impedance mismatch, but we’ll go a step further, a step closer to a real-world application. I am not going to build a Drawing Editor directly atop the Entity Framework—I’ll give you a taste of a slightly more capable application that truly makes it a different kind of a sample.

If you haven’t already checked out the spatial capabilities of SQL Server 2008, let me just say that it’s one of my favorite new features. In a nutshell, a spatial database supports spatial ADTs like lines, circles and polygons, offers spatial operators like Area and Intersect, and provides spatial indexing for efficient access to such data.

The sample application that I’ve built comes with a rudimentary spatial layer and a basic, in-memory spatial index. I’ll explain how the application leverages that capability shortly.