LINQ Left Join with EndsWith instead of Equals

Question

I am trying to do a LINQ query to entities using a left join that is not based on equality but instead using EndsWith. It is not working as intended and I am wondering if I am doing the comparison incorrectly. When I do the comparison as an equality it returns results, but just not the results I need since I need to see the results using EndsWith due to the property values.

Based on the code I have below, I should get a resulting object that looks like this:

   X              Y
 BBFile1        File1
 XDDFile2       <Empty>
 File1TTFile3   File3

Instead I just get an error stating that "Object reference not set to an instance of an object". Which I have searched and what I found mainly pointed to the fact that I would need to set default values in the 'Select' statement to indicated what to do when NULL results are returned, but I am already doing that and it doesn't seem to work.

Here is the code I have:

List<SFTPFilesListItem> SFTPFullNameList = new List<SFTPFilesListItem>();
        List<ArchiveFileListItem> ArchiveFilesList = new List<ArchiveFileListItem>();

        SFTPFilesListItem file = new SFTPFilesListItem();
        file.FullName = "BBFile1";
        SFTPFullNameList.Add(file);

        SFTPFilesListItem file2 = new SFTPFilesListItem();
        file2.FullName = "XDDFile2";
        SFTPFullNameList.Add(file2);

        SFTPFilesListItem file3 = new SFTPFilesListItem();
        file3.FullName = "File1TTFile3";
        SFTPFullNameList.Add(file3);

        ArchiveFileListItem afile = new ArchiveFileListItem();
        afile.ArchiveFileName = "File3";
        ArchiveFilesList.Add(afile);

        ArchiveFileListItem afile2 = new ArchiveFileListItem();
        afile2.ArchiveFileName = "File1";
        ArchiveFilesList.Add(afile2);

        ArchiveFileListItem afile3 = new ArchiveFileListItem();
        afile3.ArchiveFileName = "File4";
        ArchiveFilesList.Add(afile3);

        var oldfiles = from sftpfile in SFTPFullNameList
                       from archivefile in ArchiveFilesList
                       .Where(x => sftpfile.FullName.EndsWith(x.ArchiveFileName))
                       .DefaultIfEmpty()
                       select new
                       {
                           x = sftpfile.FullName == null ? string.Empty : sftpfile.FullName,
                           y = archivefile.ArchiveFileName == null ? string.Empty : archivefile.ArchiveFileName
                       };

    }

    public class SFTPFilesListItem
    {
        public string FullName { get; set; }
    }

    public class ArchiveFileListItem
    {
        public string ArchiveFileName { get; set; }
    }

Did you look at the generated sql? This will produce a cross-join, not a left-join. — David L, Jul 07 '16 at 01:26

score 0 · Answer 1 · edited Jul 07 '16 at 06:47

0

You don't "join" on anything other than an equality: you generate an outer join, then select the rows you want using a WHERE condition (it's funny how WHERE selects, whereas SELECT projects, but that's how it is).

edited Jul 07 '16 at 06:47

Flexo

87,323
22
191
272

answered Jul 07 '16 at 05:16

Bruce David Wilner

463
2
4

Bruce, Can you provide an example of how to accomplish the left join using the data that I provided? – silverbullettruck2001 Jul 07 '16 at 11:03

LINQ Left Join with EndsWith instead of Equals

1 Answers1