Unique EF6 foreign key constraint

Question

I have the following script:

public class Book { [Key] public string Isbn { get; set; } public string Title { get; set; } public int Stock { get; set; } public Author Author { get; set; } } public class Author { public int AuthorId { get; set; } [Index(IsUnique = true)] [StringLength(50)] public string Name { get; set; } public ICollection<Book> Books { get; set; } } 

I want, if necessary, to insert books and associate authors. I have the following naive code that breaks (pretty much expected):

 var author = _ctx.Authors.FirstOrDefault(x => x.Name == command.Author); if (author == null) author = new Author { Name = command.Author }; var book = new Book { Isbn = command.Id, Title = command.Title, Stock = command.Count, Author = author }; _ctx.Books.Add(book); await _ctx.SaveChangesAsync(); 

What I see is that sometimes FirstOrDefault returns null, but the insert does not work due to violation of the unique index of the author name. Are there any EF tricks that will allow this to be done in a simplified way? I think I could use a stored procedure, but if possible I would like to do it on the client side.

Answer 1

After several attempts, I went with the following:

 var author = _ctx.Authors.SqlQuery( "with data as (select @author as [name]) " + "merge Authors a " + "using data s on s.[name] = a.[name] " + "when not matched by target " + "then insert([name]) values(s.[name]); select * from Authors where [name] =@author ", new SqlParameter("author", command.Author)).Single(); var book = new Book { Isbn = command.Id, Title = command.Title, Stock = command.Count, Author = author }; _ctx.Books.Add(book); await _ctx.SaveChangesAsync(); 

Although not very good, it prevents a race condition between author validation and insertion using db's own functions. ORM and leaky abstractions, eh :)

I think I, too, could insert a book insert into it, or do all this sproc. If someone comes up with their own ORM approach that fits this scenario, I'm all ears :)