Matching two Lists of different types together

Question

I have two tables, each with their own model...

    class FamilyMan
    { 
        public int family_ID {get; set;}

        public string name {get; set;}
        public string fav_color {get; set;}
    }

    class BusinessMan
    {
        public int work_ID {get; set;}

        public string name {get; set;}
        public string fav_color {get; set;}

        //unrelated data etc
        public string job_title {get; set;}
    }

... and I want to be able to match up all FamilyMans to the matching BusinessMans based on name and fav_color.

I currently have something like:

    //fill lists from database
    var family_list = dbContext.FamilyMen.ToList();
    var busy_list = dbContext.BusinessMen.ToList();
    //create empty dict for matching the two types
    var matches = new Dict<FamilyMan, BusinessMan>();


    foreach (FamilyMan fam_man in family_list) {
        foreach (BusinessMan busy_man in busy_list) {
            //if both names and colors match, consider them a matching
            //object and add them each to the dict
            if (fam_man.name == busy_man.name &&
                    fam_man.color == busy_man.color) {
                matches_MtO[fam_man] = busy_man;
            }
        }
    }

but it takes quite a time to complete.

I've also looked at looping over one list with a foreach and then using LINQs FirstOrDefault to match them, but the efficiency seems about the same.

Is there a better way to go about matching FamilyMans and BusinessMans together?

Answer 1

Would a linq query like this be faster:

var matches = (
    from f in family_list
    join b in busy_list
    on f.color == b.color
    && f.name == b.name
    select new {f, b}
);

Answer 2

You should use LINQ's join syntax. This will enable the backend database to do the matching, and return only the result.

In order to enable a join on a composite key, follow the MSDN guidance here.

var query = from fm in dbContext.FamilyMen
            join bm in dbContext.BusinessMen on 
                new { bm.name, bm.color } equals new { fm.name, fm.color }
            select new {
               FamilyMan = fm,
               BusinessMan = bm
            };

var resultList = query.ToList();