CODEWITHSUNDEEP
c#sqllinqentity-frameworklinq-to-entities
John Chapman
John Chapman

Reputation: 898

DefaultIfEmpty() Causing "System.NotSupportedException: LINQ to Entities does not recognize the method 'System.Collections.Generic.IEnumerable'"

Scenario: I have a table of User Profiles and a table of Colleagues. The colleagues table has a record for ever other user that a User Profile is following. In this query, I am grabbing all of the people who are following the current logged in user (getting their Record IDs from the Colleagues table and joining the User Profile table to get their details). Then, I am joining the Colleagues table again to see if the logged in user is following that user back.

Issue: It is my understanding that the best way to do a LEFT JOIN (in looking for the colleague record where the user is following the colleague back) is to add "DefaultIfEmpty()" to that join. When I add .DefaultIFEmpty() to that join, I get the following error message:

System.NotSupportedException: LINQ to Entities does not recognize the method 'System.Collections.Generic.IEnumerable'

If I remove the ".DefaultIFEmpty()" from that join, it works. However, it runs it as a regular JOIN, leaving out records where the user is not following the colleague back.

Code: Here is the code I am using:

var results = (from a1 in db.Colleague
join b1 in db.UserProfile on new {ColleagueId = a1.ColleagueId} equals
  new {ColleagueId = b1.RecordId}
join d1 in db.UserProfile on new {RecordId = a1.OwnerId} equals
  new {RecordId = d1.RecordId}
join c1 in db.Colleague
  on new {OwnerId = b1.RecordId, Ignored = false, ColleagueId = a1.OwnerId}
  equals new {c1.OwnerId, c1.Ignored, c1.ColleagueId} into c1Join
from c1 in c1Join.DefaultIfEmpty()  // This is the .DefaultIfEmpty() breaking the query
where
  b1.AccountName == userName &&
  a1.Ignored == false
orderby
  b1.LastName
select new
{
    RecordId = (System.Int64?) d1.RecordId,
    d1.AccountName,
    d1.PreferredName,
    d1.FirstName,
    d1.LastName,
    d1.PictureUrl,
    d1.PublicUrl,
    IsFollowing = c1.OwnerId < 1 ? 0 : 1
});
foreach (var result in results)  // This is what throws the error
{
    // Do stuff
}

Any ideas?

Upvotes: 2

Views: 4415

Answers (3)

Dubs
Dubs

Reputation: 124

It looks like you need to create a default value to pass into DefaultIfEmpty(), since DefaultIsEmpty() takes a parameter.

DefaultIfEmpty() on MSDN

Upvotes: 2

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364279

DefaultIfEmpty is supported only in EFv4+. First version of EF doesn't support DefaultInEmpty.

Upvotes: 2

regex
regex

Reputation: 3601

The SQL Provider for Entity Framework in version 3.5 of the .NET framework does not support DefaultIfEmpty(). Sorry, but could not find a better reference than this article: http://smehrozalam.wordpress.com/2009/06/10/c-left-outer-joins-with-linq/

You might want to try straight LINQ-to-SQL rather than ADO.NET Entity Framework. I believe that it works in LINQ-to-SQL. I've verified in the past that left joins work in 3.5 via LinqPad.

Upvotes: 2

Related Questions