Get data from datatable with LINQ

Question

I am new in LINQ and I am trying to find solution for this logic;

I have datatable with 3 columns; Column1, Column2, Column3

I want LINQ for getting this SQL logic

SELECT Max(Column1), Column2 from myDataTable GroupBy Column2

Can I get that ???

Answer 1

DataTable dt = new DataTable();

        dt.AsEnumerable().Select(x => new
        {
            Column1 = x.Field<T>("Column1"),
            Column2 = x.Field<T>("Column2"),
            Column3 = x.Field<T>("Column3"),
        })
        .GroupBy(x => x.Column2)
        .Select(g => new
        {
            Column2  = g.Key,
            Max = g.Max(c=>c.Column1)
        });

---

Please Mark as Reply and Vote as Helpful if I helped.

Also please visit my blog http://msguy.net/

Answer 2

If I convert this code into VB it gave me this ...but does not work.

What else I notice, LINQ has "First" ...So, I need this ..

SELECT First(Column1), Column2 from myDataTable GroupBy Column2

Dim dt As New DataTable()

dt.AsEnumerable().[Select](Function(x) New With { _
Key .Column1 = x.Field(Of T)("Column1"), _
Key .Column2 = x.Field(Of T)("Column2"), _
Key .Column3 = x.Field(Of T)("Column3") _
}).GroupBy(Function(x) x.Column2).[Select](Function(g) New With { _
Key .Column2 = g.Key, _
Key .Max = g.Max(Function(c) c.Column1) _
})

Answer 3

Try this way

        var q = from d in dt.AsEnumerable()
                select new
                {
                    Column1 = d.Field<int>("Column1"),
                    Column2 = d.Field<int>("Column2"),
                    Column3 = d.Field<int>("Column3")
                };
        var q1 = from a in q

                 group a by a.Column2 into g
                 select new
                 {
                     Column2 = g.Key,
                     Max = g.Max(x => x.Column1)
                 };

---

Please Mark as Reply and Vote as Helpful if I helped.

Also please visit my blog http://msguy.net/

Answer 4

Hi Ilimax68;

The following Linq query that uses a DataTable object to achieve the results of the posted SQL statement is as follows: In the below code change the T in (Of T) to the correct DataType for the column you are working with.

' Visual Basic Code Snippet
Dim results = From mdt in myDataTable.AsEnumerable()
              Group By ID = mdt.Field(Of T)("Column2") Into Group
              Select Max = Group.Max(Function(C1) C1.Field(Of T)("Column1")), Column1

  

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 5

Thank you Fernando ...

Your query look good .. This is how I made it as final ..

Dim results = From mdt in myDataTable.AsEnumerable()
              Group By ID = mdt.Field(Of String)("Column2") Into Group
              Select First = Group.First(Function(mdt) mdt.Field(Of String)("Column1"))

After I made this I tried to add line  ..tblTemp = results.CopyToDataTable() .. but I can not add this "CopyToDataTable

I did not test yet results, but I can see this is not what I wanted .. I wanted to copy two columns from one datatable into new datatable ..without loop.If I use loop it takes more time ...

I tried this ..

tblTemp = myDataTable.DefaultView.ToTable(True,"Column2","Column1")

But .. there is problem .. I can not group by Column1 .. I have to group only by Column2

Any idea how I can solve my problem ...

Thank you very much for your replay

Answer 6

Hi Ilimax68;

In order to use CopyToDataTable from a Linq query the results of that query needs to be IEnumerable(Of DataRow). So as is this can NOT use CopyToDataTable. In order to do what you want you will need to implement a custom CopyToDataTable. Microsoft has created one that you can copy and past into your code. The following web page, How to: Implement CopyToDataTable<T> Where the Generic Type T Is Not a DataRow, has the code, note that you will need to use ObjectShredder<T> and CopyToDataTable<T> for the complete implementation.

  

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 7

Thank you very much Fernando for your replay.

What I notice in of all of that, I will have again LOOP in those fuctions. So, on the end I figured out there is no way for me to pull quick data from datatable without looping through records.

In that case, I can use  ... For Each r as DataRow in myDatatable.Rows

I was thinking I can find easy way for this logic

SELECT First(Column1), Column2 from myDataTable GroupBy Column2

.. and pull data quickly. But I see I will need to go back to my original LOOP through datatable.

However, if anybody have idea for faster way, please let me know.

Thanks to all for replies.