Linq DataTable Having Query

Question

I am using the Microsoft.ACE.OLEDB Driver to query an excel file. The results are then passed to a DataTAble. However, The T-SQL query that I would like to use is not supported by this driver.

with abc as
(
SELECT Credit, Debit,[Reference 2] As [Job Code]
from xlSheet
WHERE ([Reference 2] LIKE '%JOB%') OR ([Reference 2] LIKE '%CRN%')
union all
SELECT Credit, Debit,[Reference] As [Job Code]
from xlSheet
WHERE ([Reference] LIKE '%JOB%') OR ([Reference] LIKE '%CRN%')
)
SELECT sum(Credit) as  Credit, sum(debit) as Debit,ABS(ROUND(SUM(Debit - Credit),2)) as Total , [Job Code],
            case when ROUND(SUM(Debit - Credit),2) < 0
                then 'JOB'
                else 'JOBR'
            end as 'Trans Code' 
from abc
group by [Job Code]
HAVING ROUND(SUM(debit - credit),2)  <> 0

So, I have broken this into two queries Namely:

SELECT Credit, Debit,[Reference 2] As [Job Code]
    from xlSheet
    WHERE ([Reference 2] LIKE '%JOB%') OR ([Reference 2] LIKE '%CRN%')
    union all
    SELECT Credit, Debit,[Reference] As [Job Code]
    from xlSheet
    WHERE ([Reference] LIKE '%JOB%') OR ([Reference] LIKE '%CRN%')

And a second being :

SELECT sum(Credit) as  Credit, sum(debit) as Debit,ABS(ROUND(SUM(Debit - Credit),2)) as Total , [Job Code], 
            case when ROUND(SUM(Debit - Credit),2) < 0 
                then 'JOB' 
                else 'JOBR' 
            end as 'Trans Code'  
from abc 
group by [Job Code] 
HAVING ROUND(SUM(debit - credit),2)  <> 0 

Now I know that one can perform basic select queries on a DataTable, but nothing as complex as this. I have heard about LINQ, and I am sure this could be done via this. But being not familiar with LINQ I need some help in this regard. Failing this, the only other way I see would be to write the results back to a secondary excel file, and re-read the file with the secondary query – but this would be have a huge performance drawback.

Answer 1

We have free chapters of LINQ in Action introducing LINQ and the bonus chapter 14 discussing LINQ to DataSets (which you would use for DataTables) if you need something to get started. You can read them at http://www.manning.com/marguerie/.

Answer 2

So I have solved my query by using Linq to DataTable as Follows :

        var result = from b in dsXLData.Tables[0].AsEnumerable()
                     group b by b.Field<string>("cJobCode")  into grp
                     where grp.Sum(e => Math.Round(e.Field<Double>("debit"),2) - Math.Round(e.Field<Double>("credit"),2)) != 0
                     select new
                     {
                         cJobCode = grp.Key,
                         Credit = Math.Round(grp.Sum(x => x.Field<Double>("credit")), 2),
                         Debit = Math.Round(grp.Sum(x => x.Field<Double>("debit")), 2),
                         Amount_Incl = Math.Round(Math.Abs(grp.Sum(x => x.Field<Double>("debit")) - grp.Sum(x => x.Field<Double>("credit"))), 2),
                         Trans_Code =
                                (
                                    Math.Round(grp.Sum(x => x.Field<Double>("debit")) - grp.Sum(x => x.Field<Double>("credit")), 2) < 0 ? "JOB" : "JOBR"
                                )

                     };