LINQ to SQL updates in .Net 4.0

What LINQtoSQL changes will be shipped with .Net 4.0?
List of changes could be found below (originally posted by Damien):

Performance:

• Query plans are reused more often by specifically defining text parameter lengths (when connecting to SQL 2005 or later)
• Identity cache lookups for primary key with single result now includes query.Where(predicate).Single/SingleOrDefault/First/FirstOrDefault
• Reduced query execution overhead when DataLoadOptions specified (cache lookup considers DataLoadOptions value equivalency)

Usability:

• ITable interface for additional mocking possibilities
• Contains with enums automatically casts to int or string depending on column type
• Associations can now specify non-primary-key columns on the other end of the association for updates
• Support list initialization syntax for queries
• LinqDataSource now supports inherited entities
• LinqDataSource support for ASP.NET query extenders added

Query stability:

• Contains now detects self-referencing IQueryable and doesn’t cause a stack overflow
• Skip(0) no longer prevents eager loading
• GetCommand operates within SQL Compact transactions
• Exposing Link on a property/field is detected and reported correctly
• Compiled queries now correctly detect a change in mapping source and throw
• String.StartsWith, EndsWith and Contains now correctly handles ~ in the search string (regular & compiled queries)
• Now detects multiple active result sets (MARS) better
• Associations are properly created between entities when using eager loading with Table-Valued Functions (TVFs)
• Queries that contain sub-queries with scalar projections now work better

Update stability:

• SubmitChanges no longer silently consumes transaction rollback exceptions
• SubmitChanges deals with timestamps in a change conflict scenario properly
• IsDbGenerated now honors renamed properties that don’t match underlying column name
• Server-generated columns and SQL replication/triggers now work instead of throwing SQL exception
• Improved binding support with the MVC model binder

General stability:

• Binary types equate correctly after deserialization
• EntitySet.ListChanged fired when adding items to an unloaded entity set
• Dispose our connections upon context disposal (ones passed in are untouched)

Database control:

• DeleteDatabase no longer fails with case-sensitive database servers

SQL Metal:

• Foreign key property setter now checks all affected associations not just the first
• Improved error handling when primary key type not supported
• Now skips stored procedures containing table-valued parameters instead of aborting process
• Can now be used against connections that use AttachDbFilename syntax
• No longer crashes when unexpected data types are encountered

LINQ to SQL class designer:

• Now handles a single anonymously named column in SQL result set
• Improved error message for associations to nullable unique columns
• No longer fails when using clauses are added to the partial user class
• VarChar(1) now correctly maps to string and not char
• Decimal precision and scale are now emitted correctly in the DbType attributes for stored procedures & computed columns
• Foreign key changes will be picked up when bringing tables back into the designer without a restart
• Can edit the return value type of unidentified stored procedure types
• Stored procedure generated classes do not localize the word “Result” in the class name
• Opening a DBML file no longer causes it to be checked out of source control
• Changing a FK for a table and re-dragging it to the designer surface will show new FK’s

Code generation (SQL Metal + LINQ to SQL class designer):

• Stored procedures using original values now compiles when the entity and context namespaces differ
• Virtual internal now generates correct syntax
• Mapping attributes are now fully qualified to prevent conflicts with user types
• KnownTypeAttributes are now emitted for DataContractSerializer with inheritance
• Delay-loaded foreign keys now have the correct, compilable, code generated
• Using stored procedures with concurrency no longer gets confused if entities in different namespace to context
• ForeignKeyReferenceAlreadyHasValueException is now thrown if any association is loaded not just the first

Potentially breaking changes:
We worked very hard to avoid breaking changes but of course any potential bug fix is a breaking change if your application was depending on the wrong behavior. The ones I specifically want to call out are:

Skip(0) is no longer a no-op:
The special-casing of 0 for Skip to be a no-op was causing some subtle issues such as eager loading to fail and we took the decision to stop special casing this. This means if you had syntax that was invalid for a Skip greater than 0 it will now also be invalid for skip with a 0. This makes more sense and means your app would break on the first page now instead of subtlety breaking on the second page. Fail fast :)

ForeignKeyReferenceAlreadyHasValue exception:
If you are getting this exception where you weren’t previously it means you have an underlying foreign key with multiple associations based on it and you are trying to change the underlying foreign key even though we have associations loaded.Best thing to do here is to set the associations themselves and if you can’t do that make sure they aren’t loaded when you want to set the foreign key to avoid inconsistencies.

Regards,

Oleh

Identity value management

Identity column is a column that uniquely identifies every row. It differs from a primary key in that its values are managed by the server and ( unless you specify it explicitly ) can't be modified. In many cases it is used as a primary key.

Identity value management:

Create Identity:

CREATE TABLE [dbo].[Person]( [PersonID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](128) NOT NULL)

Insert Identity values:
You do not have to specify identity value when inserting new record: INSERT INTO Person(Name) values('Oleh'). It will be filled for you using current identity and increment values.
In case you specify one: INSERT INTO Person(PersonID,Name) values(2,'Oleh') you will get an error: Msg 544, Level 16, State 1, Line 2
Cannot insert explicit value for identity column in table 'Person' when IDENTITY_INSERT is set to OFF.
Instead you need to use next snippet:
SET IDENTITY_INSERT Person ON
INSERT INTO Person(PersonID, Name) VALUES(1,'Oleh')
SET IDENTITY_INSERT Person OFF

When you insert identity value explicitly and it's a primary key column then you will get an error:
Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'PK_Person'. Cannot insert duplicate key in object 'dbo.Person'.
The statement has been terminated.

Get current identity value:

• DBCC CHECKIDENT(TableName, NORESEED) - returns current identity value
• SCOPE_IDENTITY() - returns the last identity value inserted within the same scope(stored procedure, trigger, function or batch)
• @@IDENTITY - returns the last identity value inserted across all scopes
• IDENT_CURRENT(TableName) - returns the last identity value for the specified table regardless of the scope.

Update identity value:

• DBCC CHECKIDENT(TableName, RESEED, NewSeed) - resets the identity value with provided seed
• DBCC CHECKIDENT(TableName) - resets the identity value only if it is required

Get Seed/Increment:

• IDENT_INCR(TableName) - returns original increment value that was specified when identity was created.
• IDENT_SEED(TableName) - returns original seed value that was specified when identity was created.

SELECT TABLE_SCHEMA, TABLE_NAME
, IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) AS IDENT_SEED
, IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME) AS IDENT_INCR
FROM INFORMATION_SCHEMA.TABLES
WHERE IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) IS NOT NULL

Finding gaps in identity column:

• Here you can find the original post about how you can find gaps in identity column.

Regards,
Oleh

Transaction Log Shipping Copy job update

I need to configure TransactionLogShipping of a database between two database servers. Primary database is on production server that is SQL Server 2008 Standard installed on Windows Server 2003 x32 (used as web server as well). Secondary database is on SQL Server 2008 Developer installed on Windows Server 2008 x64 (I’ll use it as development database with almost up-to-date data for developing and testing heavy statistics reports). And both servers are in DIFFERENT networks.

OK. Let’s configure it!

I would not describe all the steps in detail that are required for TransactionLogShipping (you can find overview here) just step that I had problems with. My problem was copying transaction log over network. Different networks remember. Everything I googled was about having both database servers in the same network and creating user account with the exactly same name on both machines and full access rights for folders used for creating and restoring backups. Creating user accounts on both with full access rights especially on primary server that is a web server as well did not sound like a good idea for me. I tried to reuse something that I have and that is configured, tested and working 24/7 without opening potential security flaw.

I have an FTP server on primary server. And I know that LogShippingCopy job could be adjusted to reuse SSIS package for example that will take care of copying transaction log files from primary database server. Sounds like an idea!

Backup job requires network path for the folder where it will put log backup. It’s compulsory parameter for it. I have created a shared folder with FTP server folder and provided the path to BackupJob configuration dialog.

RestoreJob on secondary server is configured as in every other case.

Copy Job

Final configurations looks like:

FtpCopy package: That was the biggest problem for me.

I have created simple SSIS package with single FtpTask.

I tried it on my development machine locally (Vista x32, BIDS 2008) and it is working ok. Deployed to secondary database server started copy job manually – nothing is copied !? OK. A have BIDS installed on secondary server (since it’s a development one). Opened the same package from there and I’m getting "0xC001602A at FtpCopy, Connection manager "FTP Connection Manager": An error occurred in the requested FTP operation. Detailed error description: The operation timed out" every time. !?!? OK. I’m trying to reuse Scripting Task to copy files over Ftp in order to get detailed exception or at least step through code and see the flow. Wrote the code in C# and placed a breakpoint. I’m pressing F5 hoping that I’m seconds away from the detailed error description thus the solution. But the Output window writes: “Warning: 0x7 at Script Task: Cannot debug script tasks when running under the 64 bit version of the Integration Services runtime.” LLL

OK. Let’s check FTP logs from the Ftp server. When running either FtpTask or ScriptTask from my development machine (Vista x32) I see log entry code 226 which means “Transfer Complete” but when running the same FtpTask or ScriptTask from secondary server (WS 2008 x64) log entry is 426 Connection closed; transfer aborted. Error: 64.

Let’s google, maybe it’s specific x64 runtime problem. I have founds this MS forum entry that confirmed my suspicions. It’s related to running package on x64 runtime.

My last idea was to reuse ExecuteProcessTask and will start bat file with ftp commands for copying files over ftp.

Now everything is OK. Ftp log entries have 226 code and I see transaction log files in destination folder!

Tried complete TransactionLogShipping sequence – works like a charm! J But almost full working day was spent in order to make it working.

You can see the flow diagram below:

PS:

• Average transaction log file is 2.5MB.
• If you have existing transaction log backup jobs either stop them or re-point them to save transaction log to the same folder as log shipping backup job does as Log Sequence Number(LSN) must me continuous in oder for log shipping restore to be successful.

I hope this post will help someone.

Regards,

Oleh