EF6 unique constraint on foreign key

Question

I've got the following scenario:

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'm looking to insert books, and associated authors if needed. I have the following naive code, which 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'm seeing is that sometimes, the FirstOrDefault is returning null, but the insert is failing due to violation of the unique index on the author name. Is there some EF trickery that'll allow this to happen in a simplistic way? I guess I could use a stored proc, but would like to do it client side if possible.

Answer 1

After trying various things, I've gone 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();

While not pretty, this does prevent the race condition between the author check and insertion using db native features. ORMs and leaky abstractions, eh :)

I guess I could put the book insert in there too, or make the whole thing a sproc. If anybody comes up with an ORM native approach that caters to this scenario, I'm all ears :)