Batch insert with ef4

Sometimes you need to push large amounts of data to the database in one go. Using EF4 there is now a possibility to use ExecuteStoreCommand() which I will explain today.

First of all some background to what I'm trying to achieve. I'm building a change notification system much like facebook has. You know the one which says you have x news messages. The model I will use is this one:

Now the problem is that while I could let Entity Framework insert the mappings for me that would be a pretty bad performance hit as lots of insert statements would be generated and sent to the server. As far as I know EF cannot batch these statements togheter. Luckily EF4 introduced the ExecuteStoreCommand() method which we can use to easilty execute raw sql against the database. The easiest thing to do might have been to write the sql like this:

 INSERT INTO tbl ......  
 INSERT INTO tbl ......  
 INSERT INTO tbl ......  
 INSERT INTO tbl ......

With many users this didn't feel right to me. What I wanted to use was:

INSERT INTO tbl SELECT from (name1, name2, name3)

It would have been ever better if I could have selected the username directly from the database but for various reasons that is no possible. But you can't really send a list to T-SQL and expect that to work because it is T-SQL, it is not supposed to be easy.

Luckily SQL Server 2005 added support for XML and after a bit of research I found this code project article. And with that we can make it work pretty good.

public void Save(ChangeInfo change, IEnumerable<string> usernames)
{
    Save(change);
    this.db.SaveChanges();
    var xml = "<l>" + string.Join("", usernames.Select(un => "<u>" + un + "</u>"))  + "</l>";
    this.db.ExecuteStoreCommand(@"DECLARE @x xml
        DECLARE  @s VARCHAR(MAX)
        SET @s = {0}
        SET @x =convert (xml, @s)
        INSERT INTO [GmokData].[dbo].[ChangeInfoToUserMappings] SELECT {1},tempTable.item.value('.', 'varchar(100)') AS item FROM @x.nodes('l/u') tempTable(item)", xml, change.ID);

}

First we save the master record so we have and ID for that one. Second we create an XML string which is the list of all the usernames. The names are ugly here but they will never be used anywhere else so it does not matter much. And as a last action we execute the SQL needed to insert the mappings into the database.