Entity Framework 6 DB-First: issue InvalidOperationException only for SQL Server

Question

Premise

We have an application in .NET 4.7.2 using Entity Framework 6 db-first (with an `.edmx` file). Until now we have used only Devart for Oracle as Provider for EF, but now we need to handle SQL Server databases. All our ASP.NET MVC 5 views (hundreds) need to work with `System.Data.SqlClient`. No problem, query works well except for...

Issue

The issue is related to foreign key and referenced table.

The error is the following:

InvalidOperationException: the cast to value type 'System.Int32' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.

at System.Data.Entity.Core.Common.Internal.Materialization.ErrorHandlingValueReader1.GetValue(DbDataReader reader, Int32 ordinal) +177 at lambda_method(Closure , Shaper ) +1146 at System.Data.Entity.Core.Common.Internal.Materialization.Coordinator1.ReadNextElement(Shaper shaper) +384
at System.Data.Entity.Core.Common.Internal.Materialization.SimpleEnumerator.MoveNext() +88
at System.Linq.Buffer1..ctor(IEnumerable1 source) +284
at System.Linq.Enumerable.ToArray(IEnumerable`1 source) +90
at Medici.MediciService.GetMedici(GetMediciQuery param)

The query example:

var medici =  _dbContext.Medici
            .Select(x => new MediciDto
            {
                Id = x.Id,
                Nome = x.Nome,
                Cognome = x.Cognome,
                TipoMedico = new TipoMedico
                {
                    Codice = x.TipoMedico.Id,
                    Descrizione = x.TipoMedico.Desc,
                }
            })
            .ToArray();

And these are the classes:

public class MediciDto
{
    public int Id{ get; set; }
    public string Nome { get; set; }
    public string Cognome { get; set; }
    public TipoMedico TipoMedico { get; set; }
}

public class TipoMedico
{
    public int Id{ get; set; }
    public string Descrizione { get; set; }
}

The issue is bound to the fact that the foreign key in table Medici is nullable, but the id on the referenced table not. Devart provider for EF6 handle this setting the default value (in this case zero), while SQL Server provider runs into the InvalidOperationException.

So, in conclusion our question is: is there a way to configure the same behaviour as Oracle Devart Provider as default for Entity Framework? We can't review all queries to handle a check null value. We don't have all this time.

This problem solved in this [topic](https://stackoverflow.com/questions/6864311/the-cast-to-value-type-int32-failed-because-the-materialized-value-is-null) — Enes Kartal, Aug 19 '21 at 10:32
@EnesKartal This solution involves reviewing all EF queries.. And this is the very last thing we should do... — kiafiore, Aug 19 '21 at 10:40
@EnesKartal Do you mean TipoMedico's ID? Nope, it's not nullable. But the TipoMedico's ID referentiated (FK) in Medico is nullable — kiafiore, Aug 19 '21 at 12:45
Change your ID field to "int?" type. Because "int" is not nullable type but "int?" is nullable. — Enes Kartal, Aug 19 '21 at 14:19
@EnesKartal Thanks, I know... But the purpose of this post is to try to find a way or a configuration that makes EF having the same behaviour with the two different providers. If it's possible. — kiafiore, Aug 19 '21 at 14:29

score 1 · Accepted Answer · answered Aug 19 '21 at 22:07

I think you may be out of luck that the code was built against an assumption about a peculiarity that the Oracle Provider had around projecting null-able references that other providers like SQL Server do not share.

If you do end up accepting that you need to revisit your projections there are two options I know of. Either explicitly add the #null checks before projecting, or consider leveraging Automapper to handle the Projections /w ProjectTo as this does handle #null references, plus has the added bonus of making your Linq expressions a lot more compact. This would involve defining the mapping rules for Automapper to use. Depending on how consistent and predictable your entity vs. DTO naming conventions are, Automapper can resolve quite a bit by convention.

.Select(x => new MediciDto
{
    Id = x.Id,
    Nome = x.Nome,
    Cognome = x.Cognome,
    TipoMedico = x.TipoMedico != null 
        ? new TipoMedico
        {
            Codice = x.TipoMedico.Id,
            Descrizione = x.TipoMedico.Desc,
        } : null
}).ToArray();

or

.ProjectTo<MedicoDTO>(config)
.ToArray();

'config' being a MapperConfiguration which can be a single scoped dependency, or declared as needed. If the DTOs and Entities are consistent in their naming it can be as easy as:

var config = new MapperConfiguration(cfg => {
    cfg.CreateMap<Medico, MedicoDTO>();
    cfg.CreateMap<TipoMedico, TipoMedicoDTO>();
});

However, from your example it refers to a "TipoMedico" within your MedicoDTO DTO, whether that is a typo or refers to a DTO missing the suffix, or it refers to a copy of the Entity class itself. (Hopefully not that last one, mixing Entities and DTOs)

Re-factoring is sometimes required when project requirements change, and it can be seen as an opportunity to tidy and improve things.

Entity Framework 6 DB-First: issue InvalidOperationException only for SQL Server

Premise

Issue

1 Answers1