Excel Data using LinQ Intersecting

Question

My intersect in LINQ somehow dont seem to work. I have two excel sheets. I fetch it using LinQToExcel and (LinQToExcel does not support VisitSubQueryExpression i have to do some extra work).

  List<BoardSheet> sourceTest = (from t in Boards[0]
                        where t["Board"] == boardName
                        select new CircuitSet
                             {
                                 ID = string.Format(t["ID"]),
                                 Data = string.Format(t["Data"]),
                                 CtrlType = string.Format(t["CtrlType"]),
                                 sys = string.Format(t["sys"]),
                                 code = string.Format(t["code"])
                             }
                        ).ToList<BoardSheet>();

           List<BoardSheet> targetTest = (from t in Boards[0]
                                          where t["Board"] == boardName
                                          select new CircuitSet
                                          {
                                              ID = string.Format(t["ID"]),
                                              Data = string.Format(t["Data"]),
                                              CtrlType = string.Format(t["CtrlType"]),
                                              sys = string.Format(t["sys"]),
                                              code = string.Format(t["code"])
                                          }
                       ).ToList<BoardSheet>();

           IEnumerable<BoardSheet> board = sourceTest.Intersect(targetTest);

board's count always returns 0. But when i iterate thro the field values of sourceTest and targetSet i see common field values.

Answer 1

I think an easy way would be to implement IEqualityComparer<CircuitSet>. If CircuitSet's key is ID, then you could do it like this:

public class CircuitSetComparer : IEqualityComparer<CircuitSet>
{

    #region IEqualityComparer<CircuitSet> Members

    public bool Equals(CircuitSet x, CircuitSet y)
    {
        return x.ID == y.ID;
    }

    public int GetHashCode(CircuitSet obj)
    {
        return obj.ID;            
    }

    #endregion
}

Then in your code:

IEnumerable<BoardSheet> board = sourceTest.Intersect(targetTest, new CircuitSetComparer());

GetHashCode method is tricky though, but it should be alright if my assumptions (ID being the key) are correct.

Answer 2

These are instances of reference types. Intersect is using the DefaultComparer for reference types, which is ReferenceEquals. Since sourceTest has no common instances with targetTest, no results are found.

You could create a Comparer, or you could join like this:

List<CircuitSet> results =
(
  from s in sourceTest
  join t in targetTest
  on s.Id equals t.Id
  where s.Data == t.Data && s.ControlType == t.ControlType ...
  select s
).ToList();

Answer 3

but i changed the query based on what david suggested

List<BoardSheet> sourceTest =(from s in (from t in Boards[0]
                                         where t["Board"] == boardName
                                         select new CircuitSet
                                         {
                                             ID = string.Format(t["ID"]),
                                             Data = string.Format(t["Data"]),
                                             CtrlType = string.Format(t["CtrlType"]),
                                             sys = string.Format(t["sys"]),
                                             code = string.Format(t["code"])
                                         }
                       ).ToList<BoardSheet>() jon tbl in (from t in Boards[0]
                                         where t["Board"] == boardName
                                         select new CircuitSet
                                         {
                                             ID = string.Format(t["ID"]),
                                             Data = string.Format(t["Data"]),
                                             CtrlType = string.Format(t["CtrlType"]),
                                             sys = string.Format(t["sys"]),
                                             code = string.Format(t["code"])
                                         }
                      ).ToList<BoardSheet>() on s.ID equals tbl.ID select s).ToList<BoardSheet>() ;