How to compare two datatables using wildcards?

Question

0.00/5 (No votes)

See more:

Hi All,
I have to compare two datatables using LINQ

C#

// compare if fieldsforDynamicGroup have Already groups

            DataTable merged = (from a in fieldsforDynamicGroup.AsEnumerable()
                                join b in Dtlists.AsEnumerable()
                                on a["FieldName"].ToString() equals           b["ListName"].ToString()
                                into g
                                where g.Count() > 0
                                select a).CopyToDataTable();

but the problem is that i can't directly join a["FieldName"].ToString() equals b["ListName"].ToString()

the values of FieldName column is like "Facebook Link" in A
and ListName is like "All recruits who had Facebook Link change yesterday" in B

How can i join on the basis of wildcards %% ...Is there any performance degradation??
Please help!

Posted 9-Feb-15 9:30am

Gaurav_Chaudhary

Updated 9-Feb-15 9:33am

v3

Add a Solution

2 solutions

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)

Richard Deeming · Accepted Answer · 2015-02-09T10:56:00

Solution 3

You can do it with LINQ, but it's not pretty:

C#

DataTable merged = fieldsforDynamicGroup.AsEnumerable()
    .Where(a => Dtlists.AsEnumerable()
        .Select(b => b.Field<string>("ListName") ?? string.Empty)
        .Any(list => list.Contains(a.Field<string>("FieldName")))
        
        // Or:
        // .Any(list => list.IndexOf(a.Field<string>("FieldName"), StringComparison.CurrentCultureIgnoreCase) != -1)
    )
    .CopyToDataTable();

Posted 9-Feb-15 10:56am

Richard Deeming

Updated 10-Feb-15 1:28am

v3

Comments

Maciej Los 9-Feb-15 17:05pm

+5

[no name] 9-Feb-15 18:04pm

Thanks for solution +5 please tell if there some other method that's better than this

[no name] 10-Feb-15 2:01am

There is no overload that takes a StringComparison parameter.
Its working now
DataTable merged = fieldsforDynamicGroup.AsEnumerable()
.Where(a => Dtlists.AsEnumerable()
.Select(b => b.Field<string>("ListName") ?? string.Empty)
.Any(list => list.Contains(a.Field<string>("FieldName")))
)
.CopyToDataTable();

Richard Deeming 10-Feb-15 7:28am

Whoops! That's one of the annoying missing overloads on the string methods.

If you need a case-insensitive comparison, you can use IndexOf instead.

I've updated the answer.

[no name] 10-Feb-15 8:24am

Thanks Richard for help, it really helped me out :) answer accepted

[no name] 10-Feb-15 9:31am

Hey Richard, Sir i am a beginner to LINQ to dataset/datatable. Could you provide me some guidance or links. You seem like a genius
Thanks in advance!

Richard Deeming 10-Feb-15 9:44am

LINQPad[^] is a good place to start - it comes with a lot of interactive examples to introduce you to LINQ.

[no name] 10-Feb-15 10:22am

Thankyou :) I really needed a good software like Linqpad. Its awesome!

manchanx · Accepted Answer · 2015-02-09T10:22:00

You can't do it with a Linq Join because a Linq Join absolutely requires that the joined columns are compared for being equal or not.

Looks like you want all records from A where "FieldName" occurs in any "ListName" of B. My first idea would be:

C#

var distinctListNames = Dtlists.AsEnumerable()
          .Select(r => r.Field<string>("ListName").ToUpper()).Distinct();

string joinedListNames = String.Join("$$$", distinctListNames); 

foreach(DataRow row in fieldsforDynamicGroup.Rows)
{
   if(joinedListNames.Contains(row.Field<string>("FieldName").ToUpper()))
   {
      // do something with the row
   }
}

The "$$$" serves as some weird separator so that matches "can't" occur across adjacent ListNames.

Maybe not the most performant of all solutions but the best I could come up with quickly ;)

Cheers!