Pomelo.entityframeworkcore.mysql: Navigation property on alternate key
What's the correct way to configure a navigation to join entities when the the primary key isn't suitable? In the example below the Books class includes Formats but instead of joining on ISBN it needs to use ISBN13 (which is optional). The generated SQL is correct, but an exception is raised when value missing.
Steps to reproduce
CREATE TABLE `Books` (
`ISBN` VARCHAR(10) NOT NULL,
`ISBN13` VARCHAR(13) NULL DEFAULT NULL,
`Title` VARCHAR(50) NOT NULL,
`Author` VARCHAR(50) NOT NULL,
PRIMARY KEY (`ISBN`)
)
ENGINE=InnoDB;
INSERT INTO `Books` (`ISBN`, `ISBN13`, `Title`, `Author`)
VALUES ('161729456X', '9781617294563', 'Entity Framework Core in Action', 'Jon P Smith');
INSERT INTO `Books` (`ISBN`, `ISBN13`, `Title`, `Author`)
VALUES ('1788478126', NULL, 'C# 8.0 and .NET Core 3.0', 'Mark J. Price');
CREATE TABLE `Formats` (
`ISBN13` VARCHAR(13) NOT NULL,
`Type` VARCHAR(50) NOT NULL,
PRIMARY KEY (`ISBN13`, `Type`)
)
ENGINE=InnoDB;
INSERT INTO `Formats` (`ISBN13`, `Type`) VALUES ('9781617294563', 'Hardcover');
INSERT INTO `Formats` (`ISBN13`, `Type`) VALUES ('9781617294563', 'Paperback');
```C#
namespace ConsoleApp1
{
public class Book
{
public string ISBN { get; set; }
public string ISBN13 { get; set; }
public string Title { get; set; }
public string Author { get; set; }
public ICollection
}
public class Format
{
public string ISBN13 { get; set; }
public string Type { get; set; }
}
public class Context : DbContext
{
public DbSet<Book> Books { get; set; }
public DbSet<Format> Formats { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseMySql(
"server=127.0.0.1;port=3306;user=;password=;database=Console1",
b => b.ServerVersion("8.0.11-mysql"))
.UseLoggerFactory(
LoggerFactory.Create(b => b
.AddConsole()
.AddFilter(level => level >= LogLevel.Information)))
.EnableSensitiveDataLogging()
.EnableDetailedErrors();
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Book>(
entity =>
{
entity.HasKey(a => a.ISBN);
entity.HasMany(a => a.Formats).WithOne()
.HasForeignKey(a => a.ISBN13).HasPrincipalKey(a => a.ISBN13);
});
modelBuilder.Entity<Format>(
entity =>
{
entity.HasKey(a => new { a.ISBN13, a.Type });
});
}
}
class Program
{
static void Main(string[] args)
{
using var context = new Context();
var books = context.Books
.Include(a => a.Formats)
//.Where(a => a.ISBN == "161729456X")
.ToList();
}
}
}
### The issue
Describe what is not working as expected.
Exception message:
System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.String'.
```
Further technical details
MySQL version: 8.0.11
Operating system: Windows 10
Pomelo.EntityFrameworkCore.MySql version: 3.1.1
Microsoft.AspNetCore.App version: 3.1
UPDATE: Tried the SQLite EF Core Database Provider instead and there was no exception raised.
mguinness
All 14 comments
When adding the data using the .HasData() method, the following exceptions is being thrown:
System.InvalidOperationException: The seed entity for entity type 'Book' cannot be added because there was no value provided for the required property 'ISBN13'.
at at Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.ValidateData(IModel model, IDiagnosticsLogger`1 logger)
at at Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger)
at at Microsoft.EntityFrameworkCore.Infrastructure.RelationalModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger)
at at Microsoft.EntityFrameworkCore.SqlServer.Internal.SqlServerModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger)
at at Microsoft.EntityFrameworkCore.Metadata.Conventions.ValidatingConvention.ProcessModelFinalized(IConventionModelBuilder modelBuilder, IConventionContext`1 context)
at at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.ImmediateConventionScope.OnModelFinalized(IConventionModelBuilder modelBuilder)
at at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.OnModelFinalized(IConventionModelBuilder modelBuilder)
at at Microsoft.EntityFrameworkCore.Metadata.Internal.Model.FinalizeModel()
at at Microsoft.EntityFrameworkCore.ModelBuilder.FinalizeModel()
...
The .HasPrincipalKey() method implicitly makes the Book.ISBN13 property an alternate key/unique index, for which EF Core does not allow null values.
According to StackOverflow, this seems to be a SQL Server specific behavior, since most other major DMBS' allow multiple NULL values in unique indices.
It might be better for the relational default validator to allow multiple null values by default and for a SQL Server specific validator implementation to customize this default behavior. Of course every provider could also just explicitly implement the same behavior by itself as a workaround, but your original exception might be a hint, that some changes are needed, to support null values for unique indices/alternate keys. https://github.com/dotnet/efcore/issues/8088 seems to be related. /cc @ajcvickers, @smitpatel
The following shows the code (based on yours), that replicates this behavior and should throw in all relational database providers (here shown for SQL Server):
```c#
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
namespace IssueConsoleTemplate
{
public class Book
{
public string ISBN { get; set; }
public string ISBN13 { get; set; }
public string Title { get; set; }
public string Author { get; set; }
public ICollection
}
public class Format
{
public string ISBN13 { get; set; }
public string Type { get; set; }
}
public class Context : DbContext
{
public DbSet<Book> Books { get; set; }
public DbSet<Format> Formats { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
// .UseMySql(
// "server=127.0.0.1;port=3308;user=root;password=;database=Issue1095",
// b => b.ServerVersion("8.0.20-mysql"))
.UseSqlServer(@"Data Source=.\MSSQL14;Integrated Security=SSPI;Initial Catalog=Issue1095")
.UseLoggerFactory(
LoggerFactory.Create(
b => b
.AddConsole()
.AddFilter(level => level >= LogLevel.Information)))
.EnableSensitiveDataLogging()
.EnableDetailedErrors();
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Book>(
entity =>
{
entity.HasKey(a => a.ISBN);
//entity.HasAlternateKey(a => a.ISBN13);
//entity.HasIndex(a => a.ISBN13)
// .IsUnique();
entity.Property(a => a.ISBN13)
.IsRequired(false);
entity.HasMany(a => a.Formats)
.WithOne()
.HasForeignKey(a => a.ISBN13)
.HasPrincipalKey(a => a.ISBN13);
/*
INSERT INTO `Books` (`ISBN`, `ISBN13`, `Title`, `Author`)
VALUES ('161729456X', '9781617294563', 'Entity Framework Core in Action', 'Jon P Smith');
INSERT INTO `Books` (`ISBN`, `ISBN13`, `Title`, `Author`)
VALUES ('1788478126', NULL, 'C# 8.0 and .NET Core 3.0', 'Mark J. Price');
*/
entity.HasData(
new Book
{
ISBN = "161729456X",
ISBN13 = "9781617294563",
Title = "Entity Framework Core in Action",
Author = "Jon P Smith",
},
new Book
{
ISBN = "1788478126",
ISBN13 = null,
Title = "C# 8.0 and .NET Core 3.0",
Author = "Mark J. Price",
});
});
modelBuilder.Entity<Format>(
entity =>
{
entity.HasKey(a => new {a.ISBN13, a.Type});
/*
INSERT INTO `Formats` (`ISBN13`, `Type`) VALUES ('9781617294563', 'Hardcover');
INSERT INTO `Formats` (`ISBN13`, `Type`) VALUES ('9781617294563', 'Paperback');
*/
entity.HasData(
new Format
{
ISBN13 = "9781617294563",
Type = "Hardcover",
},
new Format
{
ISBN13 = "9781617294563",
Type = "Paperback",
});
});
}
}
internal static class Program
{
private static void Main()
{
using var context = new Context();
context.Database.EnsureDeleted(); // <-- already throws
context.Database.EnsureCreated();
var books = context.Books
.Include(a => a.Formats)
.ToList();
Debug.Assert(books.Count == 2);
Debug.Assert(books[0].ISBN13 == "9781617294563");
Debug.Assert(books[0].Formats.Count == 2);
}
}
}
```
lauxjpn
on 3 Jun 2020
@lauxjpn - Actually the behavior you are requesting is already in EF Core. EF Core allows unique indexes over nullable properties and work-arounds SqlServer limitation in SqlServer provider only.
The issue here Book.ISBN13 is an alternate key because it is a target of an FK. EF Core does not allow null columns for any key (primary or alternate). Here is the tracking issue for https://github.com/dotnet/efcore/issues/4415 for feature which allow nullable columns for alternate key also. Till then, all properties of alternate keys need to be required.
smitpatel
on 3 Jun 2020
Thanks for your detailed (as usual) response Laurents. Whilst all you say is true, I'm primarily focused on querying and the throwing of the InvalidCastException is problematic.
I tried to look for the code where GetFieldValue
I would think that when ADO.NET is returning System.DBNull then the type default of null would be returned? I assume this is what the SQLite provider does and possibly PostgreSQL too (maybe @roji could chime in also).
mguinness
on 3 Jun 2020
@mguinness not sure exactly sure what you're asking, but at the ADO.NET level calling GetFieldValue/GetValue/GetAnything throws if the specified field contain null. If the field contains null, then an IsDBNull check must be done before trying to get the value. If you're looking to change how EF interacts with the ADO.NET DbDataReader, an interceptor might come in handful.
But I'd definitely recommend understanding @smitpatel's comment first, and possibly changing the model. If what you want is a unique index, then there shouldn't be any problem with nullability anywhere and everything should work; if you need a relationship, that's a different story.
roji
on 4 Jun 2020
Thanks Shay, to your point where would the IsDBNull() check be, in this provider or upstream? I suspect it isn't being done in this provider, but it is in others like SQLite? Why can't DBNull returned from the generated SQL be set to null instead of throwing an exception?
Unfortunately I can't change the model, but I need a way to query it through EF Core. The only alternative I have otherwise is to make the field not nullable and use a default value which is less than desirable.
mguinness
on 4 Jun 2020
@mguinness The IsDBNull method is in System.Data/ADO.NET, and the behavior of throwing for null is just how the API works - that's how .NET database drivers behave. One good reason for this is that GetFieldValue you can't return null for value types, such as int. The interaction between an EF Core relational provider (such as Pomelo.EntityFrameworkCore.MySql) and its DbDataReader is typically implemented upstream in EFCore.Relational (although a provider may of course override and customize).
But in any case, the issue here isn't really about the low-level interaction between EF Core and ADO.NET - it's about EF Core not supporting null values in keys. In other words, even if you were to hack this via an interceptor to make the exception go away, it's likely that other aspects of EF Core might break as nulls simply aren't expected for keys.
roji
on 4 Jun 2020
@mguinness A simple way to workaround this issue is to just not use a navigation property (that is handled by EF Core), but to manually use a join when querying the related data. So you don't define the FK in the model definition, and thereby you don't force Book.ISBN13 to be a (unique) key.
Sample code demonstrating the manual LEFT JOIN
```c#
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
namespace IssueConsoleTemplate
{
public class Book
{
public string ISBN { get; set; }
public string ISBN13 { get; set; }
public string Title { get; set; }
public string Author { get; set; }
// An automatic navigation property will not work here.
// You can manually query the related entities though.
// public ICollection<Format> Formats { get; set; }
}
public class Format
{
public string ISBN13 { get; set; }
public string Type { get; set; }
}
public class Context : DbContext
{
public DbSet<Book> Books { get; set; }
public DbSet<Format> Formats { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseMySql(
"server=127.0.0.1;port=3308;user=root;password=;database=Issue1095",
b => b.ServerVersion("8.0.20-mysql"))
// .UseSqlServer(@"Data Source=.\MSSQL14;Integrated Security=SSPI;Initial Catalog=Issue1095")
.UseLoggerFactory(
LoggerFactory.Create(
b => b
.AddConsole()
.AddFilter(level => level >= LogLevel.Information)))
.EnableSensitiveDataLogging()
.EnableDetailedErrors();
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Book>(
entity =>
{
entity.HasKey(a => a.ISBN);
entity.HasIndex(a => a.ISBN13)
.IsUnique(); // <-- not unique index (but not key)
// This cannot be used. Manually querying via LEFT JOIN works however.
/*
entity.HasMany(a => a.Formats)
.WithOne()
.HasForeignKey(a => a.ISBN13)
.HasPrincipalKey(a => a.ISBN13);
*/
/*
INSERT INTO `Books` (`ISBN`, `ISBN13`, `Title`, `Author`)
VALUES ('161729456X', '9781617294563', 'Entity Framework Core in Action', 'Jon P Smith');
INSERT INTO `Books` (`ISBN`, `ISBN13`, `Title`, `Author`)
VALUES ('1788478126', NULL, 'C# 8.0 and .NET Core 3.0', 'Mark J. Price');
*/
entity.HasData(
new Book
{
ISBN = "161729456X",
ISBN13 = "9781617294563",
Title = "Entity Framework Core in Action",
Author = "Jon P Smith",
},
new Book
{
ISBN = "1788478126",
ISBN13 = null,
Title = "C# 8.0 and .NET Core 3.0",
Author = "Mark J. Price",
});
});
modelBuilder.Entity<Format>(
entity =>
{
entity.HasKey(a => new {a.ISBN13, a.Type});
/*
INSERT INTO `Formats` (`ISBN13`, `Type`) VALUES ('9781617294563', 'Hardcover');
INSERT INTO `Formats` (`ISBN13`, `Type`) VALUES ('9781617294563', 'Paperback');
*/
entity.HasData(
new Format
{
ISBN13 = "9781617294563",
Type = "Hardcover",
},
new Format
{
ISBN13 = "9781617294563",
Type = "Paperback",
});
});
}
}
internal static class Program
{
private static void Main()
{
using var context = new Context();
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
var bookAndFormatsQuery = from b in context.Books
join f in context.Formats on b.ISBN13 equals f.ISBN13 into g
from f in g.DefaultIfEmpty()
select new { b, f };
var booksAndFormatsResult = bookAndFormatsQuery
.AsEnumerable()
.GroupBy(g => g.b, g => g.f)
.ToList();
Debug.Assert(booksAndFormatsResult.Count == 2);
Debug.Assert(booksAndFormatsResult[0].Key.ISBN13 == "9781617294563");
Debug.Assert(booksAndFormatsResult[0].Count() == 2);
Debug.Assert(booksAndFormatsResult[0].First().ISBN13 == "9781617294563");
}
}
}
</details>
After manually querying the related `Format` data, you can also save it (more or less read-only) in your `Book` object:
<details>
<summary>Sample code that uses a manually managed Book.Formats property</summary>
```c#
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
namespace IssueConsoleTemplate
{
public class Book
{
public string ISBN { get; set; }
public string ISBN13 { get; set; }
public string Title { get; set; }
public string Author { get; set; }
// An automatic navigation property will not work here.
// You can manually query the related entities though.
public ICollection<Format> Formats { get; set; } // <-- ignored for EF Core
}
public class Format
{
public string ISBN13 { get; set; }
public string Type { get; set; }
}
public class Context : DbContext
{
public DbSet<Book> Books { get; set; }
public DbSet<Format> Formats { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseMySql(
"server=127.0.0.1;port=3308;user=root;password=;database=Issue1095",
b => b.ServerVersion("8.0.20-mysql"))
// .UseSqlServer(@"Data Source=.\MSSQL14;Integrated Security=SSPI;Initial Catalog=Issue1095")
.UseLoggerFactory(
LoggerFactory.Create(
b => b
.AddConsole()
.AddFilter(level => level >= LogLevel.Information)))
.EnableSensitiveDataLogging()
.EnableDetailedErrors();
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Book>(
entity =>
{
entity.HasKey(a => a.ISBN);
entity.HasIndex(a => a.ISBN13)
.IsUnique(); // <-- not unique index (but not key)
// This cannot be used. Manually querying via LEFT JOIN works however.
/*
entity.HasMany(a => a.Formats)
.WithOne()
.HasForeignKey(a => a.ISBN13)
.HasPrincipalKey(a => a.ISBN13);
*/
// We will use this property to manually handle related entities.
entity.Ignore(a => a.Formats);
/*
INSERT INTO `Books` (`ISBN`, `ISBN13`, `Title`, `Author`)
VALUES ('161729456X', '9781617294563', 'Entity Framework Core in Action', 'Jon P Smith');
INSERT INTO `Books` (`ISBN`, `ISBN13`, `Title`, `Author`)
VALUES ('1788478126', NULL, 'C# 8.0 and .NET Core 3.0', 'Mark J. Price');
*/
entity.HasData(
new Book
{
ISBN = "161729456X",
ISBN13 = "9781617294563",
Title = "Entity Framework Core in Action",
Author = "Jon P Smith",
},
new Book
{
ISBN = "1788478126",
ISBN13 = null,
Title = "C# 8.0 and .NET Core 3.0",
Author = "Mark J. Price",
});
});
modelBuilder.Entity<Format>(
entity =>
{
entity.HasKey(a => new {a.ISBN13, a.Type});
/*
INSERT INTO `Formats` (`ISBN13`, `Type`) VALUES ('9781617294563', 'Hardcover');
INSERT INTO `Formats` (`ISBN13`, `Type`) VALUES ('9781617294563', 'Paperback');
*/
entity.HasData(
new Format
{
ISBN13 = "9781617294563",
Type = "Hardcover",
},
new Format
{
ISBN13 = "9781617294563",
Type = "Paperback",
});
});
}
}
internal static class Program
{
private static void Main()
{
using var context = new Context();
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
var bookAndFormatsQuery = context.Books
.GroupJoin(context.Formats, b => b.ISBN13, f => f.ISBN13, (b, g) => new {b, g})
.SelectMany(t => t.g.DefaultIfEmpty(), (t, f) => new {@t.b, f});
var booksAndFormatsResult = bookAndFormatsQuery
.AsEnumerable()
.GroupBy(g => g.b, g => g.f)
.ToList();
foreach (var group in booksAndFormatsResult)
{
group.Key.Formats = new List<Format>(group);
}
var books = booksAndFormatsResult
.Select(g => g.Key)
.ToList();
Debug.Assert(books.Count == 2);
Debug.Assert(books[0].ISBN13 == "9781617294563");
Debug.Assert(books[0].Formats.Count == 2);
Debug.Assert(books[0].Formats.First().ISBN13 == "9781617294563");
}
}
}
If this is done repeatedly in the code, you might want to consider refactoring this into a two extension methods, one that works against an IQueryable<Book> or DbSet<Book> (and does the LEFT JOIN work) and the other that does the post-query work.
lauxjpn
on 4 Jun 2020
Thanks Shay, it would appear that I'd have to wait for https://github.com/dotnet/efcore/issues/4415. In the meantime can you share how an interceptor would be able to avoid the exception?
Thanks Laurents, I use dynamically built predicates so manual joins are something I'd like to avoid. I appreciate the sample code.
I'm still curious as to why the SQLite provider doesn't get this exception, but SqlServer, PostgreSQL & MySQL do (each with a different message). I would suggest for consistency that when the field is not nullable, upstream would then check IsDBNull() and if true would throw an exception with a message like "Key field cannot contain NULL value".
mguinness
on 4 Jun 2020
In the meantime can you share how an interceptor would be able to avoid the exception?
Unfortunately the real docs on this haven't been written yet, but here's the announcement with some minimal code to get you started. Try googling for ef core interceptors too, there are some tutorials out there.
I'm still curious as to why the SQLite provider doesn't get this exception, but SqlServer, PostgreSQL & MySQL do
Any chance you can create an issue with a quick runnable program showing this, on https://github.com/dotnet/efcore?
roji
on 4 Jun 2020
@mguinness The thing is, even if you get the null value out of the database table into your entity (which might be possible with some hacking), you would still need to accomplish the same the other way around, when you update or add Book entities (assuming your app needs to provide that functionality), where EF Core would throw again:
System.InvalidOperationException: Unable to track an entity of type 'Book' because alternate key property 'ISBN13' is null. If the alternate key is not used in a relationship, then consider using a unique index instead. Unique indexes may contain nulls, while alternate keys must not.
at at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.NullableKeyIdentityMap`1.Add(InternalEntityEntry entry)
at at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.StartTracking(InternalEntityEntry entry)
at at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.InternalEntityEntry.SetEntityState(EntityState oldState, EntityState newState, Boolean acceptChanges, Boolean modifyProperties)
at at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.InternalEntityEntry.SetEntityState(EntityState entityState, Boolean acceptChanges, Boolean modifyProperties, Nullable`1 forceStateWhenUnknownKey)
at at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.EntityGraphAttacher.PaintAction(EntityEntryGraphNode`1 node)
at at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.EntityEntryGraphIterator.TraverseGraph[TState](EntityEntryGraphNode`1 node, Func`2 handleNode)
at at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.EntityGraphAttacher.AttachGraph(InternalEntityEntry rootEntry, EntityState targetState, EntityState storeGeneratedWithKeySetTargetState, Boolean forceStateWhenUnknownKey)
at at Microsoft.EntityFrameworkCore.DbContext.SetEntityState(InternalEntityEntry entry, EntityState entityState)
at at Microsoft.EntityFrameworkCore.DbContext.SetEntityState[TEntity](TEntity entity, EntityState entityState)
at at Microsoft.EntityFrameworkCore.DbContext.Add[TEntity](TEntity entity)
at at Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.Add(TEntity entity)
at IssueConsoleTemplate.Program.Main() in E:\Sources\PomeloIssues\Issue1095\IssueConsoleTemplate\Program.cs:118
It's probably easier to adjust your predicate generation logic, to work with manual joins.
If you really want to go the interceptor route, a way to do this could be to funnel the null values through EF Core by replacing them with empty or magic strings and those empty or magic strings later again with null. Some regular expressions or some parsing logic (state machine) will be necessary for this, depending on your scenario.
Of course you could also ship (and maintain) your own version of EF Core (which might be painful) or use harmony to do some tweaking (usually much less painful).
But you are definitely in hacking territory with both of the latter approaches.
lauxjpn
on 4 Jun 2020
Thanks to Laurents, Shay and others with their suggestions and workarounds which I'll look into further. I'm going to close issue now as this isn't a bug per se, but a limitation which will hopefully be addressed when https://github.com/dotnet/efcore/issues/4415 is considered for a future release.
Any chance you can create an issue with a quick runnable program showing this, on https://github.com/dotnet/efcore?
Yes, I'll double check the SQLite example and if confirmed I'll post a new issue. Thanks again all.
mguinness
on 4 Jun 2020
So I must've had an empty string instead of a NULL value when testing SQLite which prevented the exception. The good news is that there is consistency between all the providers I tested (see below):
MySQL
System.InvalidCastException: 'Unable to cast object of type 'System.DBNull' to type 'System.String'.'
PostgreSQL
System.InvalidCastException: 'Column is null'
SqlServer
System.Data.SqlTypes.SqlNullValueException: 'Data is Null. This method or property cannot be called on Null values.'
SQLite
System.InvalidOperationException: 'The data is NULL at ordinal 2. This method can't be called on NULL values. Check using IsDBNull before calling.'
One final thought. I was reviewing the documentation for Alternate Keys but there's no mention about null values even though unique indexes in databases permit them.
mguinness
on 4 Jun 2020
https://docs.microsoft.com/en-us/ef/core/modeling/keys?tabs=data-annotations#key-types-and-values section also applies to alternate keys. Documentation should be update to make it clearer.
smitpatel
on 4 Jun 2020
Not amazing to see so many different exception types across providers, but at least it's consistent in that it throws...
roji
on 4 Jun 2020
Related issues
coultonluke
路
3Comments
lebartha
路
3Comments
mason-chase
路
4Comments
zuosc
路
3Comments
hainguyenthanh
路
3Comments