How to make this LINQ To entity method work when it has Nullable LEFT JOIN

Question

Here is the code snippet, actually the whole method. This method works f,ine when NULLAblE Foreign Key Refernces has value. When the value is not there, then this method does not work. My idea is to get all the records even if the references column is NULL. Here is the code :

 public List<PostedJob> GetPostedJobs(int startingIndex, int maximumRows)
    {
        using (var records = new CommonEvent())
        {
            var resultSet = 
                from r in records.ProjectPosts
                join rr in records.Categories on r.Category_FK equals rr.ID
                join al in records.ApplyForLimits on r.ApplyForLimit_FK 
                                         equals al.Id
                //from uImage in 
                // records.UploadedFiles
                //  .Where(uu=>uu.Id == r.UploadedFileInfo_FK 
               //       || r.UploadedFileInfo_FK == null).DefaultIfEmpty()
                join a in records.UploadedFiles on r.UploadedFileInfo_FK 
                                         equals a.Id into something
                from uImage in something.DefaultIfEmpty()
                orderby r.PostId
                select new Models.PostedJob
                {
                    ApplyForLimitName = al.Name,
                    ProjectTitle = r.ProjectTitle,
                    ProjectDescription = r.ProjectDescription,
                    ProjectSummaryDescription = r.ProjectSummaryDescription,
                    SkillsRequirements = r.SkillsRequirements,
                    CategoryName = rr.CategoryName,
                     UploadedFileID = (int) r.UploadedFileInfo_FK,

                        UploadedFileInformation = uImage == null ?
                            new Models.UploadedFile
                            {
                                fileContents = new  byte [] { (byte) 0},
                                FileExtension = string.Empty,
                                FileName = string.Empty,
                                FileSize = 0,
                                UploadedDate = DateTime.Now
                            } 
                        :
                            new Models.UploadedFile
                            {
                                fileContents = uImage.FileContents,
                                FileExtension = uImage.FileExtension,
                                FileName = uImage.FileName,
                                FileSize = uImage.FileSize,
                                UploadedDate = DateTime.Now
                            }
                };



            return resultSet.Skip(startingIndex).Take(maximumRows).ToList();


        }

Thank you for any suggestions or ideas on how to proceed . I am using .NET 4.0

Answer 1

Can you not use the Associations generated for you?

var a = records
.ProjectPosts
.Select(
  projectPost =>
   new Models.PostedJob()
   {
     ProjectTitle = projectPost.ProjectTitle,
     CategoryName = projectPost.Category.CategoryName,
   });

Something along those lines?

EDIT: And just add Null checks when the FK may fail

example:

CategoryName = projectPost.Category == null ? String.Empty : projectPost.Category.CategoryName,