Grouping by primary key of a left-joined entity in Entity Framework causes exception

Question

I have a simple model with two entities:

public class Product
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [StringLength(150)] public string Name { get; set; }
}

public class Order
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public decimal? Amount { get; set; }

    public int? ProductId { get; set; }

    [ForeignKey(nameof(ProductId))] public Product Product { get; set; }
}

public class OrderContext : DbContext
{
    public DbSet<Product> Products { get; set; }

    public DbSet<Order> Orders { get; set; }
}

As you see, There is a nullable foreign key between the Order and Product. I initialized the DB with these records:

using (var db = new OrderContext())
{
    var p = new Product {Name = "Test"};
    db.Products.Add(p);

    db.Orders.Add(new Order {Product = p, Amount = 1});
    db.Orders.Add(new Order {ProductId = null, Amount = 2});

    db.SaveChanges();
}

When I execute the following query, an InvalidOperationException is raised:

var result = db.Orders.GroupBy(x => x.Product.Id).ToArray();

The exception message is:

System.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.

Is this a bug in EF and how to resolve it?

--

This is not duplicate of The cast to value type 'Int32' failed because the materialized value is null . Despite of the same exception message, the causes and solutions of the problems are completely different. The referred question is about aggregating an empty collection. But this is about grouping by the primary key of another left-joined table. I don't know why should these two questions be even similar!

Will there be any order without a product or amount? If not, why are these props nullable? — Sir Rufo, Jan 07 '18 at 08:01
Try changing from `x => x.Product.Id` to `x => x.ProductId` - this may solve the issue, although I agree, EF should be able to handle this case — Andrew Williamson, Jan 07 '18 at 08:22
Do the following both work... ```var orders = db.Orders.ToArray();``` and ```var products = db.Products.ToArray();``` ? — pmcilreavy, Jan 07 '18 at 08:49
"I don't know why should these two questions be even similar!" -- Because they are merely two different manifestations of the same underlying problem, as the answers to that other question explain. I'm not going to close this again just yet, because you write "the causes and solutions of the problems are completely different". The accepted answer to that other question *should* work for you as well. Do you really mean `db.Orders.GroupBy(x => (int?)x.Product.Id)` still gives you an exception? — , Jan 08 '18 at 09:45
@hvd It's true that the two problems have the same exception. But the "underlying" exceptions may have different sources. An exaggerated example could be the `NullReferenceException`. Anyway, The workaround solves the problem. And another is `db.Orders.GroupBy(x => x.ProductId)`. But in some libraries like ODATA, the query is generated dynamically and the query generation behavior is not customizable. — Khodaie, Jan 08 '18 at 12:35
@Khodaie I'm not guessing that they may have the same underlying problem here based just on the same exception message, I'm stating that they definitely do have the same underlying problem here, which is that C# type rules and SQL nullability rules have fundamentally different behaviour in some cases, C# type rules are used at compile time, but SQL nullability rules are used at run-time. Since you now confirmed that the answer to that other question does in fact work here, contrary to what your edit suggests, is it okay to close this question again? — , Jan 08 '18 at 12:46
Possible duplicate of [The cast to value type 'Int32' failed because the materialized value is null](https://stackoverflow.com/questions/6864311/the-cast-to-value-type-int32-failed-because-the-materialized-value-is-null) — Camilo Terevinto, Jan 09 '18 at 12:29

pmcilreavy · Accepted Answer · 2018-01-08T20:18:58.070

1

Sounds like you have a null value in the db where EF is expecting a non-nullable int32. For each of the int properties on your Order and Product models you'll need to check that their corresponding database columns are non-nullable and are of type int.

You could also try this to eliminate the nulls...

var result = db.Orders.Where(o => o.Product.Id.HasValue).GroupBy(x => x.Product.Id.Value).ToArray();

If you need null included in the results then you'd need to substitute null for something else (e.g. -1)...

var result = db.Orders.GroupBy(x => x.Product?.Id ?? -1).ToArray();

edited Jan 08 '18 at 20:18

answered Jan 07 '18 at 08:52

pmcilreavy

3,076
2
28
37

This solution eliminates the null values from the result. – Khodaie Jan 08 '18 at 12:36
@Khodaie Yes I believe I say this in the answer. You should edit your question to make it explicit that you need null included in the result. Anyway, I added one possible way to include nulls. – pmcilreavy Jan 08 '18 at 20:16

Grouping by primary key of a left-joined entity in Entity Framework causes exception

1 Answers1