Entity Framework Migrations on TeamCity

This post describes how to enable EF Code First migrations on TeamCity or any other continuous integration server or deploy script.

Lets assume you have MyData class library with your migrations and DbContext. This class library references Entity Framework and has MyData.config with connection string.

If you use NuGet (if not, you really should try it), then migration tool migrate.exe is under packages\EntityFramework.5.0.0\tools\ directory.

First, let's add migrate.cmd file to MyData project

Be careful when creating file with visual studio, you have to save the file in ASCII encoding or utf-8 without signature, otherwise it will have BOM bytes at the beginning (hex EF BB BF) and command line will fail to run.

Now we make migrate.cmd copy to output directory every build.

In migrate.cmd we will write small script to copy migration tool and run migrations. We have to copy migrate.exe to our bin folder because it needs EntityFramework.dll copy /Y ..\..\..\packages\EntityFramework.5.0.0\tools\migrate.exe . migrate.exe MyData /startupConfigurationFile:MyData.dll.config

Finally, we make migrate.cmd to run as a build step in TeamCity

If you have different configuration types in one class library, you have to specify their names like this: migrate.exe MyData MyConfiguration1 /startupConfigurationFile:MyData.dll.config migrate.exe MyData MyConfiguration2 /startupConfigurationFile:MyData.dll.config

Delete by id with Entity Framework

Here is a simple extension to Entity Framework that will allow you to delete rows from database by id. First we define interface and inherit all our model classes from it public interface IMyEntity { int Id { get; set; } } Second we write extension method for DbSet's containing our IMyEntity objects public static void RemoveById(this DbSet dbSet, int id) where T : class, IMyEntity, new() { var entity = dbSet.Local.First(it => it.Id == id); if (entity == null) { entity = new T(); entity.Id = id; dbSet.Attach(entity); } dbSet.Remove(entity); } Now we can use our extension like this var context = new MyContext(); context.MyEntities.RemoveById(5); context.SaveChanges(); You can SaveChanges() in extension method, if you find it useful.

Using LESS with ASP.NET WebForms or MVC

1. Get dotless class library from http://www.dotlesscss.org or by NuGet package manager.
2. Reference library dotless.Core.dll in your web project
3. Add to the web.config following settings: <configuration> <configSections> <section name="dotless" type="dotless.Core.configuration.DotlessConfigurationSectionHandler, dotless.Core" /> </configSections> <dotless minifyCss="false" cache="true" /> <system.web> <httpHandlers> <add type="dotless.Core.LessCssHttpHandler, dotless.Core" validate="false" path="*.LESS" verb="*" /> </httpHandlers> <system.web> </configuration>
4. Reference your less files like this: <link rel="stylesheet" type="text/css" href="/content/less/style.less">
5. Profit!

Fast pattern search on varchar and text fields

You may not know that simple index like CREATE INDEX index_name ON table_name (varchar_or_text_column_name) won't work effectively with pattern search (LIKE operator) if your server is not using C locale. explain select * from suggests where text like 'matrix%'; QUERY PLAN ----------------------------------------------------------------- Seq Scan on suggests (cost=0.00..400875.45 rows=1897 width=25) Filter: ((text)::text ~~ 'matrix%'::text) To make this search much faster, you must add a varchar_pattern_ops or text_pattern_ops keyword, depending on your column type. CREATE INDEX index_name on table_name (varchar_column_name varchar_pattern_ops); Let's see the difference: explain select * from suggests where text like 'matrix%'; QUERY PLAN ----------------------------------------------------------------- Index Scan using idx_text on suggests (cost=0.00..11.99 rows=1897 width=25) Index Cond: (((text)::text ~>=~ 'matrix'::text) AND ((text)::text ~<~ 'matriy'::text)) Filter: ((text)::text ~~ 'matrix%'::text) 11.99 against 400875.45 not bad, huh?

Bulk insert to postgresql returning id list

Let's say we need to insert a lot of rows to sql table and use it's freshly generated ids to do something else. PostgreSQL offers a great syntax for that. INSERT INTO my_table (my_column) VALUES ('row 1'), ('row 2'), ('row 3') RETURNING id; ------------ Result: 1 2 3 You can do the same using rails ActiveRecord like this: res = ActiveRecord::Base.connection .execute("INSERT INTO my_table (my_column) VALUES ('row 1'), ('row 2'), ('row 3') RETURNING id;") # This returns PGresult instance and you can use it like this res.each {|x| p x} ------------ Result: {"id"=>"1"} {"id"=>"2"} {"id"=>"3"}

Removing table indexes without knowing its names

How to remove all indexes from table with rails ActiveRecord migration without even knowing its names?

ActiveRecord::Base.connection.indexes('table_name').each do |idx|
  remove_index :table_name, :name => idx.name
end

If you want to do it outside of migration file, you can use

ActiveRecord::Migration.remove_index