Entity Framework Pitfalls: Null Semantics

Imagine you have a simple class:

public class MyEntity
public int Id { get; set; }
public string Name { get; set; }

You may want to query it:

string name = ...;

var entities = ctx
.Where(x => x.Name == name)

Entity Framework generates a query such as this:

SELECT [Extent1].[Id] AS Id, [Extent1].[Name] AS Name
FROM [dbo].[MyEntities] AS [Extent1]
WHERE ([Extent1].[Name] = @p__linq__0)
OR (([Extent1].[Name] IS NULL) AND (@p__linq__0 IS NULL))

Meaning: return all records from the MyEntities table if

  1. The Name column is identical to the name variable or
  2. Both the Name column and the name variable are NULL

Seems excessively complex, doesn’t it? Well, this is to account for the case where the name parameter is null. In  that case, a simple COLUMN = VALUE comparison won’t work, because in SQL, NULL = NULL returns FALSE! The syntax for comparing with NULL is COLUMN IS NULL. By generating this kind of query, Entity Framework is playing safe. Mind you, this only happens if you do not use the literal null, if you do, Entity Framework is smart enough to generate a simpler query:

var entities = ctx
.Where(x => x.Name == null)

Results in:

SELECT [Extent1].[Id] AS Id, [Extent1].[Name] AS Name
FROM [dbo].[MyEntities] AS [Extent1]
WHERE ([Extent1].[Name] IS NULL)

However, there is a configuration setting, UseDatabaseNullSemantics (the opposite of the old UseCSharpNullComparisonBehavior of ObjectContext), that can be used to control this behavior. If set to true (the opposite of the default):

ctx.Configuration.UseDatabaseNullSemantics = true;

It generates all SQL queries as the unsafe version:

SELECT [Extent1].[Id] AS Id, [Extent1].[Name] AS Name
FROM [dbo].[MyEntities] AS [Extent1]
WHERE ([Extent1].[Name] = @p__linq__0)

The result is, even if you have a record whose Name column is NULL, it won’t be returned: remember that in SQL, by default, NULL <> NULL!

Therefore, if you are not 100% sure that the parameter that you will be using in the LINQ query will never be null, do not mess with UseDatabaseNullSemantics even if it generates somewhat poorer SQL.

Of course, if you are using SQL Server, you can set ANSI_NULLS to OFF, and the problem goes away; in that case, NULL = NULL.

SQLPort em Aveiro

(Portuguese only, sorry!)

No próximo dia 22 de Setembro irá ter lugar em Aveiro, nas instalações da Fusion Cowork (Fusion 2), o LXXXVI evento da Comunidade SQLPort, o primeiro dos quais em Aveiro!

Os oradores são do mais alto nível: Niko Neugebauer (@NikoNeugebauer) e Eduardo Piairo (@EdPiairo), irão falar sobre Performance Tuning e Database Source Control. O programa é o seguinte:

18:30 Abertura e recepção
19:00 “Performance Tuning” – Niko Neugebauer (MVP, Consultant – OH22)
20:10 Intervalo
20:30 “Database source control: migrations vs state” – Eduardo Piairo (DBA – Celfinet)  
21:40 Sorteio
22:00 Jantar livre

Podem ver mais informações aqui e registarem-se aqui. O evento é gratuito mas não dispensa inscrição!

A morada da Fusion 2 é:

Rua Carlos
Aleluia, 4, Edifício Aveiro
Centrum, Loja 3

3810-177 Aveiro

Aguardamo-vos lá! Winking smile

SQL Server Auditing

Probably the best mechanism for auditing changes to tables in SQL Server is Change Data Capture, which I already covered here. Unfortunately, it only works with the Enterprise editions of SQL Server, something that not everyone has access to.

I once had to implement a similar solution that should work on any edition of SQL Server, which is what I am going to talk about. Its only requirement was that for any changes made to target tables, their old values would be stored in a shared history table, with the indication of the timestamp and user who made the change.

Let’s imagine we have some table, anything will do:

   1: CREATE TABLE dbo.audit_target

   2: (


   4:     a NVARCHAR(50),

   5:     b INT

   6: )

As you can see, it’s just a regular table with a single-column primary key and a couple of other columns, any number and type will do, doesn’t really matter.

Now let’s create a table for holding the change history:

   1: CREATE TABLE dbo.history

   2: (


   4:     [table_name] NVARCHAR(30) NOT NULL,

   5:     table_id INT NOT NULL,

   6:     [changes] NVARCHAR(MAX),


   8:     [user] NVARCHAR(30) NOT NULL

   9: )

The table_name column shall hold the name of the table whose changes we are tracking, table_id, the id of the updated row, changes will get a XML containing all the old values, timestamp the date and time the change occurred, and user, the person who made the change (more on this later).

OK, next we need a stored procedure to populate the changes table:

   1: CREATE PROCEDURE dbo.generic_audit_procedure

   2: (

   3:     @procid INT,

   4:     @xml XML

   5: )

   6: AS

   7: BEGIN

   8:     SET NOCOUNT ON


  10:     -- check if there are changes, in which case, xml will not be empty

  11:     IF ((@xml IS NULL) OR (CAST(@xml AS NVARCHAR(MAX)) = N''))

  12:     BEGIN

  13:         RETURN 0

  14:     END


  16:     -- get the current table id for the current process (trigger)

  17:     DECLARE @tablename NVARCHAR(30)


  19:     SELECT @tablename = OBJECT_NAME(t.id)

  20:     FROM sys.sysobjects p

  21:     INNER JOIN sys.sysobjects t

  22:     ON p.parent_obj = t.id 

  23:     WHERE p.id = @procid


  25:     IF ((@tablename IS NULL) OR (@tablename = N''))

  26:     BEGIN

  27:         RAISERROR(N'Could not get table name', 16, 16)

  28:         ROLLBACK TRAN

  29:         RETURN

  30:     END


  32:     -- get the primary key column for the current table

  33:     DECLARE @idname NVARCHAR(30)


  35:     SELECT @idname = name 

  36:     FROM sys.syscolumns

  37:     WHERE id = OBJECT_ID(@tablename)

  38:     AND colstat = 1


  40:     IF ((@idname IS NULL) OR (@idname = ''))

  41:     BEGIN

  42:         RAISERROR(N'Could not get id column for the current table', 16, 16)

  43:         ROLLBACK TRAN

  44:         RETURN

  45:     END


  47:     -- get the current user from the context

  48:     DECLARE @username NVARCHAR(30)


  50:     SELECT @username = CONVERT(NVARCHAR(30), CONTEXT_INFO())


  52:     IF ((@username IS NULL) OR (@username = ''))

  53:     BEGIN

  54:         RAISERROR(N'Could not get current username', 16, 16)

  55:         ROLLBACK TRAN

  56:         RETURN

  57:     END


  59:     DECLARE @tmp TABLE([key] NVARCHAR(MAX), [value] XML)


  61:     INSERT INTO @tmp SELECT q.[key], q.[value]

  62:     FROM

  63:     (

  64:         SELECT T.N.value(N'(*[local-name(.)=sql:variable("@idname")])[1]', N'INT') AS [key], T.N.query(N'*') AS [value]

  65:         FROM @xml.nodes('/row') AS T(N)    

  66:     ) q



  69:     SET @ROWS = 0


  71:     -- loop all modified records

  72:     WHILE (1 = 1)

  73:     BEGIN

  74:         DECLARE @id NVARCHAR(MAX)

  75:         DECLARE @value XML


  77:         -- pick the first record

  78:         SELECT TOP 1 @id = [key], @value = [value]

  79:         FROM @tmp


  81:         IF (@@ROWCOUNT = 0)

  82:         BEGIN

  83:             BREAK

  84:         END


  86:         -- insert into the shared table

  87:         INSERT INTO dbo.history (table_name, table_id, [user], [changes])

  88:         VALUES (@tablename, @id, @username, CAST(@value AS NVARCHAR(MAX)))


  90:         -- increment the updated rows

  91:         SET @ROWS = @ROWS + @@ROWCOUNT


  93:         -- remove the processed record

  94:         DELETE FROM @tmp

  95:         WHERE [key] = @id

  96:     END


  98:     RETURN @ROWS

  99: END

The generic_audit_procedure will get the current table from the current process id, as passed in the @@procid parameter, the current user from CONTEXT_INFO (again, more on this later), the changes from the @xml parameter and will insert everything into the history table.

And the final part, a trigger to “glue” together the changes made to a table to the stored procedure:

   1: CREATE TRIGGER dbo.generic_audit_trigger

   2: ON dbo.test -- replace this with other target tables' names


   4: AS

   5: BEGIN

   6:     SET NOCOUNT ON


   8:     -- get all changes

   9:     -- this needs to be done on the trigger

  10:     DECLARE @xml XML

  11:     SET @xml = (SELECT * FROM inserted FOR XML PATH('row'))


  13:     -- call the generic stored procedure

  14:     EXEC dbo.generic_audit_procedure @@PROCID, @xml

  15: END

This trigger will run after changes are accepted (AFTER UPDATE) and will cast all changes (the INSERTED pseudo-table) as XML, and then invoke the generic_audit_procedure stored procedure with it as an argument, together with the current process id (@@PROCID). Of course, you need to call this for every table that you wish to audit.

The only thing left is, we need to provide a way for SQL Server to know the name of the current user. We do that by manually executing the following query (actually, it was an automated process that was executed by the data access library):

   1: DECLARE @username VARBINARY(128) = CAST(N'rjperes' AS VARBINARY(128))

   2: SET CONTEXT_INFO @username

So, whenever a change is made to a table, it will appear as this:


You can see that the changes column will contain an XML node with all the old values that were changed by the update made to the target table.

In a nutshell:

  1. We add a trigger that calls a stored procedure upon change to a number of target tables;
  2. Save the application user in CONTEXT_INFO;
  3. Modify the data in a table;
  4. Get the old values in an auditing table;

There are some limitations with this implementation:

  • Only single-column primary key target tables are supported;
  • All of the “old” values are recorded, not just those that changed;
  • It only keeps the “old” values, not the new ones, although this is by design and easy to change;
  • Binary columns could use a better treatment;
  • Doesn’t keep track of deleted or inserted records;
  • Doesn’t track schema changes (add/drop/modify columns, etc);
  • It is necessary to set CONTEXT_INFO explicitly with the application user that we want to record;
  • Although I didn’t implement it, it would be easy to add a version column to the history table, to keep track of how many changes were made to each target table.

However, it suited perfectly what I needed, maybe it will work for you too! Winking smile

Getting the SQL for HQL and Criteria Queries

OK, so, I already showed how we can get the SQL that was generated from a LINQ query. Of course, we can do the same for both HQL and Criteria APIs as well (QueryOver is just a wrapper around Criteria, mind you).

So, for HQL (and SQL), it goes like this:

   1: public static class QueryExtensions

   2: {

   3:     private static readonly PropertyInfo sessionProperty = typeof (QueryImpl).GetProperty("Session", BindingFlags.NonPublic | BindingFlags.Instance);


   5:     public static String ToSql(this IQuery query)

   6:     {

   7:         if (query is QueryImpl)

   8:         {

   9:             var session = sessionProperty.GetValue(query, null) as ISession;

  10:             var sessionImpl = session.GetSessionImplementation();

  11:             var translatorFactory = new ASTQueryTranslatorFactory();

  12:             var translators = translatorFactory.CreateQueryTranslators(query.QueryString, null, false, sessionImpl.EnabledFilters, sessionImpl.Factory);


  14:             return translators[0].SQLString;

  15:         }

  16:         else if (query is SqlQueryImpl)

  17:         {

  18:             return (query.QueryString);

  19:         }


  21:         throw (new NotSupportedException("Query type is not supported."));

  22:     }

  23: }

You can pass any implementation if IQuery, such as one produced from ISession.CreateQuery() or ISession.CreateSQLQuery(). The static field is merely for performance reasons.

As for Criteria:

   1: public static class CriteriaExtensions

   2: {

   3:     public static String ToSql(this ICriteria criteria)

   4:     {

   5:         var criteriaImpl = criteria as CriteriaImpl;

   6:         var sessionImpl = criteriaImpl.Session;

   7:         var factory = sessionImpl.Factory;

   8:         var implementors = factory.GetImplementors(criteriaImpl.EntityOrClassName);

   9:         var loader = new CriteriaLoader(factory.GetEntityPersister(implementors[0]) as IOuterJoinLoadable, factory, criteriaImpl, implementors[0], sessionImpl.EnabledFilters);


  11:         return loader.SqlString.ToString();

  12:     }

  13: }

And finally, QueryOver, just a small wrapper around the Criteria version:

   1: public static class QueryOverExtensions

   2: {

   3:     public static String ToSql(this IQueryOver queryOver)

   4:     {

   5:         var criteria = queryOver.UnderlyingCriteria;

   6:         return (criteria.ToSql());

   7:     }

   8: }

Hope you find this useful! Winking smile

Lesser-Known NHibernate Features – Generating Database Scripts

As you may know, NHibernate knows how to generate the database for you from its model. Any of the supported databases will do.

What you may not know is that you can simply generate the SQL scripts that would be used to either generate the database from scratch or just update it to match the model as it is. For that you use the SchemaExport and SchemaValidator classes.

SchemaValidator takes a Configuration instance and basically throws an exception if, when its Validate method is called, the database does not match the model.

SchemaExport has far more options; it can:

  • Create or drop a database model based on the entity model;
  • Output the SQL to a file, either executing it at the same time or not;
  • Execute the SQL to another arbitrary database connection;
  • Execute a custom action after each line of the generated SQL script.

An example:

   1: var validator = new SchemaValidator(cfg);


   3: try

   4: {

   5:     validator.Validate();

   6: }

   7: catch

   8: {

   9:     var export = new SchemaExport(cfg).SetOutputFile("Script.sql");


  11:     //send the script to standard output and execute it

  12:     export.Execute(useStdOut: true, execute: true, justDrop: false);

  13:     //or

  14:     //send all lines of the script to the System.Console.WriteLine method

  15:     export.Execute(scriptAction: System.Console.WriteLine, execute: false, justDrop: false);

  16: }