using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
namespace LinqToDataTable
{
class Program
{
static void Main(string[] args)
{
DataTable Table1 = new DataTable();
Table1.Columns.Add(new DataColumn("Id", Type.GetType("System.Int32")));
Table1.Columns.Add(new DataColumn("Value", Type.GetType("System.String")));
DataRow row = null;
row = Table1.NewRow();
row["Id"] = 1;
row["Value"] = "First";
Table1.Rows.Add(row);
row = null;
row = Table1.NewRow();
row["Id"] = 2;
row["Value"] = "Second";
Table1.Rows.Add(row);
row = null;
row = Table1.NewRow();
row["Id"] = 3;
row["Value"] = "Third";
Table1.Rows.Add(row);
row = null;
row = Table1.NewRow();
row["Id"] = 4;
row["Value"] = "Fourth";
Table1.Rows.Add(row);
row = null;
row = Table1.NewRow();
row["Id"] = 5;
row["Value"] = "Fifth";
Table1.Rows.Add(row);
DataTable Table2 = new DataTable();
Table2.Columns.Add(new DataColumn("Id", Type.GetType("System.Int32")));
Table2.Columns.Add(new DataColumn("Value", Type.GetType("System.String")));
row = null;
row = Table2.NewRow();
row["Id"] = 1;
row["Value"] = "First";
Table2.Rows.Add(row);
row = null;
row = Table2.NewRow();
row["Id"] = 2;
row["Value"] = "Second";
Table2.Rows.Add(row);
row = null;
row = Table2.NewRow();
row["Id"] = 3;
row["Value"] = "Third";
Table2.Rows.Add(row);
row = null;
row = Table2.NewRow();
row["Id"] = 6;
row["Value"] = "Six";
Table2.Rows.Add(row);
row = null;
row = Table2.NewRow();
row["Id"] = 7;
row["Value"] = "Seven";
Table2.Rows.Add(row);
row = null;
row = Table2.NewRow();
row["Id"] = 8;
row["Value"] = "Eight";
Table2.Rows.Add(row);
Console.WriteLine("---Inner join in DataTables.---");
Console.WriteLine(Environment.NewLine);
var data = from table1 in Table1.AsEnumerable()
join table2 in Table2.AsEnumerable()
on table1.Field<Int32>("Id") equals table2.Field<Int32>("Id")
select new { Table1Id=table1.Field<Int32>("Id"),
Table1Value = table1.Field<String>("Value"),
Table2Id = table2.Field<Int32>("Id"),
Table2Value = table2.Field<String>("Value")
};
foreach (var item in data)
{
Console.WriteLine("Table1 Id : {0}\tTable1 Value : {1}\tTable2 Id : {2}\tTable2 Value : {3}"
,item.Table1Id
, item.Table1Value
, item.Table2Id
, item.Table2Value
);
}
Console.WriteLine(Environment.NewLine);
Console.WriteLine("---Left Join in DataTables.---");
Console.WriteLine(Environment.NewLine);
var data1 = from table1 in Table1.AsEnumerable()
join table2 in Table2.AsEnumerable()
on table1.Field<Int32>("Id") equals table2.Field<Int32>("Id") into table1_table2
from items in table1_table2.DefaultIfEmpty()
select new
{
Table1Id = table1.Field<Int32>("Id"),
Table1Value = table1.Field<String>("Value"),
Table2Id = items == null ? 0 : items.Field<Int32>("Id"),
Table2Value = items == null ? "Null" : items.Field<String>("Value")
};
foreach (var item in data1)
{
Console.WriteLine("Table1 Id : {0}\tTable1 Value : {1}\tTable2 Id : {2}\tTable2 Value : {3}"
, item.Table1Id
, item.Table1Value
, item.Table2Id
, item.Table2Value
);
}
Console.WriteLine(Environment.NewLine);
Console.WriteLine("---Right Join in DataTables.---");
Console.WriteLine(Environment.NewLine);
var data2 = from table2 in Table2.AsEnumerable()
join table1 in Table1.AsEnumerable()
on table2.Field<Int32>("Id") equals table1.Field<Int32>("Id") into table1_table2
from items in table1_table2.DefaultIfEmpty()
select new
{
Table1Id = items == null ? 0 : items.Field<Int32>("Id"),
Table1Value = items == null ? "Null" : items.Field<String>("Value"),
Table2Id = table2.Field<Int32>("Id"),
Table2Value = table2.Field<String>("Value")
};
foreach (var item in data2)
{
Console.WriteLine("Table1 Id : {0}\tTable1 Value : {1}\tTable2 Id : {2}\tTable2 Value : {3}"
, item.Table1Id
, item.Table1Value
, item.Table2Id
, item.Table2Value
);
}
Console.WriteLine(Environment.NewLine);
Console.WriteLine("---Outer Join in DataTables.---");
Console.WriteLine(Environment.NewLine);
var data3 = (from table1 in Table1.AsEnumerable()
join table2 in Table2.AsEnumerable()
on table1.Field<Int32>("Id") equals table2.Field<Int32>("Id") into table1_table2
from table2 in table1_table2.DefaultIfEmpty()
//where table2 == null
select new { table1, table2 }
).Concat(from table2 in Table2.AsEnumerable()
join t1 in Table1.AsEnumerable()
on table2.Field<Int32>("Id") equals t1.Field<Int32>("Id") into t1_t2
from table1 in t1_t2.DefaultIfEmpty()
where table1 == null
select new { table1, table2 }
);
foreach (var item in data3)
{
Console.WriteLine("Table1 Id : {0}\tTable1 Value : {1}\tTable2 Id : {2}\tTable2 Value : {3}"
, item.table1 == null ? 0 : item.table1.Field<Int32>("Id")
, item.table1 == null ? "Null" : item.table1.Field<String>("Value")
, item.table2 == null ? 0 : item.table2.Field<Int32>("Id")
, item.table2 == null ? "Null" : item.table2.Field<String>("Value")
);
}
Console.WriteLine(Environment.NewLine);
Console.WriteLine("---Left Join where null in DataTables.---");
Console.WriteLine(Environment.NewLine);
var data4 = from table1 in Table1.AsEnumerable()
join table2 in Table2.AsEnumerable()
on table1.Field<Int32>("Id") equals table2.Field<Int32>("Id") into table1_table2
from items in table1_table2.DefaultIfEmpty()
where items == null
select new
{
Table1Id = table1.Field<Int32>("Id"),
Table1Value = table1.Field<String>("Value"),
Table2Id = items == null ? 0 : items.Field<Int32>("Id"),
Table2Value = items == null ? "Null" : items.Field<String>("Value")
};
foreach (var item in data4)
{
Console.WriteLine("Table1 Id : {0}\tTable1 Value : {1}\tTable2 Id : {2}\tTable2 Value : {3}"
, item.Table1Id
, item.Table1Value
, item.Table2Id
, item.Table2Value
);
}
Console.WriteLine(Environment.NewLine);
Console.WriteLine("---Right Join where null in DataTables.---");
Console.WriteLine(Environment.NewLine);
var data5 = from table2 in Table2.AsEnumerable()
join table1 in Table1.AsEnumerable()
on table2.Field<Int32>("Id") equals table1.Field<Int32>("Id") into table1_table2
from items in table1_table2.DefaultIfEmpty()
where items==null
select new
{
Table1Id = items == null ? 0 : items.Field<Int32>("Id"),
Table1Value = items == null ? "Null" : items.Field<String>("Value"),
Table2Id = table2.Field<Int32>("Id"),
Table2Value = table2.Field<String>("Value")
};
foreach (var item in data5)
{
Console.WriteLine("Table1 Id : {0}\tTable1 Value : {1}\tTable2 Id : {2}\tTable2 Value : {3}"
, item.Table1Id
, item.Table1Value
, item.Table2Id
, item.Table2Value
);
}
Console.WriteLine(Environment.NewLine);
Console.WriteLine("---Outer Join where null in DataTables.---");
Console.WriteLine(Environment.NewLine);
var data6 = (from table1 in Table1.AsEnumerable()
join table2 in Table2.AsEnumerable()
on table1.Field<Int32>("Id") equals table2.Field<Int32>("Id") into table1_table2
from table2 in table1_table2.DefaultIfEmpty()
where table2 == null
select new { table1, table2 }
).Concat(from table2 in Table2.AsEnumerable()
join t1 in Table1.AsEnumerable()
on table2.Field<Int32>("Id") equals t1.Field<Int32>("Id") into t1_t2
from table1 in t1_t2.DefaultIfEmpty()
where table1 == null
select new { table1,table2}
);
foreach (var item in data6)
{
Console.WriteLine("Table1 Id : {0}\tTable1 Value : {1}\tTable2 Id : {2}\tTable2 Value : {3}"
, item.table1 == null ? 0 : item.table1.Field<Int32>("Id")
, item.table1==null? "Null":item.table1.Field<String>("Value")
, item.table2 == null ? 0 : item.table2.Field<Int32>("Id")
, item.table2 == null ? "Null" : item.table2.Field<String>("Value")
);
}
Console.WriteLine(Environment.NewLine);
Console.WriteLine("---Cross Join in DataTables.---");
Console.WriteLine(Environment.NewLine);
var data7 = from table1 in Table1.AsEnumerable()
from table2 in Table2.AsEnumerable()
select new { table1, table2 };
foreach (var item in data7)
{
Console.WriteLine("Table1 Id : {0}\tTable1 Value : {1}\tTable2 Id : {2}\tTable2 Value : {3}"
, item.table1.Field<Int32>("Id")
, item.table1.Field<String>("Value")
, item.table2.Field<Int32>("Id")
, item.table2.Field<String>("Value")
);
}
Console.Read();
}
}
}
/*******************************************************************************/
--Equivalent SQL Queryes
Go
Create database softwarekaffee
Go
Use softwarekaffee
Go
Create table table1
(ID INT, Value VARCHAR(10))
INSERT INTO table1 (ID, Value)
Select 1,'First'
UNION ALL
Select 2,'Second'
UNION ALL
Select 3,'Third'
UNION ALL
Select 4,'Fourth'
UNION ALL
Select 5,'Fifth'
GO
Create table table2
(ID INT, Value VARCHAR(10))
INSERT INTO table2 (ID, Value)
Select 1,'First'
UNION ALL
Select 2,'Second'
UNION ALL
Select 3,'Third'
UNION ALL
Select 6,'Sixth'
UNION ALL
Select 7,'Seventh'
UNION ALL
Select 8,'Eighth'
GO
Select *
From table1
Select *
From table2
GO
/* INNER JOIN */
Select t1.*,t2.*
From table1 t1
INNER JOIN table2 t2 ON t1.ID = t2.ID
GO
/* LEFT JOIN */
Select t1.*,t2.*
From table1 t1
LEFT JOIN table2 t2 ON t1.ID = t2.ID
GO
/* RIGHT JOIN */
Select t1.*,t2.*
From table1 t1
RIGHT JOIN table2 t2 ON t1.ID = t2.ID
GO
/* OUTER JOIN */
Select t1.*,t2.*
From table1 t1
FULL OUTER JOIN table2 t2 ON t1.ID = t2.ID
GO
/* LEFT JOIN - WHERE NULL */
Select t1.*,t2.*
From table1 t1
LEFT JOIN table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL
GO
/* RIGHT JOIN - WHERE NULL */
Select t1.*,t2.*
From table1 t1
RIGHT JOIN table2 t2 ON t1.ID = t2.ID
WHERE t1.ID IS NULL
GO
/* OUTER JOIN - WHERE NULL */
Select t1.*,t2.*
From table1 t1
FULL OUTER JOIN table2 t2 ON t1.ID = t2.ID
WHERE t1.ID IS NULL OR t2.ID IS NULL
GO
/* CROSS JOIN */
Select t1.*,t2.*
From table1 t1
CROSS JOIN table2 t2
No comments:
Post a Comment