Works in LinqPad, but not in EF Core

Question

This easy linq query works in LinqPad 5, but not in EF Core. When I run this i get "System.NullReferenceException: Object reference not set to an instance of an object.". But on what ? I have checked the context and all contexts has data.

var query = "kost";
var specialtyPageIds = 
(from pageSpecialty in Page_Specialties
join s in Specialties on pageSpecialty.SpecialtyId equals s.Id into ps
from s in ps.DefaultIfEmpty()
join cs in CustomSpecialties on pageSpecialty.CustomSpecialtyId equals cs.Id into pcs                                                                       
from cs in pcs.DefaultIfEmpty()
where s.Name.Contains(query) || cs.Name.Contains(query)
select pageSpecialty).ToList();

You can use Step-Into in the debugger to see exactly which item is the issue. We won't really be able to help more than that, because almost any of those items could be null. — vbnet3d, Dec 23 '16 at 16:56
I just realised that SpecialtyId and CustomSpecialtyId is nullable ints. Can that be the problem ? — n3tx, Dec 23 '16 at 16:59
Possible duplicate of [What is a NullReferenceException, and how do I fix it?](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — Owen Pauling, Dec 23 '16 at 17:01
It is certainly possible. If you have a way to force them to a value of 0 when a null is found, that might solve your issue. — vbnet3d, Dec 23 '16 at 17:02
I get the error on this: public int Id { get; set; } But it is autogenerated by the db so it cant be null or 0. — n3tx, Dec 23 '16 at 17:24
It works if I remove the "from s.in ps.DefaultIfEmpty()" but then I dont get any results because of the left join. — n3tx, Dec 23 '16 at 18:05
DefaultIfEmpty() is equivalent to doing a left join. So there is no guarantee there is a corresponding "s" or "cs" value. — Keith, Dec 23 '16 at 18:18
@Keith The statement is translated into SQL, so there are no null reference exceptions there. n3tx, look at the stack trace, the exception probably from some internal EF method. — Gert Arnold, Dec 23 '16 at 21:01
This works without the where clause, var test = (from ps in _context.PageSpecialties join s in _context.Specialties on new { Specialty = ps.SpecialtyId } equals new { Specialty = (int?)s.Id} into xy from x in xy.DefaultIfEmpty() //where x.Name.Contains(query) select new { Id = ps.Id, PageId = ps.PageId, Specialty = x }).ToList(); — n3tx, Dec 23 '16 at 21:07
System.NullReferenceException: 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.<_trackentities>d__15`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 sourc — n3tx, Dec 23 '16 at 21:10
Ok, Do you know another way to solve this ? What I am trying to achieve is - I am trying to get all the results in the PageSpecialties table that has SpecialtyId and CustomSpecialtyId and both columns are nullable, and I am trying to query the specialty.Name OR customSpecialty.Name by a text param — n3tx, Dec 23 '16 at 21:32

score 1 · Answer 1 · answered Dec 24 '16 at 14:20

There is nothing wrong with your query, but EF Core still has issues with LEFT OUTER JOIN (and many other constructs) query translation.

As a workaround I could suggest an alternative equivalent query based on LINQ Any conditions which are succesfully translated to SQL EXISTS:

from ps in Page_Specialties
where Specialties.Any(s => s.Id == ps.SpecialtyId && s.Name.Contains(query)) ||
   CustomSpecialties.Any(cs => cs.Id == ps.CustomSpecialtyId && cs.Name.Contains(query))
select ps

Works in LinqPad, but not in EF Core

1 Answers1