CODEWITHSUNDEEP
c#sql-serverlinqjoin
razorsyntax
razorsyntax

Reputation: 359

Linq with Multiple Joins

I'm attempting to translate the following SQL statement to Linq and am having trouble with the multiple joins- I seem to be missing something.

            SELECT DISTINCT
                  Test1 = Table1.Column1,
                  Test2 = 1,
                  Test3 = Table1.Column2,
                  Test4 = Table1.Column5,
                  Test5 = Table1.Column6
            FROM Table1
            LEFT JOIN Table2 ON Table1.Column1 = Table2.Column1
            INNER JOIN Table3 ON Table1.Column3 = Table3.Column3 
            WHERE Table3.Column4 IN (1,2,6)

Here's the Linq so far:

var TestQuery = Table1_Collection.Select(x => new
            {
                Test1 = Table1.Column1,
                Test2 = 1,
                Test3 = Table1.Column2,
                Test4 = Table1.Column5,
                Test5 = Table1.Column6
            })
            [joins go here]
            .Where("where stuff goes here");

Any ideas? I'm not so much seeking assistance with the .Where as I am the joins. I'm not sure about the formatting with the method syntax.

Upvotes: 2

Views: 1760

Answers (3)

bigtlb
bigtlb

Reputation: 1572

Here is an example of your original SQL statement in LINQ query syntax.

List<int> vals = new List<int> {1,2,6};

var qry = from rec1 in Table1
          join rec2 in Table2 on rec1.Column1 equals rec2.Column2 into ljT2
          from rec2 in ljT2.DefaultIfEmpty()  //Handle left join
          join rec3 in Table3 on rec1.Column1 equals rec3.Column3
          where vals.Contains(rec3.Column4)
          select new {
                Test1 = rec1.Column1,
                Test2 = 1,
                Test3 = rec2 == null?null:rec2.Column2, //Must allow for rec2 to be null
                Test4 = rec3.Column5,
                Test5 = rec3.Column6
            }

qry = qry.Distinct();

Upvotes: 1

Jose M.
Jose M.

Reputation: 1316

try this example, I hope you help

class Table1
{
    public int Id1 { get; set; }
    public string Column1 { get; set; }
}

class Table2
{
    public int Id2 { get; set; }
    public string Column2 { get; set; }
}

class Table3
{
    public int Id3 { get; set; }
    public string Column3 { get; set; }
}


    static void Main(string[] args)
    {
        var table1 = new List<Table1>();
        var table2 = new List<Table2>();
        var table3 = new List<Table3>();
        for (int i = 0; i < 10; i++)
        {
            table1.Add(new Table1 { Id1 = i, Column1 = "column1_table1_" + i });
            table2.Add(new Table2 { Id2 = i, Column2 = "column2_table2_" + i });
            table3.Add(new Table3 { Id3 = i, Column3 = "column3_table3_" + i });
        }

        var table1JoinTable2 = table1.Join(table2, t1 => t1.Id1, t2 => t2.Id2, (t1, t2) => new { Id = t1.Id1, Column1 = t1.Column1, Column2 = t2.Column2 } );
        var table1JoinTable2JoinTable3 = table1JoinTable2.Join(table3, t12 => t12.Id, t3 => t3.Id3, (t12, t3) => new { Id = t12.Id, Column1 = t12.Column1, Column2 = t12.Column2, Column3 = t3.Column3 });

        var result1 = table1JoinTable2JoinTable3.Single(t123 => t123.Id == 1);
        Console.WriteLine("Id={0} C1={1} C2={2} C3={3}", result1.Id, result1.Column1, result1.Column2, result1.Column3);
        // prints "Id=1 C1=column_table1_1 C2=column_table2_1 C3=column_table3_1"
    }

Upvotes: 1

Jason White
Jason White

Reputation: 218

Here you go:

var results = Table3_Collection
    .Where(i => column4s.Contains(i.Column4))
    .Join(Table1_Collection, i => i.Column3, i => i.Column3, (i, j) => j)
    .Join(Table2_Collection, i => i.Column1, i => i.Column1, (i, j) => i)
    .Distinct(comparer);

In your original SQL query you weren't using selecting any columns from Table2, so you could omit that join. I included it above, but please feel free to remove it.

Also, your C# example didn't have Distinct, but I included it for you as it was in your original SQL query, and is most likely your intent. And, please, don't forget to implement your own IEqualityComparer. Here is an example of one:

class Table1Comparer : IEqualityComparer<Table1>
{
    public bool Equals(Table1 x, Table1 y)
    {
        return x.Column1 == y.Column1
            && x.Column2 == y.Column2
            && x.Column3 == y.Column3
            && x.Column4 == y.Column4
            && x.Column5 == y.Column5
            && x.Column6 == y.Column6;
    }

    public int GetHashCode(Table1 obj)
    {
        return obj.GetHashCode();
    }
}

Upvotes: 4

Related Questions