/*

https://stackoverflow.com/questions/26405658/sql-server-linq-c-sharp-store-data-chain-information

*/

using System;

using System.Collections.Generic;

using System.Linq;  

​

public class Compatible

{

    public long Id;

    public long Prod1_id;

    public long Prod2_id;

}

​

public class Product

{

    public long Id;

    public string Name;

}

​

public class Program

{

    public static void Main()

    {

        //define 3 product

        var products = new List<Product>{

            new Product{Id=1,Name="A"},         

            new Product{Id=2,Name="B"},

            new Product{Id=3,Name="C"},

        };

        //define direct product connections

        // 2 is compatible with 1, and 3 is compatible with 1 (logicaly that also means that 3 is compatible with 2)

        var compatibilities = new List<Compatible>{         

            new Compatible{Id=1,Prod1_id=1,Prod2_id=2},

            new Compatible{Id=2,Prod1_id=1,Prod2_id=3},

            new Compatible{Id=3,Prod1_id=3,Prod2_id=1}, //test circe loop

            new Compatible{Id=4,Prod1_id=1,Prod2_id=1}, //test self loop

        };

        List<long> addedProducts = new List<long>();

        var p1_comps = GetAllCompatibles(compatibilities,1,1,addedProducts);

        addedProducts = new List<long>();

        var p2_comps = GetAllCompatibles(compatibilities,2,2,addedProducts);

        addedProducts = new List<long>();

        var p3_comps = GetAllCompatibles(compatibilities,3,3,addedProducts);

        p1_comps.Dump();

        p2_comps.Dump();

        p3_comps.Dump();

    }

    static List<long> GetCompatibles(IEnumerable<Compatible> table, long productId, long originalId, List<long> addedProducts)

    {

        return table.Where(c =>

                (c.Prod1_id == productId &&

                c.Prod2_id != originalId &&

                !addedProducts.Contains(c.Prod2_id))

            ).Select(c => c.Prod2_id).ToList();

    }

    static List<long> GetAllCompatibles(List<Compatible> table,long pid, long origid, List<long> addedProducts)

    {

        List<long> result = new List<long>();

        //Gets all the directly compatible products

        var Childs = GetCompatibles(table, pid, origid, addedProducts);

​

        //Gets all the directly compatible products by retrocompatibility

        Childs.AddRange(GetCompatibles(table.Select(t =>

                        new Compatible { Prod1_id = t.Prod2_id, Prod2_id = t.Prod1_id }

                    ).ToList(), pid, origid, addedProducts));

        result.AddRange(Childs);

        addedProducts.AddRange(Childs);

        //Iterates over the directly compatible products

        foreach (long child in Childs)

        {

            //Again, get all the directly compatible products of the "child"

            var temp = GetAllCompatibles(table, child, origid, addedProducts);

            result.AddRange(temp);

​

            //If there are childs compatible products, adds it to the final result

            if (temp.Count > 0)

            {

                result.AddRange(temp.Select(p =>

                        GetCompatibles(table, p, origid, addedProducts)

                    ).Aggregate((l1, l2) =>

                        { l1.AddRange(l2); return l1; })

                    );

            }

        }

        return result.Distinct().ToList();

    }

}