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.
Comments on “When LINQ-to-SQL Meets Partitioned View”