When LINQ-to-SQL Meets Partitioned View‏

When SQL Server (including SQL Azure) is the database for my projects, I like to use LINQ-to-SQL. Its attribute-based mapping is pretty neat. I also like to use Partitioned View, which makes it easy and fast to purge old data: just drop tables rather than run DELETE commands.

Recently, in a new project when I used LINQ-to-SQL and Partitioned View together, I ran into such an error:

System.Data.SqlClient.SqlException: The OUTPUT clause cannot be specified because the target view "FooBar" is a partitioned view.

I wasn’t able to find a good answer in Bing/Google/StackOverflow. It seemed to me that I might have to look into the source code of System.Data.Linq to find out what was the exact SQL command that LINQ-to-SQL generated and why there was an OUTPUT in there. When I was about to start this laborious source code reading journey, I happened to look at my entity class again and suddenly realized “wait a second, could the problem be the IsDbGenerated = true and AutoSync = AutoSync.OnInsert flags?”:

[Column(Name = "guid_row_id", CanBeNull = false, IsPrimaryKey = true, 
        IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
public Guid RowId;

“Yeah, that would make sense”, I thought, because if I were to write LINQ-to-SQL by myself, I would probably too have used the OUTPUT clause to implement the IsDbGenerated and AutoSync flags. So I removed IsDbGenerated and AutoSync:

[Column(Name = "guid_row_id", CanBeNull = false, IsPrimaryKey = true)]
public Guid RowId;

Voilà, the error was gone!

Eventually it turns out that the culprit was IsDbGenerated. So as a workaround, I changed my code to generate new row IDs with Guid.NewGuid() in the application code. It’s fine for my project since it’s just a Guid. I guess this issue, that IsDbGenerated in LINQ-to-SQL doesn’t work with partitioned views, would be more troublesome if someone wants to use other DB generated value like GetUTCDate(), which could be quite useful to avoid the time drift issue on the client side, or auto increment integer.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s