How to join two lists together using an id and map to an object that contains the properties from both lists in c#

I have two list and they have elements like this :

List<Model1> m1List= new List<Model1>();
List<Model2> m2List= new List<Model2>();
m1List[0].id= "1";
m1List[0].name = "a";
m1List[0].surName= "b";
m1List[1].id= "2";
m1List[1].name = "c";
m1List[1].surName= "d";
m1List[2].id= "5";
m1List[2].name = "e";
m1List[2].surName= "f";
*********
m2List[0].id= "1";
m2List[0].email= "aa@gmail.com";
m2List[0].phone= "555";
m2List[1].id= "2";
m2List[1].email= "bb@gmail.com";
m2List[1].phone= "44";
m2List[2].id= "3";
m2List[2].email= "cc@gmail.com";
m2List[2].phone= "77";

Then I want create a new list that contains records from both lists that are matched together with the same id value. The matched records should be combined into a new object that has the properties from both records.

List<Model1and2> m1Andm2List= new List<Model1and2>();
m1Andm2List[0].id= "1";
m1Andm2List[0].name = "a";
m1Andm2List[0].surName= "b";
m1Andm2List[0].email= "aa@gmail.com";
m1Andm2List[0].phone= "555";
m1Andm2List[1].id= "2";
m1Andm2List[1].name = "c";
m1Andm2List[1].surName= "d";
m1Andm2List[1].email= "bb@gmail.com";
m1Andm2List[1].phone= "44";

Then I want to append that list with all of the records that did not match records in the other list. Because there is no match the properties from the record that wasn't found should be left blank, it should be similar to this:

m1Andm2List[2].id= "3";
m1Andm2List[2].name = "";
m1Andm2List[2].surName= "";
m1Andm2List[2].email= "cc@gmail.com";
m1Andm2List[2].phone= "77";
m1Andm2List[3].id= "5";
m1Andm2List[3].name = "e";
m1Andm2List[3].surName= "f";
m1Andm2List[3].email= "";
m1Andm2List[3].phone= "";

Because m1List doesn't have a record with id == 3 and m2List doesn't have a record with id == 5.

How can I combine two lists like this in C# to produce the expected results?

What you are asking for is a Full Outer Join between these two sets.
Conceptually this is 3 lists that are concatenated together:

1. All the items that match in both m1List and m2List
2. All the items in m1List that do not exist in m2List
3. All the items in m2List that do not exist in m1List

Skipping all the first principals, we can use a LINQ Left Outer join to combine both the first and second lists into a single query, this is documented here: Perform left outer joins. The third query we can refer to as an Anti-Join because it needs to return only the items that do not match, we would concatenate the results from these two expressions together:

// Full Outer Join
var m1Andm2List = m1Andm2_LeftJoin_Query
.Concat(m1Andm2_RightAntiJoin_Query)
.OrderBy(x => x.id)
.ToList();

LEFT OUTER JOIN

Lets talk about the Left outer join first:

// LINQ Query - LEFT OUTER JOIN - All of m1, default values for m2
var m1Andm2_LeftJoin_Query = from m1 in m1List
join m2 in m2List on m1.id equals m2.id into merged
from m2 in merged.DefaultIfEmpty()
select new Model1and2
{
id = m1.id,
name = m1.name,
surName = m1.surName,
email = m2 != null ? m2.email : String.Empty,
phone = m2 != null ? m2.phone : String.Empty
};

This works because the special function .DefaultIfEmpty() will provide a null object into the results if no matching row from m2List can be found. The select statement is a projection that allows us to constrcut a new object using the results from the two tables at the same time. If you're not into LINQ, this is equivalent logic:

var m1Andm2_LeftJoin_List = new List<Model1and2>();
foreach(m1 in m1List)
{
var m2 = m2List.FirstOrDefault(x => x.id == m1.id);
var projection = new Model1and2 
{
id = m1.id,
name = m1.name,
surname = m1.surName,
email = String.Empty,
phone = String.Empty
};
if (m2 != null)
{
projection.email = m2.email;
projection.phone = projection.phone;
}
m1Andm2_LeftJoin_List.Add(projection);
}

The LINQ expression is just a different way to structure and the same intent, but it isolates any filtering and comparison logic while still combining everything into a single statement. Many people (especially those familiar with SQL style syntax) will understand the intent behind your logic with a LINQ expression quicker than the procedural steps. By combining everything into a single expression, future refactoring or maintenance of your code is more likely to keep it together and make less mistakes caused when parts of the logic are omitted.

• If you forgot to call the .Add() method to actually put the object on the list, the code would look right, but would not have any results. Over the years I have seen this too many times to count.

Anti Join

In LINQ this is less obvious, what we do is invert the lists, so start with m2List and LEFT OUTER JOIN into m1List, but then filter out any rows that did find a match in both lists. In LINQ we do this with a simple where expression:

// LINQ Query - RIGHT Anti Semi Join - All of m2 that don't match m1, default values for m1
var m1Andm2_RightAntiJoin_Query = from m2 in m2List
join m1 in m1List on m2.id equals m1.id into merged
from m1 in merged.DefaultIfEmpty()
where m1 == null
select new Model1and2
{
id = m2.id,
name = String.Empty,
surName = String.Empty,
email = m2.email,
phone = m2.phone
};

You could also have used a more simple to read non-correlation expression like this:

var m1Andm2_RightAntiJoin_Query = from m2 in m2List
where !m1List.Any(m1 => m1.id == m2.id)
select new Model1and2
{
id = m2.id,
name = String.Empty,
surName = String.Empty,
email = m2.email,
phone = m2.phone
};

or even this simple loop:

var m1Andm2_RightAntiJoin_List = new List<Model1and2>();
foreach(m2 in m2List)
{
var m1 = m1List.FirstOrDefault(x => x.id == m2.id);
if (m1 == null)
{
var projection = new Model1and2 
{
id = m2.id,
name = String.Empty,
surname = String.Empty,
email = m2.email,
phone = m2.phone
};
m1Andm2_RightAntiJoin_List.Add(projection);
}
}

The JOIN syntax for this scenario might not make the query always easier to read, but we use it anyway because it allows us to identify and reuse common code expressions between similar logic patterns. We also expect as developers that the framework would have an implementation for managing the lookup and match logic that is optimised by default by people who are far smarter than us.

The final output, after choosing your syntax options to implement, should look like this: https://dotnetfiddle.net/Xw7mvc