using System;

using System.Linq;

using System.Collections.Generic;

public class Program

{

    public static void Main()

    {

        var productSeriesRecords = GetProductSeriesRecords();

        var etaRecords = GetEtaRecords();

        // This is the query that I want to change so that we only get one EtaRecord, even if it references a product series

        // that exists more than once.

        var query = from etaRecord in etaRecords

                    join productSeriesRecord in productSeriesRecords

                    on etaRecord.ProductSeriesId equals productSeriesRecord.ProductSeriesId

                    into productSeriesGroup

                    from productSeries in productSeriesGroup.DefaultIfEmpty()

                    select new { EtaRecord = etaRecord, ProductSeriesRecord = productSeries };

​

​

        // The issue is that we'll see "Snuh 6" twice because that EtaRecord references a product series "002" that

        // exists twice.

        // What we want is only one EtaRecord with either product series record.

        // I guess one way to handle this would be to get the duplicates and then remove them in memory.

        foreach (var record in query)

        {

            string productSeriesDescription = record.ProductSeriesRecord == null ? "null" : record.ProductSeriesRecord.Description;

            Console.WriteLine(record.EtaRecord.Title + " - " + productSeriesDescription);

        }

    }

    private static List<ProductSeriesRecord> GetProductSeriesRecords()

    {

        var records = new List<ProductSeriesRecord>();

        records.Add(new ProductSeriesRecord() { Id = 425, ProductSeriesId = "001", Description = "Description A" });

        records.Add(new ProductSeriesRecord() { Id = 426, ProductSeriesId = "002", Description = "Description B" });

        records.Add(new ProductSeriesRecord() { Id = 427, ProductSeriesId = "002", Description = "Description B" });

        records.Add(new ProductSeriesRecord() { Id = 428, ProductSeriesId = "003", Description = "Description C" });

        records.Add(new ProductSeriesRecord() { Id = 429, ProductSeriesId = "004", Description = "Description D" });

        return records; 

    }

    private static List<EtaRecord> GetEtaRecords()

    {

        var records = new List<EtaRecord>();

        records.Add(new EtaRecord() { EtaId = 1, Title = "Snuh 1", ProductSeriesId = null });

        records.Add(new EtaRecord() { EtaId = 2, Title = "Snuh 2", ProductSeriesId = null });

        records.Add(new EtaRecord() { EtaId = 3, Title = "Snuh 3", ProductSeriesId = null });

        records.Add(new EtaRecord() { EtaId = 4, Title = "Snuh 4", ProductSeriesId = "001" });

        records.Add(new EtaRecord() { EtaId = 5, Title = "Snuh 5", ProductSeriesId = null });

        records.Add(new EtaRecord() { EtaId = 6, Title = "Snuh 6", ProductSeriesId = "002" });

        return records; 

    }

}

​

public class EtaRecord

{

    public int EtaId { get; set; }

    public string Title { get; set; }

    public string ProductSeriesId { get; set; }

}

​

public class ProductSeriesRecord

{

    public int Id { get; set; }

    public string ProductSeriesId { get; set; }

    public string Description { get; set; }

}