Support DefaultIfEmpty and null coalescing operators (??) in batch updates (EF6)

[RudeySH]

I know this is a pretty long issue description, but please read it entirely. I've done my best to explain it.

---

Consider I have the following code-first model classes:

public class Parent
{
    [Key] public int Id { get; set; }
    public int TotalChildrenAge { get; set; }
    public ICollection<Child> Children { get; set; }
}

public class Child
{
    public int Age { get; set; }
}

I am using EntityFramework.Plus to batch-update TotalChildrenAge like so:

DbContext.Parents
    .Update(p => new Parent
    {
        Id = p.Id,
        TotalChildrenAge = p.Children.Sum(c => c.Age) // issue
    });

This code compiles, because LINQ's Sum returns int, thus not a nullable integer. However, in SQL aggregate functions like Sum do return null when the collection is empty. The following exception is thrown because the collection is empty:

SqlException (0x80131904): Cannot insert the value NULL into column 'TotalChildrenAge', table 'Example.dbo.Parent'; column does not allow nulls. UPDATE fails. The statement has been terminated.

---

To further explain this issue, I'll take a step back and use Sum in a plain Entity Framework SELECT statement:

var updatedParents = DbContext.Parents
    .Select(p => new
    {
        Id = p.Id,
        TotalChildrenAge = p.Children.Sum(c => c.Age)
    }
    .ToArray();

The following exception is thrown because the collection is empty:

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.

Entity Framework provides two ways of dealing with this. Either use DefaultIfEmpty:

        TotalChildrenAge = p.Children.Select(c => c.Age).DefaultIfEmpty().Sum()

Or use a null coalescing operator:

        TotalChildrenAge = p.Children.Sum(c => ((int?)c.Age) ?? 0

Both solutions allow us to get the desired behavior in a plain Entity Framework SELECT statement. After performing some tests myself, I've come to the conclusion that the latter one generates a cleaner SELECT statement and thus performs better.

---

Now, back to EntityFramework.Plus. Unfortunately, both solutions above are not compatible with batch updates. When using DefaultIfEmpty, the following exception is thrown:

SqlException (0x80131904): No column name was specified for column 2 of 'Project3'. Invalid column name 'C1'.

And when using a null coalescing operator, the following exception is thrown:

The multi-part identifier "Project6.C1" could not be bound. The multi-part identifier "Project6.C2" could not be bound.

Please note that the generated column names mentioned in these exceptions, or the exceptions themselves might differ for you if you try to reproduce this issue. I am using EF6 and SQL Server 2016.

In this simple example I am using an aggregate function but it could be possible that this issue is also present when using DefaultIfEmpty and null coalescing operators in other contexts. I am aware that I can easily fix the example by making TotalChildrenAge a nullable integer but that is not a solution for me.

---

To sum this issue up (pun intended), both DefaultIfEmpty and null coalescing operators need to be supported in batch updates.

[JonathanMagnan]

Hello @rlenders ,

Thank you for reporting.

I will look at this issue this week and see if I can find out a solution for DefaultIfEmpty or null coalescing.

Best Regards,

Jonathan

[JonathanMagnan]

Hello @rlenders ,

Unfortunately, I believe the fix will require to much code to rewrite for now. We will for sure try to support it when we will rewrite the v2.x but too much code needs to be modified to support it and side impact will for sure happen.

Even if it's not ideal, I would recommend you for now to perform two Update

// TEST
using (var ctx = new EntityContext())
{
	ctx.Parents.Where(x => !x.Children.Any())
		.Update(p => new Parent
		{
			TotalChildrenAge = 0
		});

	ctx.Parents.Where(x => x.Children.Any())
		.Update(p => new Parent
		{
			TotalChildrenAge = p.Children.Sum(x => x.Age)
		});
} 

Let me know if that solves your issue for now.

Best Regards,

Jonathan

[RudeySH]

That's what I've been doing before, and I guess I'll continue doing so.

Just for the sake of completeness, I also tried doing one batch update like so:

TotalChildrenAge = p.Children.Any() ? p.Children.Sum(c => c.Age) : 0

But that doesn't work also.

Thanks for your time!

[VSVeras]

TotalChildrenAge = p.Children.Sum(c => c.Age) ?? 0

[RudeySH]

@VSVeras I tried that, and mentioned it in my initial post. I got an exception when I tried that in an EFPlus batch update.

[RudeySH]

I find myself coming back to this issue every time I investigate if my update queries could be faster.

Compared to the suggested workaround (https://github.com/zzzprojects/EntityFramework-Plus/issues/232#issuecomment-329023897), I have found that it is faster to simply do a normal EF query with ??, loading all data into C# objects, and then do a bulk update.

Will EntityFramework-Plus ever support null coalescing in batch updates?