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

Answer 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
             };