FluentMigrator - Check if data/row exists

Question

I am using FluentMigrator to migrate one database schema to another. I have a case in which I want to check if some data (specifically a row) exists before adding a new one.

if (!Schema.Table("MyTable").Something().Exists)
    Insert.IntoTable("MyTable").Row(new { Id = 100, Field="Value" });

How do I check that the row exists first?

Stanwinn

Answer 1

As of version 3.0, there are no builtin features in FluentMigrator to insert a row if it doesn't exist. There is a request on GitHub to add this feature: https://github.com/fluentmigrator/fluentmigrator/issues/640.

However, you could use the Execute.Sql() method and write your own SQL query that checks if the row exists before inserting it as shown here Check if a row exists, otherwise insert.

Execute.Sql(@"
begin tran

if not exists (select * from MyTable with (updlock, rowlock, holdlock) where id='100' and Field='Value')
begin
    insert into MyTable values (100, 'Value')
end

commit
");

Witch

This is precisely what I had to do. Thanks! – Stanwinn