一个非常简单的方法是部分完成此操作。执行第一个外部联接查询并在第二个外部联接查询中使用它。请考虑以下示例（不确定连接列是否正确）

  var  query1 = 来自 s   sourceTable.AsEnumerable（）
 join  ec 在 entityChartTable.AsEnumerable（）on s.Field< string>（  SEntity）等于ec.Field< string>（  SEntity） into  i1 
来自 ec_sub  in  i1.DefaultIfEmpty（）
选择  new  {
 a1 = s.Field< string>（  SEntity），
 a2 = ec_sub ==  null ？  （缺少）：ec_sub.Field< string>（  SEntity），
 a3 = ec_sub ==  null ？  （缺少）：ec_sub.Field< string>（  TChart）
}; 

 var  query2 = 来自 q1  in  query1 
 join  ca 在 entityChartTable.AsEnumerable（）上q1。 a3等于ca.Field< string>（  SChart）进入 i1 
来自 ca_sub   i1.DefaultIfEmpty（）
选择  new  {
 a1 = q1.a1，
 a2 = q1。 a2，
 a3 = q1.a3，
 a4 = ca_sub ==  null ？  （缺少）：ca_sub.Field< string>（  SChart）
}; < /  字符串 >  < /   string  >  < /  字符串 >  < /  字符串 >  < /   string  >  < /   string  >  < /   string  >  

我已经验证了所有可能的以下查询这是正确的....



var queryInValidEntity =

（源自s中的s来源.AsEnumerable（）

加入ec在entityChartTable.AsEnumerable（）

on new {a = s.Field（SEntity）}等于new {a = ec.Field（SEntity）}

从k.DefaultIfEmpty（）中的m到k

。其中（p => p == null）

选择新的

{

SEntity =（m == null）？ s.Field（SEntity）：0，

SAccount =（m == null）？ s.Field（SAccount）：0，

警告=实体

}）。ToList（）;


var queryInValidAccount =

（源自sourceTable.AsEnumerable（）中的s

加入ec在entityChartTable.AsEnumerable（）

on new {a = s.Field（SEntity）}等于new {a = ec.Field（SEntity）}

join ca in chartAccountTable.AsEnumerable（）

on new {x = ec.Field（TChart），y = ec.Field（SChart），z = s.Field（SAccount）}等于new {x = ca.Field（TChart ），y = ca.Field（SChart），z = ca.Field（SAccount）}

从l.DefaultIfEmpty中的n进入l

（ ）。哪里（p => p == null）

选择新的

{

SEntity =（n == null）？s .Field（SEntity）：0，

SAccount =（n == null）？s。Field（SAccount）：0，

警告=帐户

}）。ToList（）;



var queryInValidSourceList = queryInValidEntity.Concat（queryInValidAccount）.ToList（）;

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");    // invalid
sourceTable.Rows.Add("E", "5");    //  invalid
sourceTable.Rows.Add("F", "6");    // invalid


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", "", "");

One quite easy way would be to do this in parts. Do the first outer join query and use it in the second outer join query. Consider the following example (not sure if the joining columns are correct)

var query1 = from s in sourceTable.AsEnumerable()
            join ec in entityChartTable.AsEnumerable() on s.Field<string>("SEntity") equals ec.Field<string>("SEntity") into i1
            from ec_sub in i1.DefaultIfEmpty()
            select new {
               a1 = s.Field<string>("SEntity"),
               a2 = ec_sub == null ? "(missing)" : ec_sub.Field<string>("SEntity"),
               a3 = ec_sub == null ? "(missing)" : ec_sub.Field<string>("TChart")
            };

var query2 = from q1 in query1
             join ca in entityChartTable.AsEnumerable() on q1.a3 equals ca.Field<string>("SChart") into i1
             from ca_sub in i1.DefaultIfEmpty()
             select new {
                a1 = q1.a1,
                a2 = q1.a2,
                a3 = q1.a3,
                a4 = ca_sub == null ? "(missing)" : ca_sub.Field<string>("SChart")
             };</string></string></string></string></string></string></string>

I have verified below queries with all possible cases it's correct....

var queryInValidEntity =
(from s in sourceTable.AsEnumerable()
join ec in entityChartTable.AsEnumerable()
on new { a = s.Field("SEntity") } equals new { a = ec.Field("SEntity") }
into k
from m in k.DefaultIfEmpty().Where(p => p == null)
select new
{
SEntity = (m == null) ? s.Field("SEntity") : "0",
SAccount = (m == null) ? s.Field("SAccount") : "0",
Warning = "Entity"
}).ToList();

var queryInValidAccount =
(from s in sourceTable.AsEnumerable()
join ec in entityChartTable.AsEnumerable()
on new { a = s.Field("SEntity") } equals new { a = ec.Field("SEntity") }
join ca in chartAccountTable.AsEnumerable()
on new { x = ec.Field("TChart"), y = ec.Field("SChart"), z = s.Field("SAccount") } equals new { x = ca.Field("TChart"), y = ca.Field("SChart"), z = ca.Field("SAccount") }
into l
from n in l.DefaultIfEmpty().Where(p => p == null)
select new
{
SEntity = (n == null) ? s.Field("SEntity") : "0",
SAccount = (n == null) ? s.Field("SAccount") : "0",
Warning = "Account"
}).ToList();

var queryInValidSourceList = queryInValidEntity.Concat(queryInValidAccount).ToList();