MSDN Documentation

Querying Data with .NET

This section delves into the various ways you can query data using the .NET framework. Effective data querying is crucial for retrieving, filtering, and manipulating information from diverse data sources.

LINQ (Language Integrated Query)

Language Integrated Query (LINQ) is a powerful feature of .NET that allows you to write queries directly in C# or Visual Basic code. It provides a consistent syntax for querying different data sources like collections, databases, XML, and more.

LINQ to Objects

LINQ to Objects enables querying in-memory collections such as arrays, lists, and dictionaries. It simplifies complex filtering, sorting, and grouping operations.

var numbers = new List { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 };

var evenNumbers = from num in numbers
                  where num % 2 == 0
                  orderby num
                  select num;

foreach (var num in evenNumbers)
{
    Console.WriteLine(num);
}
            

LINQ to SQL

LINQ to SQL provides a runtime environment that supports the translation of queries expressed in LINQ into Transact-SQL queries that are sent to SQL Server.

// Assuming a DataContext named 'db' connected to a SQL Server database
var londonCustomers = from cust in db.Customers
                      where cust.City == "London"
                      select cust;

foreach (var customer in londonCustomers)
{
    Console.WriteLine($"{customer.CustomerID}: {customer.CompanyName}");
}
            

LINQ to XML

LINQ to XML is a set of classes that represent XML documents in memory, allowing you to query XML data using LINQ.

XDocument doc = XDocument.Load("books.xml");

var bookTitles = from book in doc.Descendants("book")
                 where (int)book.Element("year") > 2000
                 select book.Element("title").Value;

foreach (var title in bookTitles)
{
    Console.WriteLine(title);
}
            

Entity Framework Core (EF Core)

Entity Framework Core is a modern, cross-platform, extensible data access technology for .NET. It's an Object-Relational Mapper (ORM) that allows you to query and save data to and from a database using strongly typed objects.

Basic Querying with EF Core

using (var context = new MyDbContext())
{
    // Get all products with a price greater than 50
    var expensiveProducts = context.Products
                                   .Where(p => p.Price > 50)
                                   .OrderByDescending(p => p.Price)
                                   .ToList();

    foreach (var product in expensiveProducts)
    {
        Console.WriteLine($"{product.Name} - ${product.Price}");
    }
}
            

ADO.NET

While LINQ and EF Core offer higher-level abstractions, ADO.NET remains a fundamental technology for accessing data. It provides a set of classes for connecting to data sources, executing commands, and retrieving results.

Using SqlCommand

string connectionString = "Your_Connection_String";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    string sql = "SELECT CustomerID, CompanyName FROM Customers WHERE City = @City";
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        command.Parameters.AddWithValue("@City", "London");
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine($"{reader["CustomerID"]} - {reader["CompanyName"]}");
            }
        }
    }
}
            

Conclusion

Understanding these querying mechanisms in .NET is essential for building efficient and responsive data-driven applications. Choose the right tool based on your project's requirements, complexity, and desired level of abstraction.