Entity Framework 6 Code First: error 3023: Problem in mapping fragments starting at lines xxxx:Column has no default value and is not nullable. A column value is required to store entity data

There are many reasons why this error would happen. There's a known bug in 6.1 which will be fixed in 6.2. But my problem wasn't related to it. Here's what happened for me.

I use a single table, tbl_Person,  to map a hierarchy, like this example, where Person is abstract :

And I use this mapping using Fluent API:

modelBuilder.Entity<Person>()
 .ToTable("tbl_Person")
 .HasKey(m => m.PersonID)
 .Map<Student =>(m.Requires (discrimination stuff) )
 .Map<Instructor>(m => m.Requires (discrimination stuff) )

And then later, I added another kind of Person to my model. Let's say a "Director"  but I forgot to add the mapping. That got me the error whenever I would try to use a DbContext. So I just added the mapping for "Director" and the error went away.

By the way, a discriminator column can be NOT NULL and have no default value, as opposed to what the error lead me to think.

Coffee time!

Entity Framework Code First: map to updatable view with INSTEAD OF INSERT trigger

At first, I was glad we could map entities to SQL views. Just map them to a view instead of a table. But when we need to update or delete, we need an updatable view. This is doable as explained here. My view was a join of multiple tables so I had to rely on an INSTEAD OF triggers. This works fine in theory or if you do your own queries. However, Entity Framework (v6 in my case) doesn't like them very much and I get this error when I add a new entity:

Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.

Here' why it thinks no rows were affected. Using SQL Server Profiler, I've been able to catch the following query that EF generates when I call SaveChanges() on my DbContext:

exec sp_executesql 

N'INSERT [dbo].[vw_ErpAndType]([Description], [ConnectionString], ...)

VALUES (@0, NULL, ...)

SELECT [idERP]

FROM [dbo].[vw_ErpAndType]

WHERE @@ROWCOUNT > 0 AND [idERP] = scope_identity()'

,N'@0 nvarchar(max) ,@1 bit, ...'

,@0=N'bill',@1=0 ...

See that part in bold and underlined red? This is how EF gets the key of the newly inserted row and give it back to the business object. The problem is that when there's an INSTEAD OF INSERT trigger, that scope_identity() instruction returns NULL because the trigger happens in another scope. So even if the insertion is successful, the query returns nothing and thus, EF thinks nothing happened and throws the error.

The workaround would be for EF to use @@identity instead but I don't believe we can do that.

This is as far as I went trying to use updatable views with EF. Now I'll see what can be done with mapped stored procedures for insert-delete-update.