Wednesday, 17 June 2009

SQL Server – ISNULL() = IS_SLOW!

Just been doing some query optimisation and trying to work out what part of a Stored Procedure is slowing the query down.

I found this line: AND CategoryId = ISNULL(@CategoryId, CategoryId)

This is the lazy way to conditionally include clauses!

This is much better: (@CategoryId IS NULL OR CategoryId = @CategoryId)

Changing that line increased the performance by 0.5 second. Now it runs in < 150ms.

Friday, 12 June 2009

ADO.NET Entity Framework Designer : Deleting Table causes validation errors!

Remember, to delete a table from the Entity Framework designer, don’t just select the table and hit delete… you need to delete the table from the database first, then hit “Update Model from Database…”. 

ADO.NET Entity Framework “Problem in Mapping Fragments starting at lines XXX Non-Primary-Key column(s) [XXX] are being mapped in both fragments to different conceptual side properties - data inconsistency is possible because the corresponding conceptual side properties can be independently modified.”

I am using the ADO.NET Entity Framework to represent my domain model.  When I first created the model from the database, some of the tables in my database had implied foreign keys, i.e., foreign keys that weren’t explicitly set as Foreign Keys – this was a mistake.

So within SQL Server Management Studio I used the table designer to set up the foreign keys.  Easily done!

Then I used the “Update Model from database” function inside the ADO.NET EF Designer, expecting it to pick up the foreign keys; which it did do… but on Validation, it caused the error above.

This error occurs because the foreign key inside appears both as an Association (this was created when you hit “Update model from database”) AND  as a scalar property on the Entity object.

The solution is to delete the scalar property that represented your foreign key, before it was transformed into an association. Basically, if your parent table is “Parent”, and your child table is “Child”, which has a reference to Parent.Id, via Child.ParentId… just delete ParentId from the Child entity in the designer.  This should leave just “Parent” as an association under the navigation properties of the Entity.