Combine 2 Datatables with the common rows only

Question

I have 2 datatables:

_wizardStepSelectUnitDataSet.WizardStepSelectUnits_UnitsSelectedInOtherAgreements

and

_wizardStepSelectUnitDataSet.WizardStepSelectUnits_SelectedUnits

The schemas are the same, I need to know which rows exist on both datatables,

How can I do that into a new datatable?

There is no CODE: I only have 2 datatables already designed. They are already filled with some data in memory.

I need to know which are the common rows between both datatables using one ID field.

I cant copy the code of the designer, you will be overwhelmed.

I tried this: var result = from r in _wizardStepSelectUnitDataSet.WizardStepSelectUnits_UnitsSelectedInOtherAgreements.AsEnumerable() join c in _wizardStepSelectUnitDataSet.WizardStepSelectUnits_SelectedUnits.AsEnumerable() on r.Field("UnitId") equals c.Field("UnitId") select r;

        DataRow[] dr = result.Select(
            String.Format(CultureInfo.InvariantCulture.DateTimeFormat,"StartDate <= #{0}#", stopDate));

However I got a compiler error: Error 7 The type arguments for method 'System.Linq.Enumerable.Select(System.Collections.Generic.IEnumerable, System.Func)' cannot be inferred from the usage. Try specifying the type arguments explicitly. C:\BITProjects\TeamSystem\luival\refm\DEV\AgreementModule\GUI\WorkItems\UC021_HuurovereenkomstWizard_WorkItem.cs 511 28 Ceusters.REFM.AgreementModule.GUI

Thank you

possible duplicate of [C# - Merge two DataTables where rows are duplicate](http://stackoverflow.com/questions/6833454/c-merge-two-datatables-where-rows-are-duplicate) — H H, Jul 28 '11 at 08:32
I foound that as well, it doesn work: Error 8 'System.Data.EnumerableRowCollection' does n — Jean Carlo, Jul 28 '11 at 08:36
Please add the code used and complete, formatted error to the question. — H H, Jul 28 '11 at 08:38

score 0 · Answer 1 · answered Jul 28 '11 at 08:32

0

Identify a unique_id field in the tables and then,

SELECT * FROM table1, table2 WHERE table1.unique_id = table2.unique_id

Once you're happy with what's returned you can build your insert statement based on the select.

answered Jul 28 '11 at 08:32

OTTA

1,071
7
8

No, this is wrong. its .NET code, not Sql Server Code. I have already both datatables on memory, I need to do it with .net code – Jean Carlo Jul 28 '11 at 08:35

score 0 · Accepted Answer · answered Jul 28 '11 at 08:40

0

I'm not that good with LINQ, but maybe you want to try this approach?

var result = from r in dt.AsEnumerable()
                     join c in dt2.AsEnumerable()
                     on r.Field<string>("col1") equals c.Field<string>("col1")
                     select r;

And then for all your columns in both tables.

I'm sure there is a ways comparing complete rows instead of fields/columns.

answered Jul 28 '11 at 08:40

timmkrause

3,367
4
32
59

the code compiles, but how can I access the columns of the resulting rows or how can I put that result into a datatable? – Jean Carlo Jul 28 '11 at 08:45
"result" is a set of DataRows so you can "foreach (DataRow row in result)". Or you can use "var" as well instead of "DataRow". – timmkrause Jul 28 '11 at 08:46
Oh. Found something new to me, too. You can use "result.CopyToDataTable()". – timmkrause Jul 28 '11 at 08:51

johanekdahl · Answer 3 · 2011-07-28T08:35:54.543

-1

Use System.Data.DataTable.Merge() , see this: http://msdn.microsoft.com/en-us/library/fk68ew7b.aspx

EDIT: Might not be a relevant answer, I misread "exist on both tables". Yes, what you are looking for is the intersection as others have speculated.

edited Jul 28 '11 at 08:35

answered Jul 28 '11 at 08:28

johanekdahl

249
3
10

.Merge doesnt do that, I only need the common rows of both datatables, Merge Joins ALL Rows – Jean Carlo Jul 28 '11 at 08:34

Combine 2 Datatables with the common rows only

3 Answers3