DataTable filtering

[Jenner]

Hi all, I got a rather general question about DataTables, just to see if I'm missing anything.

I have two DataTables. The first is something like this:
Table: Master_list_of_parts
Columns:
PART (string)
REVISION (string)
DATA1
DATA2
.
.
DATAETC

The second:
Table: Parts_completed
Columns:
PART (string)
REVISION (string)

In each table, the combination of "PART" and "REVISION" is a primary, unique key.

Each DataTable comes from a completely different database (the first is from MSSQL, the second from MySQL) so I can't just do this with a line of SQL.
What I'm trying to do is "DataTable1 - DataTable2 = DataTable3 (where PART = PART and REVISION = REVISION); i.e. filter out all the rows from the first data table that have a match in the second.

I can do this with loops, and .Search() and other methods, but I'm curious what the most "elegant" way to do this is? (least code, fastest, least computations)

Thanks in advance for any suggestions!

[Shaggy Hiker]

I almost hate to suggest it, since I'm no big fan of the technology, but what you are talking about is a JOIN....and LINQ allows you to do joins between datatables, so it is likely that you could do this in one line with LINQ. The reason I don't much like LINQ is two things. The first is that, while it is compact, it isn't particularly efficient. I have yet to see some LINQ that performs better than writing a loop, so the whole reason for using it is that you can type less, and the difference in speed almost never matters. It's not a HUGE difference, after all. The other thing I don't really like about LINQ is that it can be harder to parse. You can write such convoluted stuff into a single line of LINQ that it's hard for a good coder to parse it fully, so it doesn't teach well.

Having said that, this is a case where both of those objections are probably invalid. Writing a JOIN using loops would be sufficiently tedious that LINQ may perform better than the loops, or at least as well. Second, writing a join using loops is going to take enough code that it will be harder for somebody to understand at a glance than just writing what amounts to SQL.

There are numerous examples, but this one looks pretty suited to your question (once you get past a page or two of setting up the problem):

https://docs.microsoft.com/en-us/dot...by-using-joins

[Jenner]

Yea, I don't care for LINQ for the same reasons. That's one of the ways I set it up to do it.
If it was as easy as SQL to a single database, I'd do something like SELECT * FROM Table1 WHERE Part + Rev NOT IN (SELECT Part + Rev FROM Table2)

I'm messing around with .Select and DataSet relations now. There's no way I'm happy with doing this. I mostly asked the question to see if I'm missing some stupid, obvious answer and to ask other programmers "how would you do this"?

[pclement]

Just because the data sources are from different databases doesn't mean you can't use SQL. Have you tried creating a linked server table from SQL Server to MySQL and then use a SELECT statement with OPENQUERY? The below links should help:

http://www.c-sharpcorner.com/article...to-sql-server/
https://stackoverflow.com/questions/...t-in-statement

[topshot]

Could you perhaps create a link from one db to the other so you can just do it in SQL? One such example

Edit: I see pclement beat me to the punch.

[techgnome]

I almost hate to suggest it, since I'm no big fan of the technology, but what you are talking about is a JOIN....and LINQ allows you to do joins between datatables, so it is likely that you could do this in one line with LINQ. The reason I don't much like LINQ is two things. The first is that, while it is compact, it isn't particularly efficient. I have yet to see some LINQ that performs better than writing a loop, so the whole reason for using it is that you can type less, and the difference in speed almost never matters. It's not a HUGE difference, after all. The other thing I don't really like about LINQ is that it can be harder to parse. You can write such convoluted stuff into a single line of LINQ that it's hard for a good coder to parse it fully, so it doesn't teach well.

Having said that, this is a case where both of those objections are probably invalid. Writing a JOIN using loops would be sufficiently tedious that LINQ may perform better than the loops, or at least as well. Second, writing a join using loops is going to take enough code that it will be harder for somebody to understand at a glance than just writing what amounts to SQL.

There are numerous examples, but this one looks pretty suited to your question (once you get past a page or two of setting up the problem):

https://docs.microsoft.com/en-us/dot...by-using-joins

If I was still at my previous job, I could actually supply you with some code where LINQ/Lambda setup was faster than a filter/loop... by many orders... I couldn't explain it then, I still can't explain it now. But we had several bottlenecks that were performing pretty badly until they were replaced by a combination of LINQ & Lambdas, even though convetional wisdom would suggest the opposite should be true. Needless to say, it baffled a lot of us. But the evidence at the time was hard to ignore. That said, I don't remember much about the circumstances, and there may have been joins involved, and that's why there was an improvement.

-tg

[jmcilhinney]

I think that there's also some confusion about what LINQ actually is. For instance, you can't use Entity Framework without using LINQ. It's good to understand the difference between LINQ the technology and individual LINQ providers. When Shaggy says that he doesn't like LINQ, I think that he's really talking about LINQ to Objects, which is one specific provider for generic collections. LINQ to DataSet is another provider, as is LINQ to SQL and LINQ to Entities is the one for EF. There are various others from Microsoft, e.g. LINQ to XML, and third parties have provided their own implementations too.

[Jenner]

Can't do that. The environment has the MSSQL server isolated. I'm using essentially an edge server to send requests to it and it sends back DataTable objects.

I need to read the MSSQL datatable into a set of data objects anyways. The solution I'm thinking I'm settling on is:

Load the key data from the MySQL datatable into a HashSet(Of String)
Enumerate the MSSQL datatable, check if it's key column information is contained in the HashSet and if it is, skip it.
Otherwise, load the data Row into a new data object and append that object to a collection.

I'm enumerating the table regardless, and the lookup to the HashSet is pretty minimal.

[techgnome]

I think that there's also some confusion about what LINQ actually is. For instance, you can't use Entity Framework without using LINQ. It's good to understand the difference between LINQ the technology and individual LINQ providers. When Shaggy says that he doesn't like LINQ, I think that he's really talking about LINQ to Objects, which is one specific provider for generic collections. LINQ to DataSet is another provider, as is LINQ to SQL and LINQ to Entities is the one for EF. There are various others from Microsoft, e.g. LINQ to XML, and third parties have provided their own implementations too.

True, in our case it would have been LINQ to (typed) DataSets that we were using. It probably also doesn't help that people also confuse the use of type extensions and Lambdas with LINQ... you can use extensions and Lambdas w/o LINQ but you can't really use LINQ w/o the extensions (in fact, if I remember right, they were added to support LINQ).

-tg

[Jenner]

My issue with LINQ is the readability. I've certainly used it, and in many cases, rather extensively to sort and filter things like data objects, but it seems when I go back years later and look at that code, while very tight and orderly, I always scratch my head and wonder exactly how many quad machiattos I had the morning I wrote it.

[Shaggy Hiker]

I think that there's also some confusion about what LINQ actually is. For instance, you can't use Entity Framework without using LINQ. It's good to understand the difference between LINQ the technology and individual LINQ providers. When Shaggy says that he doesn't like LINQ, I think that he's really talking about LINQ to Objects, which is one specific provider for generic collections. LINQ to DataSet is another provider, as is LINQ to SQL and LINQ to Entities is the one for EF. There are various others from Microsoft, e.g. LINQ to XML, and third parties have provided their own implementations too.

That's true. My objections are both LINQ to Object and LINQ to Datasets. I haven't really used any other.