Reference DataTable columns with Linq

Question

I am trying to join two datatables using linq

var invoices420 = dt420_.AsEnumerable();
var invoices430 = dt430_.AsEnumerable();

            var query = from inv430 in invoices430
                        join inv420 in invoices420 on inv430.LinkDoc equals inv420.LinkDoc
                        orderby inv430.SID
                        select new
                {
                    LinkDoc = inv430.LinkDoc,
                    TotalIn = Math.Round(inv430.Credit, 2),
                    TotalOut = ((inv420 == null) ? 0 : Math.Round(inv420.Debit, 2))

                };

Joining does not seems to be a problem, but I am getting an error'System.Data.DataRow' does not contain a definition for 'LinkDoc' and no extension method 'LinkDoc' accepting a first argument of type 'System.Data.DataRow' could be found (are you missing a using directive or an assembly reference?).

What do I have to do to reference a column in DataTable for example inv430.LinkDoc without using inv430.Field("linkdoc")?

If I want to do a group by on result set I am thinking

var q2 = query
         .GroupBy(item => item.LinkDoc);

return q2.ToArray();

Problem is that in q2 I dont get all the columns (linkdoc, totalin, totalout).

Original data is

dt420_
Linkdoc   Credit
Invoice1  500
Invoice2  100
Invoice3  200

dt430_
LinkDoc   Debit
Invoice1  100
Invoice1  100
Invoice2  200

Result would be
LinkDoc    TotalIn(Credit)  TotalOut(Debit)
Invoice1   500              200
Invoice2   100              200
Invoice3   200              0

score 1 · Accepted Answer · edited Nov 11 '12 at 14:12

1

You need to replace all places you called directly to properties like

inv430.LinkDoc

to

inv430["LinkDoc"]

inv430 is a DataRow so you need to use the indexer that gets a string.

EDIT: Your join will bring wrong data (see my comment below). You need to use this code:

var group430 = from inv430 in invoices430
               group inv430 by inv430["LinkDoc"].ToString().Trim() into g
               select new
               {
                    LinkDoc = g.Key.ToString().Trim(),
                    TotalOut = g.Sum(inv => Math.Round((decimal)inv["Debit"], 2))
               };

var group420 = from inv420 in invoices420
               group inv420 by inv420["LinkDoc"].ToString().Trim() into g
               select new
               {
                    LinkDoc = g.Key.ToString().Trim(),
                    TotalIn = g.Sum(inv => Math.Round((decimal)inv["Credit"], 2))
               };

var result = from inv430 in group430
             join inv420 in group420 on inv430.LinkDoc equals inv420.LinkDoc into inv
             from inv420 in inv.DefaultIfEmpty()
             select new
             {
                 inv430.LinkDoc,
                 TotalOut = inv430.TotalOut,
                 TotalIn = inv420 != null ? inv420.TotalIn : 0
             };

edited Nov 11 '12 at 14:12

mko

6,638
12
67
118

answered Nov 11 '12 at 09:38

Amiram Korach

13,056
3
28
30

Ok, that makes sense. Is is possible to use group by when joining two datables? – mko Nov 11 '12 at 09:52
You can use group by as any ienumerable – Amiram Korach Nov 11 '12 at 10:10
but it has to be grouped in a separate operation? – mko Nov 11 '12 at 10:30
I don't think so. Add the group to your question. – Amiram Korach Nov 11 '12 at 10:33
What are the columns you want to group by? – Amiram Korach Nov 11 '12 at 10:38
I wand to group by LinkDoc from query, and I want to retreive all three columns in a result set. – mko Nov 11 '12 at 10:40
But if you group by LinkDoc, you have to use an aggregate function on the rest or get only the first one in the group. – Amiram Korach Nov 11 '12 at 10:44
It's hard to tell without understanding what you want from your query. Describe your data and what you want to get. – Amiram Korach Nov 11 '12 at 10:59
Data and query are very simple. From linq query you can see that I want to join two tables on linkdoc column, and as a result I would like to get unique linkdoc (group by), sum(totalin), sum(totalout). – mko Nov 11 '12 at 12:34
Now I understand. Your query will bring wrong data since you're joining two tables that are not really related. For example, Invoice1 will get credit of 1000 because it is doubled because it appears twice in dt430. You have to group by each table separately and then join the results. I'll add this to my answer. – Amiram Korach Nov 11 '12 at 12:51
you are right, but i think there should also be a statement before select new. Something like "into outer from inv420 in outer.DefaultIfEmpty()" in case there is no matching key in inv420. – mko Nov 11 '12 at 13:17
I tested the code and i believe only the last number is being summed with TotalOut = g.Sum(inv => Math.Round((decimal)inv["Debit"], 2)) instead all the numbers in invoices430 – mko Nov 11 '12 at 13:41
test only the first query. What do you get? – Amiram Korach Nov 11 '12 at 13:53
Sorry my mistake, I definitely need to use inv430["LinkDoc"].ToString().Trim() in order to avoid any trailing blanks and g.Key.ToString().Trim() for later use in join. – mko Nov 11 '12 at 13:57
When I want to sum all the values in results with foreach (var item in result) { total_URA += item.TotalIn; total_IRA += item.TotalOut; } I receive a total which is double the value. I am working on it to found a reason. – mko Nov 11 '12 at 14:00
actually i didn't figure out why do i and up with twice as big total – mko Nov 11 '12 at 19:06

Reference DataTable columns with Linq

1 Answers1