C# Combining 2 lists of different types using Linq [Method syntax]

Question

I have 2 different lists say MarksAList and MarksBList. Following are the modal class for both

   Class MarksA
      Id int
      Name String  
      Mark1 int
      Mark2 int
      Mark3 int
   End Class

   Class MarksB
      Id int
      Name String
      Mark4 int
      Mark5 int
      Mark6 int
   End Class

MarksAList-> List<MarksA> MarksBList -> List<MarksB>

ID is the unique field in both the class

Sample:

MarksAList

Id      Name    Mark1    Mark2    Mark3
1       A        60       70       80
2       B        40       50       60
3       C        50       80       50
4       D        50       80       50

MarksBList

Id    Name        Mark4        Mark5        Mark6
3      C           60            70           60
4      D           78            55           88
5      E           60            70           60
6      F           78            55           88

Now I have another class. Lets name it MarksList which is List<Marks>. This needs to hold both the record from MarksA class and MarksB class.

The modal class for Marks

  Class Marks
      Id int
      Name String  
      Mark1 int
      Mark2 int
      Mark3 int
      Mark4 int
      Mark5 int
      Mark6 int
   End Class

How do I combine MarksAList and MarksB list so that the result looks like:

Id      Name    Mark1    Mark2    Mark3    Mark4     Mark5      Mark6
 1       A        60       70       80       0         0          0
 2       B        40       50       60       0         0          0
 3       C        50       80       50       60        70         60
 4       D        50       80       50       78        55         88
 5       E        0         0        0       60        70         60
 6       F        0         0        0       78        55         88

I cannot use join as MarksAList can contain records that are not there in MarksBList and viseversa.
How do I join this using method syntax?

Thanks in advance.

Answer 1

Are you looking for left join?

var first = from a in MarksAList
            from b in MarksBList.Where(x => x.Id == a.Id).DefaultIfEmpty()
            select new Marks
            {
                Id = ((int?)a.Id) ?? b.Id,
                Name = a.Name ?? b.Name,
                Mark1 = a?.Mark1 ?? 0,
                Mark2 = a?.Mark2 ?? 0,
                Mark3 = a?.Mark3 ?? 0,
                Mark4 = b?.Mark4 ?? 0,
                Mark5 = b?.Mark5 ?? 0,
                Mark6 = b?.Mark6 ?? 0
            };

var second = from b in MarksBList
             from a in MarksAList.Where(x => x.Id == b.Id).DefaultIfEmpty()
             select new Marks
             {
                 Id = ((int?)b.Id) ?? a.Id,
                 Name = b.Name ?? a.Name,
                 Mark1 = a?.Mark1 ?? 0,
                 Mark2 = a?.Mark2 ?? 0,
                 Mark3 = a?.Mark3 ?? 0,
                 Mark4 = b?.Mark4 ?? 0,
                 Mark5 = b?.Mark5 ?? 0,
                 Mark6 = b?.Mark6 ?? 0
             }; 

// it will act same as distinct and remove the duplicated rows
var final = first.Union(second).GroupBy(x => x.Id).Select(x => x.First());

Answer 2

I think the following code can help you

public class MarksA
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Mark1 { get; set; }
    public int Mark2 { get; set; }
    public int Mark3 { get; set; }
}

public class MarksB
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Mark4 { get; set; }
    public int Mark5 { get; set; }
    public int Mark6 { get; set; }
}

public class Marks
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Mark1 { get; set; }
    public int Mark2 { get; set; }
    public int Mark3 { get; set; }
    public int Mark4 { get; set; }
    public int Mark5 { get; set; }
    public int Mark6 { get; set; }
}

Main method

List<MarksA> marksAs = new List<MarksA>
        {

            new MarksA{ Id = 1 , Name = "A" , Mark1 = 60 ,  Mark2 = 70,Mark3 = 80},
            new MarksA{ Id = 2 , Name = "B" , Mark1 = 40 ,  Mark2 = 50,Mark3 = 60},
            new MarksA{ Id = 3 , Name = "C" , Mark1 = 50 ,  Mark2 = 80,Mark3 = 50},
            new MarksA{ Id = 4 , Name = "D" , Mark1 = 50 ,  Mark2 = 80,Mark3 = 50}
        };

        List<MarksB> marksBs = new List<MarksB>
        {

            new MarksB{ Id = 3 , Name = "C" , Mark4 = 60 ,  Mark5 = 70,Mark6 = 60},
            new MarksB{ Id = 4 , Name = "D" , Mark4 = 78 ,  Mark5 = 55,Mark6 = 88},
            new MarksB{ Id = 5 , Name = "E" , Mark4 = 60 ,  Mark5 = 70,Mark6 = 60},
            new MarksB{ Id = 6 , Name = "F" , Mark4 = 78 ,  Mark5 = 55,Mark6 = 88}
        };

var innerJoinMarks = marksAs.Join(marksBs,
                post => post.Id,
                meta => meta.Id,
                (post, meta) => new { Post = post, Meta = meta }).Select(x => new Marks
                {
                    Id = x.Post.Id,
                    Name = x.Post.Name,
                    Mark1 = x.Post.Mark1,
                    Mark2 = x.Post.Mark2,
                    Mark3 = x.Post.Mark3,
                    Mark4 = x.Meta.Mark4,
                    Mark5 = x.Meta.Mark5,
                    Mark6 = x.Meta.Mark6

                }).ToList();

        var fullOuterExcludingJoin = marksAs.Where(x => innerJoinMarks.All(z => z.Id != x.Id)).Select(x => new Marks
        {
            Id = x.Id,
            Name = x.Name,
            Mark1 = x.Mark1,
            Mark2 = x.Mark2,
            Mark3 = x.Mark3

        }).Union(marksBs.Where(x => innerJoinMarks.All(z => z.Id != x.Id)).Select(x => new Marks
        {
            Id = x.Id,
            Name = x.Name,
            Mark4 = x.Mark4,
            Mark5 = x.Mark5,
            Mark6 = x.Mark6

        })).ToList();


        var fullOuterJoin = innerJoinMarks.Union(fullOuterExcludingJoin ).OrderBy(x => x.Id);

Answer 3

Can you do this:

List<MarksA> listA = new List<MarksA>();

        listA.Add(new MarksA(1, "A", 60, 70, 80));
        listA.Add(new MarksA(2, "B", 40, 50, 60));
        listA.Add(new MarksA(3, "C", 50, 80, 50));

        List<MarksB> listB = new List<MarksB>();
        listB.Add(new MarksB(3, "C", 60, 70, 80));
        listB.Add(new MarksB(4, "D", 78, 80, 90));
        listB.Add(new MarksB(5, "E", 60, 70, 60));
        listB.Add(new MarksB(6, "F", 78, 55, 88));

        var concatList = listA.Join(listB, xA => xA.Id, xB => xB.Id, (xA, xB ) => new { xA.Id, xA.Name, xA.Mark1, xA.Mark2, xA.Mark3, xB.Mark4, xB.Mark5, xB.Mark6 });