On Azure, I have a setup of several sharded databases and an elastic query database with external tables mirroring the tables on the shards. The two main tables I use are:
- Channels:
- [Name] nvarchar NOT NULL,
- [Id] [uniqueidentifier] NOT NULL,
- [ExternalReference] nvarchar NULL
- Users:
- [Email] nvarchar NOT NULL,
- [FirstName] nvarchar NOT NULL,
- [LastName] nvarchar NOT NULL,
- [ChannelId] [uniqueidentifier] NOT NULL,
- [Status] [int] NOT NULL,
- [AvatarId] [uniqueidentifier] NULL,
- [Id] [uniqueidentifier] NOT NULL
When I query this via EF and linq:
var user = db.Users.Include("Channel").FirstOrDefault(u => u.Email == "tony@soprano.com");
I get an error:
An error occurred while executing GlobalQuery operation: Nullable object must have a value.
This is what the User class looks like:
public class User
{
public Guid Id { get; set; } = SequentialGuid.NewGuid();
[Required]
public string Email { get; set; }
[Required]
public string FirstName { get; set; }
[Required]
public string LastName { get; set; }
[Index]
public Status Status { get; set; }
public Guid? AvatarId { get; set; }
[Index]
public Guid ChannelId { get; set; }
[ForeignKey("ChannelId")]
public virtual Channel Channel { get; set; }
}
Querying directly via T-SQL:
SELECT * FROM Users INNER JOIN Channels ON Users.ChannelId = Channels.Id
gives me the same error.
Further investigation shows that casting the Ids to uniqueidentifiers (which they already are) solves the problem:
SELECT * FROM Users INNER JOIN Channels ON CAST(Users.ChannelId as uniqueidentifier) = CAST(Channels.Id as uniqueidentifier)
The ChannelId and Id Columns are already non nullable uniqueidentifiers. The data inside the shards is also valid and not null, so what exactly is the problem here?
Another question: how can I force that ‘cast to uniqueidentifier’ in linq?
