Left join not working as expected

[jazzonaut]

The following query:

var results = from call in db.Calls
                join fieldData in db.Fields on call.CallNumber equals fieldData.CallNumber into jj
                from j in jj.DefaultIfEmpty()
                select new
                {
                    Description = call.Description,
                    Value = j.Value
                };

Will result in SQL that will try to select all columns from db.Fields, instead of just one (Value).
Also, if there is nothing on the right (i.e. when 'j' is technically null), it will result in an exception:

Exception thrown: 'System.NullReferenceException' in Microsoft.EntityFrameworkCore.dll
Additional information: Object reference not set to an instance of an object.

   at lambda_method(Closure , TransparentIdentifier`2 )
   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

Using build 20620 from the 19th or April.

[maumar]

@amokre this is a known limitation of EF currently (#4588). Basically everything that will happen after DefaultIfEmpty is evaluated on the client, hence we are projecting all the columns and you are getting null ref. Experience is better if you use navigation properties directly (if they are available in your model) as we inject null propagation logic into projection:

from call in db.Calls
select new { Description = call.Description, Value = call.FieldData.Value }

note that it Field.Value is not nullable (e.g. int) you will have to explicitly cast it to Nullable otherwise you will get materialization error, trying to fit null into int property of the anonymous type.