CODEWITHSUNDEEP
c#sqllinq
navig8tr
navig8tr

Reputation: 1844

How to query two tables for matching values with linq and C#?

I'm pretty new to C# and Linq and I'm having some trouble querying two tables for matching values.

Where I work as an intern, we use a Unit of Work Repository Pattern. To query an sql table, for example, I would use this linq expression: _entities.Atable.Where(x => x.Col1 == col1 && x.Col2 == col2). The _entities variable represents the interface of all the tables in the database model.

Suppose I have two tables Table1 and Table2. Here are the columns of each table:

Table1: Col1, Col2, colA, ColB
Table2: Col1, col2, ColC, ColD

Here's what I would like to do:

In the end, I'd like to have a list of Table2 items that match Table1 by Col1 and Col2. And a list of the leftover Table1 items for which there was no match in Table2.

Mostly, I'm stuck on the third bullet point, but I'm open to any suggestions and improvements.

Lastly, and I don't know if this is possible, I'd like to combine the two lists into one. Since they're different types I'm not sure how this would work.

I hope all this makes sense. I've been trying to wrap my head around it for a while, but I'm still stumped on how to create the queries. I also played around with Except() and Intersect() but didn't have much luck.

Any help would be greatly appreciated.

Upvotes: 1

Views: 2153

Answers (1)

David Ewen
David Ewen

Reputation: 3732

I usually find Any to be the clearest method for matching multiple values from different types.

class Type1
{
    public int Key1 { get; set; }
    public int Key2 { get; set; }
    public string Type1Prop { get; set; }
    public Type1(int key1, int key2, string prop)
    {
        Key1 = key1;
        Key2 = key2;
        Type1Prop = prop;
    }
}

class Type2
{
    public int Key1 { get; set; }
    public int Key2 { get; set; }
    public string Type2Prop { get; set; }
    public Type2(int key1, int key2, string prop)
    {
        Key1 = key1;
        Key2 = key2;
        Type2Prop = prop;
    }
}

public void Main()
{   
    var list1 = new List<Type1>
    {
        new Type1(1,1,"Value"), new Type1(1,2,"Value"), new Type1(2,1,"Value"), new Type1(2,2,"Value")
    };
    var list2 = new List<Type2>
    {
        new Type2(1,1,"Value"), new Type2(2,1,"Value"), new Type2(3,1,"Value")
    };
    var in1ButNot2 = list1.Where(item => !list2.Any(item2 => item2.Key1 == item.Key1 && item2.Key2 == item.Key2)).ToList();
    var in2ButNot1 = list2.Where(item => !list1.Any(item2 => item2.Key1 == item.Key1 && item2.Key2 == item.Key2)).ToList();
    var in1And2 = list2.Where(item => list1.Any(item2 => item2.Key1 == item.Key1 && item2.Key2 == item.Key2)).ToList();
    in1ButNot2.ForEach(item => Console.WriteLine("in1ButNot2 - Key1={0},Key2={1}", item.Key1, item.Key2));
    in2ButNot1.ForEach(item => Console.WriteLine("in2ButNot1 - Key1={0},Key2={1}", item.Key1, item.Key2));
    in1And2.ForEach(item => Console.WriteLine("in1And2 - Key1={0},Key2={1}", item.Key1, item.Key2));
}

This ends outputting the below and shows you all the ways you can intersect the data. Combining the lists is something you will have to figure out yourself if you just want the keys then you can create a common type that you convert into to create a combined list.

in1ButNot2 - Key1=1,Key2=2
in1ButNot2 - Key1=2,Key2=2
in2ButNot1 - Key1=3,Key2=1
in1And2 - Key1=1,Key2=1
in1And2 - Key1=2,Key2=1

Upvotes: 2

Related Questions