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?
This question is related to
c#
linq-to-sql
left-join
multiple-columns
Title_Authors is a look up two things join at a time project results and continue chaining
DataClasses1DataContext db = new DataClasses1DataContext();
var queryresults = from a in db.Authors
join ba in db.Title_Authors
on a.Au_ID equals ba.Au_ID into idAuthor
from c in idAuthor
join t in db.Titles
on c.ISBN equals t.ISBN
select new { Author = a.Author1,Title= t.Title1 };
foreach (var item in queryresults)
{
MessageBox.Show(item.Author);
MessageBox.Show(item.Title);
return;
}
U can also use :
var query =
from t1 in myTABLE1List
join t2 in myTABLE1List
on new { ColA=t1.ColumnA, ColB=t1.ColumnB } equals new { ColA=t2.ColumnA, ColB=t2.ColumnB }
join t3 in myTABLE1List
on new {ColC=t2.ColumnA, ColD=t2.ColumnB } equals new { ColC=t3.ColumnA, ColD=t3.ColumnB }
You can write your query like this.
var query = from t1 in myTABLE1List // List<TABLE_1>
join t2 in myTABLE1List
on t1.ColumnA equals t2.ColumnA
and t1.ColumnB equals t2.ColumnA
If you want to compare your column with multiple columns.
You can also join if the number of columns are not same in both tables and can map static value to table column
from t1 in Table1
join t2 in Table2
on new {X = t1.Column1, Y = 0 } on new {X = t2.Column1, Y = t2.Column2 }
select new {t1, t2}
I would like to give another example in which multiple (3) joins are used.
DataClasses1DataContext ctx = new DataClasses1DataContext();
var Owners = ctx.OwnerMasters;
var Category = ctx.CategoryMasters;
var Status = ctx.StatusMasters;
var Tasks = ctx.TaskMasters;
var xyz = from t in Tasks
join c in Category
on t.TaskCategory equals c.CategoryID
join s in Status
on t.TaskStatus equals s.StatusID
join o in Owners
on t.TaskOwner equals o.OwnerID
select new
{
t.TaskID,
t.TaskShortDescription,
c.CategoryName,
s.StatusName,
o.OwnerName
};
In LINQ2SQL you seldom need to join explicitly when using inner joins.
If you have proper foreign key relationships in your database you will automatically get a relation in the LINQ designer (if not you can create a relation manually in the designer, although you should really have proper relations in your database)
Then you can just access related tables with the "dot-notation"
var q = from child in context.Childs
where child.Parent.col2 == 4
select new
{
childCol1 = child.col1,
parentCol1 = child.Parent.col1,
};
will generate the query
SELECT [t0].[col1] AS [childCol1], [t1].[col1] AS [parentCol1]
FROM [dbo].[Child] AS [t0]
INNER JOIN [dbo].[Parent] AS [t1] ON ([t1].[col1] = [t0].[col1]) AND ([t1].[col2] = [t0].[col2])
WHERE [t1].[col2] = @p0
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [4]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
In my opinion this is much more readable and lets you concentrate on your special conditions and not the actual mechanics of the join.
Edit
This is of course only applicable when you want to join in the line with our database model. If you want to join "outside the model" you need to resort to manual joins as in the answer from Quintin Robinson
In my opinion, this is the simplest way to join two tables with multiple fields:
from a in Table1 join b in Table2
on (a.Field1.ToString() + "&" + a.Field2.ToString())
equals (b.Field1.ToString() + "&" + b.Field2.ToString())
select a
Source: Stackoverflow.com