Introduction to Linq To Datatables

LINQ to objects will work on any object that implements IEnumerable (the same interface that allows an object to be used in a For Each loop). Datasets are objects, but they don’t implement IEnumerable by default, so you’ll need to first add a reference to a library that adds some extension methods that wrap DataTable and allow it to be enumerated.

Just go to Project > References > Add > .NET > System.Data.DataSetExtensions

DataSetExtensions

In LINQ, you always start the query with the From statement

From a In b    

This gives you access to each item that is part of the B collection in the same way that a for loop does

For Each a In b

For the rest of the for loop (or linq query), you can just refer to a and it’s automatically populated as it iterates over the collection.

If you want to loop over a DataTable, you need to create an enumerable table by calling DataTable.AsEnumerable() in order to convert the collection of rows into one that is, well, enumerable. When you loop over a DataTable, you’ll be holding a row object, so you can generally query it in the same way that you otherwise would

If I have a DataRow object, I can get the values like this:

'longhand
Dim name = myRow.Field(Of String)("Name")

'shorthand
Dim name = myRow("Name")

Let’s say that we have the following SQL Query that we’d like to do in .NET with Datatables instead:

SELECT p.ID, p.Name, e.SSN
FROM People p
JOIN ExtendedInfo e 
  ON p.ID = e.PersonID

LINQ (especially using it’s Query Syntax) is intended to function a lot like SQL. So here’s what it would look like in .NET:

Dim query = From person In people.AsEnumerable
    Join extra In extendedInfo.AsEnumerable()
      On person("ID") Equals extra("PersonID")
    Select New With {
            .ID = person("ID"),
            .Name = person("Name"),
            .SSN = extra("SSN")
    }

The syntax on the bottom just creates a new anonymous object and then selects it. We can Select any object available from the query (i.e. person) or we can create our own on the fly and select that

If you’re unfamiliar with anonymous objects, the syntax may look unfamiliar, but it’s a pretty basic concept. One rarely used feature you might not know about is that if we want to initialize a Person class (which has a Name Property), we can set the name property directly from the initializer using a With {} statement like this:

Dim typedPerson = New Person With {
    .Name = "Angelic"
}

If we don’t have a Person class to stuff our data in, or we just want to do it on the fly, we can do the same thing by omitting the class name and then just passing in any properties we want our new class to have.

Note: The compiler will actually create a class for you behind the scenes that has all the properties you’ve specified, but we don’t have a name for it - thus it’s anonymous.

'Anonymous Person
Dim anonPerson = New With {
    .Name = "Angelic"
}

Now we have a query that, once evaluated, will return the joined tables into an enumerable collection of anonymous objects. If we want to convert that back into a new Datatable it’s as simple as calling CopyToDataTable on the query result:

Dim newTable = query.CopyToDataTable()

However, when you do this right now, you’ll get the following error:

CopyToDataTable is not a member of System.Collections.Generic.IEnumerable(Of <anonymous type>)

Okay, I lied - It would be simple if the enumerable was of type DataRow. But not to fear, as written by this MSDN article on How to Implement CopyToDataTable Where the Generic Type T Is Not a DataRow, you just need to include some extra extension methods that don’t ship with DataSetExtensions .

Just add this file to your project anywhere.

Now the code should compile and work fine.

You can test the result by running:

For Each row In newTable.AsEnumerable()
Console.WriteLine("ID: "   & row("ID") & " / " &
                  "Name: " & row("Name") & " / " &
                  "SSN: "  & row("SSN"))
Next

If you’d like to run this code immediately without even opening up Visual Studio, you can see a working demo in dotNetFiddle here.

For further reading, there is a TON of great information on MSDN under

LINQ to DataSet - Getting Started | Programming Guide

18 comments:

  1. Nice and informative article.
    I am preparing from mettl.com forcoding. It really helps.

    ReplyDelete
  2. IEnumerable by default, so you’ll need to first add a reference to a library that adds some extension methods that wrap DataTable and allow it to be enumerated. Dolby Atmos Crack

    ReplyDelete
  3. Our security and bodyguard services are mainly characterized by maximum reliability, efficiency, and careful and precise preparations. residential securityThey are highly trained to provide security and safety for all those who have legitimate concerns about real or even imagined threats. We will take care of all your security needs tactfully and professionally. At UK Close Protection Services, we also maintain the highest level of discretion. This is because our officers have several years of experience in handling emergencies.

    ReplyDelete
  4. hi, i am really impressed by it .You’ve done an incredible job. Keep it up if you want to progress in the future. Keep it up! TemplateToaster Serial Key

    ReplyDelete
  5. o OR Operator
    - Use OR operator to search either of the given words. Example: cricket or sports which returns files which has cricket or sports word in it.
    https://buyinstasingapore.com/

    ReplyDelete
  6. excellent article.visit my profile for more information. Click here

    ReplyDelete
  7. Awesome blog!! Thank for providing excellent information. if you have any issues with QuickBooks automatic backup not working, you can go through the detailed steps mentioned in this article.

    ReplyDelete
  8. ค่ายMEGA SlotAuto ที่คาสิโนออนไลน์ พนันออนไลน์ สร้างรายได้ พร้อมลุ้นเงินก้อนใหญ่จากการเล่น อัตราต่อรองไม่ได้มีมาบ่อยๆ อย่ารอช้า

    ReplyDelete
  9. Thank you sharing the information. Besides if you are a dentist, and you're constantly looking for ways to grow your practice. A digital marketing specialist can help you tap into the power of the internet and attract new patients to your door. Check Go Big LA to take their best internet marketing for dentists service. And watch a massive grow of your business form the first day.

    ReplyDelete
  10. This feature-rich library enhances efficiency and flexibility in data handling, Best Websites Gaming making it an invaluable resource for developers working with DataTables in C#.

    ReplyDelete
  11. The body uses carbohydrates as its main energy source, especially during vigorous exercise. Because of its high carbohydrate content, Rc King Mass gainer is an excellent source of quick food for workouts and for refueling glycogen reserves after exercise. This encourages anabolism, the process of gaining muscular mass, and helps stop the breakdown of muscles.

    ReplyDelete
  12. You have the entire world on your shoulders even if you enter the gym with the intention of succeeding. Your blood becomes a ball of fire when Gat nitraflex pre workout is infused into it.

    ReplyDelete
  13. I had been searching a long time for trustworthy local bathroom cleaning services near me until I came across Helperji. My bathroom is always left immaculate by their amazing team.

    ReplyDelete
  14. Probyto's Data Consolidation services enable enterprises to confidently drive strategic decision-making, unearth hidden insights, and improve their data management procedures.

    ReplyDelete
  15. The way Probyto AI deploys Machine Learning Models deployment intrigues me. Their commitment to customization guarantees that companies may use AI solutions that precisely match their aims and objectives. Positive indication for AI's future

    ReplyDelete