Compare / Join two object collections LINQ

Question

I have two csv files that I need to compare and merge together. There is a 'big' list with many records and columns of data and a 'small' list of the desired records and some additional data. The ultimate goal is to output the records on the small list joined with the data in the big list.

The below code seems to make the correct selects, but I am unsure if I should be looking at using JOIN instead?

I like the cleanness of this solution in how it handles the CSV files in such few lines of code - let ... some.Split(',') - and haven't come up with as clean of a way in using JOIN.

string[] small = System.IO.File.ReadAllLines(@"I:\\" + Environment.UserName + "\\smallfile.csv");

string[] big = System.IO.File.ReadAllLines(@"J:\\DROPFOLDERS\\bigfile.csv");

IEnumerable<MBSGroup> queryMBSObjects =
from smallLine in small
let splitSmallLine = smallLine.Split(',')
from bigLine in big
let splitBigLine = bigLine.Split(',')
where ((splitSmallLine[0] == splitBigLine[0] && splitSmallLine[2] == splitBigLine[1] && splitSmallLine[0] != "" && splitSmallLine[2] != "" && splitBigLine[1] != "") ||
       (splitSmallLine[0] == splitBigLine[0] && splitSmallLine[2] == splitBigLine[5] && splitSmallLine[0] != "" && splitSmallLine[2] != "" && splitBigLine[5] != ""))
select new MBSGroup()
{
    write data to merge object
};
List<MBSGroup> MBSlists = queryMBSObjects.ToList();

Answer 1

This code seems to suffer from poor readability and maintainability. What does splitSmallLine[2] mean, and why do I care if it matches splitBigLine[1] (ponders the next developer to run into this code)? It's very easy to make mistakes in this code, so I think bringing some clarity into what you are actually selecting/comparing against will help.

Since I don't actually know what your files look like, or what the fields represent, let's just start here:

string[] small = new string[] {
    "Joe,Smith,22",
    "Sally,Jones,32"
};

string[] big = new string[] {
    "Smith,22,Joe",
    "Jones,34,Sally",
    "Doe,15,Jane"
};

We have two "files" that store people, but the fields aren't in the same place. The first thing you could do is bring meaning to your parsed rows, like so:

var smallFilePeople = from personLine in small
                      let personFields = personLine.Split(',')
                      select new
                      {
                          FirstName = personFields[0],
                          LastName = personFields[1],
                          Age = personFields[2]
                      };

var bigFilePeople = from personLine in big
                    let personFields = personLine.Split(',')
                    select new
                    {
                        FirstName = personFields[2],
                        LastName = personFields[0],
                        Age = personFields[1]
                    };

Now I know that I'm selecting people, and I have a single place to show what I'm actually pulling out of each indexed location. This makes writing a join query based on this very simple and easy to understand:

var joined = from smallFilePerson in smallFilePeople
             join bigFilePerson in bigFilePeople
                on new { smallFilePerson.FirstName, smallFilePerson.LastName } equals
                   new { bigFilePerson.FirstName, bigFilePerson.LastName }
             select new
             {
                 FirstName = smallFilePerson.FirstName,
                 LastName = smallFilePerson.LastName,
                 AgeInSmallFile = smallFilePerson.Age,
                 AgeInBigFile = bigFilePerson.Age
             };

This example is a bit more contrived than yours, but I'm sure you can see where this may help. I would also take this a step further and split out the query/parsing logic/joining into distinct functions. It's a good separation of concerns, and you may end up reusing the parsing for each file in other new ways.