How To get Missing Rows

Question

Dear experts,

var result = (from p in excel2table.AsEnumerable()
                                      join v in LinkedBills.AsEnumerable()
                                      //This is how you join by multiple values
                                      on new { no = p.Field<string>("doc_no") }
                                      equals new { no = v.Field<string>("bill_no") }
                                      into jointData
                                      //This is how you actually turn the join into a left-join
                                      from jointRecord in jointData.DefaultIfEmpty()
                                      where jointData is null
                                      select new
                                      {
                                          doc_no = p.Field<string>("doc_no"),
                                          linkamt = jointRecord?.Field<decimal>("linkamt") ?? 0
                                          
                                          
                                      }).ToList();

above query giving me no rows, 

if i remove where joindata is null, i get all rows in excel2table and  unmatched rows has linkamt=0

this is too much of data which is not requied. the purpose of this query is to find missing doc_no and unmatched linkamt

but i want only doc_no not in linkedbills or excel2table.linkamt <>linkedbills .linkamt

Answer 1

To get the items that are not in LinkedBills according to “doc_no” and “bill_no” fields, I think that you can try this query:

var query = excel2table.AsEnumerable().Where( e => ! LinkedBills.AsEnumerable().Any( b => b.Field<string>(“bill_no”) == e.Field<string>(“doc_no”)).

Probably you can also filter by “linkamt” in a similar manner.

Or show more details about the fields and required filtering.

Answer 2

Thanks Virol,

//https://stackoverflow.com/questions/3944803/use-linq-to-get-items-in-one-list-that-are-not-in-another-list
                        var xcludedlinks = (excel2table.AsEnumerable()
                            .Where(p => LinkedBills.AsEnumerable()
                            .All(p2 => p2.Field<string>("bill_no")!= p.Field<string>("doc_no")))).ToList();

is working partitialy o.k. i.e. based on doc_no comparision record not found in linkedbills only could be filtered

bust i know there will be cases when doc_no with be found but mismatched  linkamt records will be missed in above query, but i want that also to be incuded in result.

can some body suggest a good exaustive book or on line reading to understand linq syntax , i stimply get stuck do linq, code that i can weite for t-sql , i am stumped due to linq syntax

Answer 3

Hi Sushil Agarwal, 
About linq syntax, here are some documents you can refer to.
[Language Integrated Query (LINQ)]
[LINQ in C#]
Best Regards,
Daniel Zhang

---

"Windows Forms General" forum will be migrating to a new home on Microsoft Q&A (Preview)!
We invite you to post new questions in the "Windows Forms General" forum’s new home on Microsoft Q&A (Preview)!
For more information, please refer to the sticky post.

Answer 4

To get the items that are not in LinkedBills according to “linkamt” fields, I think that you can try this second query:

var query2 = excel2table.AsEnumerable().Where( e => ! LinkedBills.AsEnumerable().Any( b => b.Field<string>(“linkamt”) == e.Field<string>(“linkamt”)).

To combine the above queries, avoiding duplicates:

var result = query.Union( query2 ).

Or maybe reformulate the filtering criteria for better interpretation. You can also show the equivalent SQL query.

Answer 5

Thanks Viorel

i tried your suggetion 

//https://stackoverflow.com/questions/3944803/use-linq-to-get-items-in-one-list-that-are-not-in-another-list
                        var xcludedbills = excel2table.AsEnumerable()
                                            .Where(
                                                    p => LinkedBills.AsEnumerable()
                                                        .All(p2 => p2.Field<string>("bill_no") != p.Field<string>("doc_no"))
                                                    );

                        var mmlinkamt = excel2table.AsEnumerable()
                            .Where(e1 => !LinkedBills.AsEnumerable()
                            .Any(b => b.Field<decimal>("linkamt") == e1.Field<decimal>("linkamt")));

                        //To combine the above queries, avoiding duplicates:
                        var result = (xcludedbills.Union(mmlinkamt)).ToList();

but i am getting error ?

Answer 6

If the next test gives the same error:

   var test = mmlinkamt.ToList(),

then maybe “linkamt” field is not decimal. If it is string, then maybe compare the values of Field<string>, or use ‘decimal.Parse(b.Field<string>("linkamt")) == e1…’.

Clarify the type of fields.

Answer 7

excel2table.linkamt is system.Double and LinkedBills..linkamt is decimal

when as per your example i simply copy pasted and there linkamt was string it gave error, i corrected

now as per above new code i got error which i could not understand

the error was on 3459 and i.e. the line having code UNION and .Tolist()

even i tried 

var mmlinkamt = (excel2table.AsEnumerable()
                            .Where(e1 => LinkedBills.AsEnumerable()
                                        .All(b => b.Field<decimal>("linkamt")-e1.Field<decimal>("linkamt")!=0))).ToList();

but the same cast mismatch error is persisting

even if i cast both the feilds to decimal same error

Answer 8

Hi Sushil Agarwal, 
According to the two queries provided by Viorel_, have you got the correct data?
If the data obtained is correct, I suggest you can convert them into a list first and then merge them.
Here is a thread about joining two lists you can refer to.
Best Regards,
Daniel Zhang

---

"Windows Forms General" forum will be migrating to a new home on Microsoft Q&A (Preview)!
We invite you to post new questions in the "Windows Forms General" forum’s new home on Microsoft Q&A (Preview)!
For more information, please refer to the sticky post.

Answer 9

Dear Daniel_Zhang,

Thanks for your support.

but sorry to say the second qury do not run correctly .

even casting the linkamt both to sae types or sustracting and then checking diffrence !=0 still throws the same error.