GroupJoin, SelectMany, GroupBy and Sum

c# entity-framework entity-framework-6 linq

Question

I am trying to do a 'left outer join' in linq using GroupJoin and SelectMany, but then I also want to aggregate the result using GroupBy and Sum.

But when I execute the code below, I get:

System.NotSupportedException: 'The entity or complex type '...tableB' cannot be constructed in a LINQ to Entities query.'

Repo<tableA>().All()
    .Where(i =>
        (i.Date >= dateF && i.Date <= dateT)
        &&
        i.EndOfMonth
    )
    .GroupJoin(
        Repo<tableB>().All().Where(i => (i.fieldX = ...somevalue... )), 
        dt => dt.DayIndex, scd => scd.DayIndex, (dt, scd) =>
            new {
                dt = dt,
                scd = scd,
            }
    )
    .SelectMany(
        jn => jn.scd.DefaultIfEmpty( new tableB { Count1 = 0, Count2 = 0 }), // runtime error here
        (dt,scd) => new { dt=dt.dt, scd = scd}
        )
    .GroupBy(i => i.dt)
    .Select(i => new CountListItem
    {
        Date = i.Key.Date,
        CountField1 = i.Sum(o => o.scd.Count1),
        CountField2 = i.Sum(p => p.scd.Count2)
     })
     .OrderBy(i => i.Date)
     .ToList()

When I just do DefaultIfEmpty() I get the error:

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

I am assuming, I must admit, this is because Sum encounters null values.

I tried i.Sum(o => o.scd.Count1 ?? 0, but then it says:

Operator '??' cannot be applied to operands of type 'int' and 'int' [sic]

I also tried DefaultIfEmpty(new { Count1 = 0, Count2 =0}) but this gives me>

... type cannot be inferred.

How can I get this to work?

1
3
2/27/2018 6:00:11 PM

Accepted Answer

Avoiding the first exception is easy - simply use the standard pattern for left outer join with parameterless DefaultIfEmpty().

The second problem originates from the difference between SQL and LINQ (C#) query data types. SQL queries support NULL values natively and can return NULL even if the source expression is not nullable. As you noticed with the last attempt, LINQ (and in particular C# compiler) is not happy with that syntax.

The trick is to promote the non nullable type to nullable using the C# cast operator and then apply the null-coalescing operator to the resulting expression:

CountField1 = i.Sum(o => (int?)o.scd.Count1 ?? 0),
CountField2 = i.Sum(o => (int?)o.scd.Count2 ?? 0),
2
2/27/2018 6:28:29 PM


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