Can't enumerate LinQ results with left join

Question

var itemSet = from item in da.GetList<Models.account>()
                           join file in objFileStorageList
                           on item.account_id equals file.parent_id into objFile
                           from fileItem in objFile.DefaultIfEmpty()
                           where item.company != null && item.company.company_id == 123
                           orderby item.updatedDate descending
                           select
                           new
                           {
                               Id = item.account_id,
                               RefNo = item.refNo,
                               StartDate = item.StartDate ,
                               EndDate = item.EndDate ,
                               Comment = item.comment,
                               FileStorageID = fileItem != null ? fileItem.fileStorage_id : -1,
                               Identification = fileItem != null ? fileItem.identifier : null,
                               fileName = fileItem != null ? fileItem.file_nm : null
                           };

It raises error message when I try to enumerate through collection result from Linq query above.

LINQ to Entities does not recognize the method 'System.Collections.Generic.IEnumerable1[SCEFramework.Models.fileStorage] DefaultIfEmpty[fileStorage](System.Collections.Generic.IEnumerable1[SCEFramework.Models.fileStorage])' method, and this method cannot be translated into a store expression

foreach (var item in itemSet)
        {
            string itemRef=  item.RefNo;    
        }

Please suggest me any solutions. Thanks in advance.

Answer 1

I think the problem with this is the following 'from' clause:

from fileItem in objFile.DefaultIfEmpty()

Note that your LINQ query may only be executed at the time the result collection is executed. So while you think your exception is in the foreach, it's actually within your expression. This breaks because the possible null value of objFile.DefaultIfEmpty() cannot cast into an IEnumerable.

Try removing the DefaultIfEmpty call and see what happens.