LINQ query compary multiple with multiple

Question

UPDATED: Apologies my colleague has updated the model a little

UPDATED: Given context to this idea perhaps itll help

I have a list of ShowData for a Show. Each ShowData contains a number of Prints. So

1 Show -> Many ShowData -> Many Prints

I have two datasets both of the same type of object - ShowData

A Fingerprint looks like the following:

public class ShowData
{
    public ShowData() { }        
    public int time { get; set; }
    public List<Prints> prints { get; set; }
}
public class Prints
{
    public Prints() { }        
    public int value { get; set; }
    public string range { get; set; }
}

I get all the ShowData for a particular show:

var ShowData1 = (from showData1 in context.ShowDatas
                                where (showData1.Show.Id == 1)                                    
                                select new
                                {
                                    showData = showData1,
                                    prints = showData1.Prints
                                });                  

So an example is:

DATASET A

time      prints
1         {1,low},{4,low},{8,low},{9,low},{10,low},{11,high},{15,high},{16,high},{18,high}
2         {4,low},{7,low},{8,low},{9,low},{10,low},{12,high},{15,high},{16,high},{19,high}
3         {1,low},{2,low},{3,low},{8,low},{9,low},{11,high},{12,high},{15,high},{16,high}
4         {1,low},{7,low},{8,low},{9,low},{10,low},{11,high},{12,high},{14,high},{15,high}
5         {1,low},{5,low},{6,low},{8,low},{9,low},{11,high},{14,high},{17,high},{19,high}

DATASET B

time      prints
1         {1,low},{2,low},{3,low},{4,low},{5,low},{11,high},{12,high},{13,high},{18,high}
2         {0,low},{3,low},{5,low},{6,low},{7,low},{11,high},{13,high},{19,high},{20,high}

The first dataset (DATASET A) is roughly 4000 ShowData items long. I have another dataset of the ShowData, which is about 120 items long (DATASET B).

Im trying to find a way to compare the two lists to bring out all the time points where the prints in DATASET B have at least 2 matches to a print in DATASET A. However there need to be at least 2 matches for low and 2 matches for high

So my returned query might look like the following:

TimeInDataSetB         TimesInDataSetAForLows      TimeInDataSetAForHighs
1                              1,3,5                       3,4
2                                                           5

So above, the print (with range=low) at time 1 in DatasetB had at least 2 matches with prints located at times 1,3,5 in DatasetA AND the print (with range=high) at time 1 in DatasetB had at least 2 matches with prints located at times 3,4 in DatasetA.

Item at time 2 in DataSetB has no matches for any in DataSet for the lows and only 1 match for the highs

Can anyone help with that? (im looking for answer in c#)

Using the method described in the first answer i tried the following:

var query3 = from a in recordingPoints
                             from b1 in ShowData1
                             let timeIntersects = a.Prints.Intersect(b1.prints, printsEqualityComparer)
                             where timeIntersects.GroupBy(x => x.Range)
                                                 .All(x => x.Count() > 2)
                             group b1 by a.Time into grouped
                             select new
                             {

                                 TimeInDataSetA = grouped.Key,
                                 TimeInDataSetB = grouped.ToArray()
                             };

where recordingPoints is a list of ShowData

DATASET TO TEST WITH

List<ShowData> bigdataset = new List<Ent.ShowData>();
                List<ShowData> smalldataset = new List<Ent.ShowData>();

                List<int> ints = new List<int>(new int[]{1, 4, 8, 9, 10, 11, 15, 16, 18});
                ShowData od = new Ent.ShowData();
                od.Show.Id = 7;
                foreach (int it in ints)
                {
                    Prints pr = new Prints();
                    if (it < 11)
                        pr.Range = "low";
                    else
                        pr.Range = "high";
                    pr.Value = it.ToString();
                    od.Prints.Add(pr);
                }                    
                od.Time = 1;
                bigdataset.Add(od);

                ints = new List<int>(new int[] { 4, 7, 8, 9, 10, 12, 15, 16, 19 });
                od = new Ent.ShowData();
                od.Show.Id = 7;
                foreach (int it in ints)
                {
                    Prints pr = new Prints();
                    if (it < 11)
                        pr.Range = "low";
                    else
                        pr.Range = "high";
                    pr.Value = it.ToString();
                    od.Prints.Add(pr);
                }
                od.Time = 2;
                bigdataset.Add(od);

                ints = new List<int>(new int[] { 1, 2, 3, 8, 9, 11, 12, 15, 16 });
                od = new Ent.ShowData();
                od.Show.Id = 7;
                foreach (int it in ints)
                {
                    Prints pr = new Prints();
                    if (it < 11)
                        pr.Range = "low";
                    else
                        pr.Range = "high";
                    pr.Value = it.ToString();
                    od.Prints.Add(pr);
                }
                od.Time = 3;
                bigdataset.Add(od);

                ints = new List<int>(new int[] { 1, 7, 8, 9, 10, 11, 12, 14, 15 });
                od = new Ent.ShowData();
                od.Show.Id = 7;
                foreach (int it in ints)
                {
                    Prints pr = new Prints();
                    if (it < 11)
                        pr.Range = "low";
                    else
                        pr.Range = "high";
                    pr.Value = it.ToString();
                    od.Prints.Add(pr);
                }
                od.Time = 4;
                bigdataset.Add(od);

                ints = new List<int>(new int[] { 1, 5, 6, 8, 9, 11, 14, 17, 19 });
                od = new Ent.ShowData();
                od.Show.Id = 7;
                foreach (int it in ints)
                {
                    Prints pr = new Prints();
                    if (it < 11)
                        pr.Range = "low";
                    else
                        pr.Range = "high";
                    pr.Value = it.ToString();
                    od.Prints.Add(pr);
                }
                od.Time = 5;
                bigdataset.Add(od);

                ints = new List<int>(new int[] { 1, 2, 3, 4, 5, 11, 12, 13, 18 });
                od = new Ent.ShowData();
                foreach (int it in ints)
                {
                    Prints pr = new Prints();
                    if (it < 11)
                        pr.Range = "low";
                    else
                        pr.Range = "high";
                    pr.Value = it.ToString();
                    od.Prints.Add(pr);
                }
                od.Time = 1;
                smalldataset.Add(od);

                ints = new List<int>(new int[] { 0, 3, 5, 6, 7, 11, 13, 19, 20 });
                od = new Ent.ShowData();
                foreach (int it in ints)
                {
                    Prints pr = new Prints();
                    if (it < 11)
                        pr.Range = "low";
                    else
                        pr.Range = "high";
                    pr.Value = it.ToString();
                    od.Prints.Add(pr);
                }
                od.Time = 2;
                smalldataset.Add(od);

var printsEqualityComparer = new PrintsEqualityComparer();

                    var query4 = from a in smalldataset
                                 from b1 in bigdataset
                                 let timeIntersects = a.Prints.Intersect(b1.Prints, printsEqualityComparer)
                                 where timeIntersects.GroupBy(x => x.Range)
                                                     .All(x => x.Count() > 1)
                                 group b1 by a.Time into grouped
                                 select new
                                 {
                                     TimeInDataSetA = grouped.Key,
                                     TimeInDataSetB = grouped.ToArray()
                                 };

Answer 1

You can perform an intersect for each item in B over each item in A filtering on a minimum match of 3 and grouping by the Time set in A like:

var query = from a in listA
            from b in listB
            where a.prints.Intersect(b.prints).Count() >= 3
            group b by a.time into grouped
            select new
            {
                TimeInDataSetA = grouped.Key,
                TimeInDataSetB = grouped.ToArray()
            };

Edit, based on your new request, you can provide a equalityComparer to the intersect method to determine equality for 2 instances of Prints. Note that in the example below i provided a very primitive implementation. Please read the link provided.

// please see: http://blogs.msdn.com/b/ericlippert/archive/2011/02/28/guidelines-and-rules-for-gethashcode.aspx
class PrintsEqualityComparer : IEqualityComparer<Prints>
{
    public bool Equals(Prints x, Prints y)
    {
        return object.Equals(x, y) && object.Equals(x.value, y.value);
    }

    public int GetHashCode(Prints obj)
    {
        return obj.range.GetHashCode() ^ obj.value.GetHashCode();
    }
}
var printsEqualityComparer = new PrintsEqualityComparer();

var query = from a in listA
        from b in listB
        let timeIntersects = a.prints.Intersect(b.prints, printsEqualityComparer)
        where timeIntersects.GroupBy(x => x.range)
                            .All(x => x.Count() > 2)
        group b by a.time into grouped
        select new
        {
            TimeInDataSetA = grouped.Key,
            TimeInDataSetB = grouped.ToArray()
        };