How to find invalid (null) records using LINQ - left outer join query

Question

1.00/5 (1 vote)

See more:

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 :

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# :

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

Posted 24-Jul-15 0:46am

amitjoshi_87

Updated 24-Jul-15 1:10am

Wendelius

v2

Add a Solution

Comments

Wendelius 24-Jul-15 7:11am

What is the criteria for an invalid record?

amitjoshi_87 29-Jul-15 6:51am

Hi Mika

Please refer SQL script.

Match all records of sourceTable with other two Tables. If we get invalid records in sourceTable then list out them.

waiting for your reply.....

Thanking you....

ZurdoDev 24-Jul-15 7:57am

Perhaps google how to do a left join in linq?

amitjoshi_87 29-Jul-15 6:54am

Hi Ryan

I have tried but I am not get similar case. I have done this with two different linq queries. But I need one common linq query.

Thanks for your guidance.

virusstorm 24-Jul-15 11:50am

If you actually Google "linq left join", the first link is the MSDN article that walks you through it.

Just out of curiosity, does your requirement actually say "do a left join in linq to find invalid records"?

amitjoshi_87 29-Jul-15 7:01am

Hello

I have already go through MSDN and I have use below queries for left join in LINQ

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

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

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

I am not sure it is correct or not? But It gives correct result.

Thanks

2 solutions

Solution 2

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)

C#

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>

Posted 29-Jul-15 1:32am

Wendelius

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

amitjoshi_87 · Accepted Answer · 2015-07-31T00:49:00

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();