3

I am writing the Linq query as below: But on run its throwing the following error:

The method 'Join' cannot follow the method 'SelectMany' or is not supported. Try writing the query in terms of supported methods or call the 'AsEnumerable' or 'ToList' method before calling unsupported methods.

LINQ 

from a in AccountSet
join sm in new_schoolMemberSet on a.AccountId equals sm.new_OrganisationId.Id 
        into ps from suboc in ps.DefaultIfEmpty()
join sr in new_schoolRoleSet on suboc.new_SchoolRoleId.Id equals sr.new_schoolRoleId
where sr.new_name == "Manager"
where a.new_OrganisationType.Value == 430870007
select new { a.AccountId, suboc.new_schoolMemberName }

I am expecting the result as below:

enter image description here

I never used the Outer join in Linq before. So please correct me if I am doing it wrong.

Thanks

Scorpion
  • 4,495
  • 7
  • 39
  • 60

1 Answers1

1

The error seems pretty clear. The problem is not with the Linq, but with the provider not being able to translate your query into something it can execute on its end. To test this theory, you can simply add .ToList() to the end of AccountSet, new_schoolMemberSet, and new_schoolRoleSet. This won't be how you will want to run the query, but it'll act as a proof of whether or not the query is at fault, or the provider (based on the error, it's the provider, but this will still prove that the query is formed properly).

Adding ToList() to each of these collections will bring all the data into memory, and linq-to-objects will be used instead of linq-to-whateverYourLinqProviderIs. Some linq providers are simply not equipped to handle more complex queries. You can imagine how difficult it is to translate a linq query into a query format that the provider can understand. Additionally, some linq query concepts just don't translate into something that is possible for a specific provider.

So what's the fix? You want to make use of the linq providers ability to efficiently query for data, but it may be limited in what it can do. Try bringing the least amount of data possible into memory by using filtering, etc. that is supported, then do the rest with linq-to-objects.

Ocelot20
  • 10,510
  • 11
  • 55
  • 96
  • Hi, Thanks for you answer. I tried it according to your suggestions. Now its saying `Object reference not set to an instance of an object.` I am also filtering the result with an accountId now. With error, link pad is highlighting `suboc` as well. – Scorpion Oct 24 '13 at 08:42
  • The problem is that with an outer join, one side of the join has the potential to be null. So when you write `suboc in ps.DefaultIfEmpty()`, `suboc` has the potential to be null. When you use `suboc.new_SchoolRoleId.Id`, or `suboc.new_schoolMemberName`, `suboc` is null so you get an exception. You can provide a default value by doing something like `suboc == null ? string.empty : suboc.someProperty`. – Ocelot20 Oct 24 '13 at 12:53
  • See here for some clear outer join examples/links: http://stackoverflow.com/questions/3404975/left-outer-join-in-linq – Ocelot20 Oct 24 '13 at 12:55
  • Thats fine, I can check the null in select but Its failing because of the next join following the outer join. `join sr in new_schoolRoleSet` – Scorpion Oct 24 '13 at 13:00
  • Itf failing when I am trying to join `suboc` with `new_schoolRoleSet` – Scorpion Oct 24 '13 at 13:00
  • Try adding `where suboc != null` after `DefaultIfEmpty()`. – Ocelot20 Oct 24 '13 at 13:09
  • It returned the results now. But not exactly what I want because we are filtering it with `where suboc != null` – Scorpion Oct 24 '13 at 13:33
  • Think about that for a moment. If you want the results where `suboc == null`, then you don't want a join at all, or you want a join to a different result set. I believe I explained the reason for the original issue. If you have other questions about how joins work I suggest opening up a new question. – Ocelot20 Oct 24 '13 at 14:46