如何使用LINQ - 左外连接查询查找无效(null)记录 [英] How to find invalid (null) records using LINQ - left outer join query
问题描述
我有三个DataTable。请任何人帮我使用LINQ查询获取无效记录。
我已经完成了SQL但我需要使用LINQ只是我项目要求的Bcoz。
--------------------------------------- --------------------------
SQL:
创建 表 #Source
(
SourceEntity varchar ( 50 ),
SourceAccount varchar ( 50 )
)
创建 表 #EntityChart
(
TEntity varchar ( 50 ),
TChart varchar ( 50 ),
SourceEntity varchar ( 50 ),
SourceChart varchar ( 50 )
)
创建 表 #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 (< span class =code-string>' E4',' D1',' D',' D')
INSERT INTO #EntityChart VALUES (' E5',' < span class =code-string> C1',' E',' C')
INSERT < span class =code-keyword> INTO #EntityChart VALUES (' E6',' C1',' ',' ')
INSERT INTO #ChartAccount VALUE S (' 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 (< span class =code-string>' D1',' 5',' D1',' 5')
INSERT INTO #ChartAccount VALUES (' C1',' < span class =code-string> 6',' ',' ')
select s。*,ec.TEntity,ca.TChart 来自 #Source s left outer join #EntityChart ec
on s.SourceEntity = ec.SourceEntity left 外部 加入# ChartAccount ca
ec.TChart = ca.TChart 和 ec .SourceChart = ca.SourceChart 和 s.SourceAccount = ca.SourceAccount
其中 ec.TEntity IS NULL 或 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, , );
一个非常简单的方法是部分完成此操作。执行第一个外部联接查询并在第二个外部联接查询中使用它。请考虑以下示例(不确定连接列是否正确)
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();
< br $>
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();
这篇关于如何使用LINQ - 左外连接查询查找无效(null)记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!