EF Core Left Outer Join failing with nullable object must have a value

c# entity-framework entity-framework-core

Question

I am currently struggling with a EntityFramework Core query. Basically I am applying a LEFT OUTER JOIN (besides of other INNER JOINS).

The first table is Solutions and is basically the foundation, a separate table 'SolutionViews' tracks each single view of a Solution, so let's say the solution with Id 1 has 2 views, you will find 2 rows in SolutionViews that point to the SolutionId. At the end I aggregate all the rows based on the SolutionId to get the total views per solution.

Here's the SQL Query that I am basically trying to replicate:

SELECT solution.Id, solution.Name, category.Name, Count(solutionView.SolutionId) As 'Views'
FROM Solutions solution
JOIN Categories category on solution.CategoryId = category.Id
LEFT JOIN SolutionViews solutionView on solution.Id = solutionView.SolutionId
GROUP BY solution.Id, solution.Name, category.Name
ORDER BY Views desc

The result of that query looks like this:

Id  Name        CategoryId  Name        Views
9   Solution4   3           Category1   3
1   Solution1   1           Category2   2
2   Solution2   2           Category1   1
8   Solution3   3           Category3   0
10  Solution5   3           Category3   0

Using EF Core 2.1 I've tried at first the I think so called 'fluent' syntax (as I liked the concept) with just using LINQ, but switchedo ver to the query syntax below:

    var query = (from solution in context.Solutions
                 join category in context.Categories
                 on solution.CategoryId equals category.Id
                 join view in context.SolutionViews.GroupBy(sv => sv.SolutionId)
                     .Select(g => new {
                         SolutionId = g.Key,
                         Views = g.Count()
                     })
                 on solution.Id equals view.SolutionId into a
                 from b in a.DefaultIfEmpty()
                 select new SolutionWithViewsDto {
                     Solution = solution,
                     Views = b == null ? 0 : b.Views
                 }
        );

I left out the OrderBy as I am dynamically adding that later based on the API search specifications, but right now this query is providing me with an exception: System.InvalidOperationException: 'Nullable object must have a value.'

I'm pretty sure this originates from the fact that some records do not have any entries in the SolutionViews table, and hence I am doing the LEFT OUTER JOIN wrong.

1
1
6/28/2019 6:48:08 PM

Accepted Answer

Credits go to Ivan Stoev, I am just providing a visible answer to this. The query syntax seems to be behaving a bit buggy, but in the end what did work was using only Navigation Properties, which is much easier too. All I had to do was apply .Count() on the SolutionsView navigation property, which will give you the count of views for each individual Solution record.

var query = context.Solutions
                .Select(s => new SolutionDto {
                    Id = s.Id,
                    Name = s.Name,
                    Description = s.Description,
                    Category = new CategoryDto {
                        Id = s.Category.Id,
                        Name = s.Category.Name
                    },
                    Views = s.SolutionViews.Count()
                });
0
7/2/2019 1:01:04 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