问题描述
Given:
A table named TABLE_1 with the following columns:
- ID
- ColumnA
- ColumnB
- ColumnC
I have SQL query where TABLE_1 joins on itself twice based off of ColumnA, ColumnB, ColumnC. The query might look something like this:
Select t1.ID, t2.ID, t3.ID From TABLE_1 t1 Left Join TABLE_1 t2 On t1.ColumnA = t2.ColumnA And t1.ColumnB = t2.ColumnB And t1.ColumnC = t2.ColumnC Left Join TABLE_1 t3 On t2.ColumnA = t3.ColumnA And t2.ColumnB = t3.ColumnB And t2.ColumnC = t3.ColumnC ... and query continues on etc.
Problem:
I need that Query to be rewritten in LINQ. I've tried taking a stab at it:
var query = from t1 in myTABLE1List // List<TABLE_1> join t2 in myTABLE1List on t1.ColumnA equals t2.ColumnA && t1.ColumnB equals t2.ColumnA // ... and at this point intellisense is making it very obvious // I am doing something wrong :(
How do I write my query in LINQ? What am I doing wrong?
Joining on multiple columns in Linq to SQL is a little different.
var query = from t1 in myTABLE1List // List<TABLE_1> join t2 in myTABLE1List on new { t1.ColumnA, t1.ColumnB } equals new { t2.ColumnA, t2.ColumnB } ...
You have to take advantage of anonymous types and compose a type for the multiple columns you wish to compare against.
This seems confusing at first but once you get acquainted with the way the SQL is composed from the expressions it will make a lot more sense, under the covers this will generate the type of join you are looking for.
EDIT Adding example for second join based on comment.
var query = from t1 in myTABLE1List // List<TABLE_1> join t2 in myTABLE1List on new { A = t1.ColumnA, B = t1.ColumnB } equals new { A = t2.ColumnA, B = t2.ColumnB } join t3 in myTABLE1List on new { A = t2.ColumnA, B = t2.ColumnB } equals new { A = t3.ColumnA, B = t3.ColumnB } ...