I have following Three DataTable. Please anyone help me to get invalid records using LINQ query.
I have done with SQL but I need use LINQ just Bcoz of my project requirement.
-----------------------------------------------------------------
SQL :
Create Table #Source
(
SourceEntity varchar(50),
SourceAccount varchar(50)
)
Create Table #EntityChart
(
TEntity varchar(50),
TChart varchar(50),
SourceEntity varchar(50),
SourceChart varchar(50)
)
Create Table #ChartAccount
(
TChart varchar(50),
TAccount varchar(50),
SourceChart varchar(50),
SourceAccount varchar(50)
)
INSERT INTO #Source VALUES ('A', '1')
INSERT INTO #Source VALUES ('B', '2')
INSERT INTO #Source VALUES ('C', '3')
INSERT INTO #Source VALUES ('D', '4')
INSERT INTO #Source VALUES ('E', '5')
INSERT INTO #Source VALUES ('F', '6')
INSERT INTO #EntityChart VALUES ('E1', 'C1', 'A', 'C')
INSERT INTO #EntityChart VALUES ('E2', 'C1', 'B', 'C')
INSERT INTO #EntityChart VALUES ('E3', 'C1', 'C', 'C')
INSERT INTO #EntityChart VALUES ('E4', 'D1', 'D', 'D')
INSERT INTO #EntityChart VALUES ('E5', 'C1', 'E', 'C')
INSERT INTO #EntityChart VALUES ('E6', 'C1', '', '')
INSERT INTO #ChartAccount VALUES ('C1', '1', 'C', '1')
INSERT INTO #ChartAccount VALUES ('C1', '2', 'C', '2')
INSERT INTO #ChartAccount VALUES ('C1', '3', 'C', '3')
INSERT INTO #ChartAccount VALUES ('C1', '4', 'C', '4')
INSERT INTO #ChartAccount VALUES ('D1', '5', 'D1', '5')
INSERT INTO #ChartAccount VALUES ('C1', '6', '', '')
select s.*, ec.TEntity, ca.TChart from #Source s left outer join #EntityChart ec
on s.SourceEntity = ec.SourceEntity left outer join #ChartAccount ca
on ec.TChart = ca.TChart and ec.SourceChart = ca.SourceChart and s.SourceAccount = ca.SourceAccount
where ec.TEntity IS NULL OR ca.TChart IS NULL
-------------------------------------------------------------------
===================================================================
-------------------------------------------------------------------
C# :
DataTable sourceTable = new DataTable();
sourceTable.Columns.Add("SEntity");
sourceTable.Columns.Add("SAccount");
sourceTable.Rows.Add("A", "1");
sourceTable.Rows.Add("B", "2");
sourceTable.Rows.Add("C", "3");
sourceTable.Rows.Add("D", "4");
sourceTable.Rows.Add("E", "5");
sourceTable.Rows.Add("F", "6");
DataTable entityChartTable = new DataTable();
entityChartTable.Columns.Add("TEntity");
entityChartTable.Columns.Add("TChart");
entityChartTable.Columns.Add("SEntity");
entityChartTable.Columns.Add("SChart");
entityChartTable.Rows.Add("E1", "C1", "A", "C");
entityChartTable.Rows.Add("E2", "C1", "B", "C");
entityChartTable.Rows.Add("E3", "C1", "C", "C");
entityChartTable.Rows.Add("E4", "D1", "D", "D");
entityChartTable.Rows.Add("E5", "C1", "E", "C");
entityChartTable.Rows.Add("E6", "C1", "", "");
DataTable chartAccountTable = new DataTable();
chartAccountTable.Columns.Add("TChart");
chartAccountTable.Columns.Add("TAccount");
chartAccountTable.Columns.Add("SChart");
chartAccountTable.Columns.Add("SAccount");
chartAccountTable.Rows.Add("C1", "1", "C", "1");
chartAccountTable.Rows.Add("C2", "2", "C", "2");
chartAccountTable.Rows.Add("C3", "3", "C", "3");
chartAccountTable.Rows.Add("C4", "4", "C", "4");
chartAccountTable.Rows.Add("D1", "5", "D1", "5");
chartAccountTable.Rows.Add("C6", "6", "", "");