Null value in group by after a left outer join in EF Core

c# ef-core-2.2 entity-framework entity-framework-core

Question

i have a problem with a linq query where i try to get certain information out of it and simply can't find where the problem is. I have Tools, Tasks and a M-N ToolTask entity in my ef core db. This query includes 2 left outer joins and a group by at the end.

Even though i am checking if the grouped values are not null i am still getting an error message like "Nullable object must have a value". What am i missing here?

Also it seems like this linq query is evaluated client side, is there anything i can do to make it server side? It has been server side when i didn't have the "let maxDateV..." line in the code for some reason.

var max = (from t in _fabContext.Tools

join tt in _fabContext.ToolTask on t.ToolId equals tt.ToolId into tt1
from tt in tt1.DefaultIfEmpty()

join ts in _fabContext.Tasks on tt.TaskId equals ts.TaskId into ts1
from ts in ts1.DefaultIfEmpty()

group tt by tt.ToolId into g
let maxOrderV = g.Max(c => c != null ? c.Order : 0)
let maxDateV = g.Max(c => c != null ? c.Task.ExportDate : DateTime.MinValue)
select new
{
  ToolId = g.Key,
  MaxOrder = maxOrderV,
  MaxExportDate = maxDateV
}).ToDictionary(d => d.ToolId, d => 
    new OrderExportDate { 
        Order = d.MaxOrder, 
        ExportDate = d.MaxExportDate 
    });

Update 1 (Entity classes):

Task

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;

namespace Main.DataLayer.EfClasses
{
    public class Task
    {
        public int TaskId { get; private set; }
        [Required]
        public string Name { get; private set; }
        [Required]
        public int ProfileId { get; private set; }
        [Required]
        public DateTime ExportDate { get; private set; }

        private HashSet<ToolTask> _toolTask;
        public IEnumerable<ToolTask> ToolTask => _toolTask?.ToList();

        private Task()
        {
        }

        public Task(
            string name,
            int profileId,
            DateTime exportDate)
        {
            Name = name;
            ProfileId = profileId;
            ExportDate = exportDate;
        }
    }
}

ToolTask

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;

namespace Main.DataLayer.EfClasses
{
    public class ToolTask
    {
        public int ToolId
        {
            get; private set;
        }

        public Tool Tool
        {
            get; private set;
        }

        public int TaskId
        {
            get; private set;
        }

        public Task Task
        {
            get; private set;
        }

        [Required]
        public int SortOrder
        {
            get; private set;
        }

        private ToolTask() { }

        internal ToolTask(Tool tool, Task task, int sortOrder)
        {
            Tool = tool;
            ToolId = tool.ToolId;
            Task = task;
            TaskId = task.TaskId;
            SortOrder = sortOrder;
        }

        public void ChangeOrder(int order)
        {
            SortOrder = order;
        }
    }
}

Tool

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;
using Main.DataLayer.EfCode;

namespace Main.DataLayer.EfClasses
{
    public class Tool
    {
        public int ToolId
        {
            get; private set;
        }

        [Required]
        public string Name
        {
            get; private set;
        }

        [Required]
        public string Model
        {
            get; private set;
        }

        private HashSet<ToolTask> _toolTask;
        public IEnumerable<ToolTask> ToolTask => _toolTask?.ToList();

        internal Tool() { }

        public Tool(string name, string model)
        {
            Name = name;
            Model = model;

            _toolTask = new HashSet<ToolTask>();
        }

        public void AddTask(Task task, int sortOrder)
        {
            _toolTask?.Add(new ToolTask(this, task, sortOrder));
        }
    }
}

Each entity class has an own configuration class, but there is nothing huge in it apart from setting primary keys and the access mode for the backing fields.

1
1
3/11/2019 8:06:35 AM

Accepted Answer

The problem with that implementation is that you group by tt.ToolId, where tt is coming from the "right" side of the left outer join, hence tt can be null.

You can easily fix that by using the corresponding field from the "left" side, e.g. group tt by t.ToolId into g.

But that won't make to query fully translatable. In order to do that, you need (at least with the current EF Core query translator) to follow some rules when building the LINQ query:

  1. Use navigation properties rather than manual joins.
  2. Specifically for GroupBy queries with aggregates, always use the {element} part of the group {element} by {key} construct to preselect all the expressions you need inside aggregate methods.
  3. In SQL (server side) queries, nulls are supported naturally, even when the data type is non nullable and is coming from the optional side of the joins. But not in C#, so you have to specify that explicit using cast.
  4. Always use nullable overloads of Min and Max. Convert the result (not the operands) to sentinel value if needed. But better keep it null and nullable type. Avoid DateTime.MinValue because it has no representation in SqlServer.

Applying them to your query:

var max =
    (from t in _fabContext.Tools
     from tt in t.ToolTask.DefaultIfEmpty()
     let ts = tt.Task
     group new
     {
         SortOrder = (int?)tt.SortOrder,
         ExportDate = (DateTime?)ts.ExportDate
     }
     by new { t.ToolId }
     into g
     select new
     {
         ToolId = g.Key.ToolId,
         MaxOrder = g.Max(e => e.SortOrder) ?? 0,
         MaxExportDate = g.Max(e => e.ExportDate)
     })
    .ToDictionary(d => d.ToolId, d => new OrderExportDate
    {
        Order = d.MaxOrder,
        ExportDate = d.MaxExportDate ?? DateTime.MinValue
    });

which nicely translates to a single SQL query

  SELECT [t].[ToolId], MAX([t.ToolTask].[SortOrder]) AS [MaxOrder], MAX([t.ToolTask.Task].[ExportDate]) AS [MaxExportDate]
  FROM [Tool] AS [t]
  LEFT JOIN [ToolTask] AS [t.ToolTask] ON [t].[ToolId] = [t.ToolTask].[ToolId]
  LEFT JOIN [Task] AS [t.ToolTask.Task] ON [t.ToolTask].[TaskId] = [t.ToolTask.Task].[TaskId]
  GROUP BY [t].[ToolId]

which is exactly what people with SQL background would expect.

If you don't care about the shape of the translated SQL query, you could avoid group by and just write the LINQ query in a simple logical way:

var max2 = _fabContext.Tools
    .Select(t => new
    {
        t.ToolId,
        MaxOrder = t.ToolTask.Max(tt => (int?)tt.SortOrder),
        MaxExportDate = t.ToolTask.Max(tt => (DateTime?)tt.Task.ExportDate)
    })
    .ToDictionary(d => d.ToolId, d => new OrderExportDate
    {
        Order = d.MaxOrder ?? 0,
        ExportDate = d.MaxExportDate ?? DateTime.MinValue
    });
2
3/11/2019 8:05:45 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow