EF Core Querying

This section delves into the various ways you can query data using Entity Framework Core (EF Core). EF Core provides powerful and flexible mechanisms for retrieving data from your database, integrating seamlessly with LINQ and offering options for raw SQL execution.

Understanding Querying in EF Core

EF Core translates your queries into SQL statements that are executed against the database. This abstraction allows you to work with your domain models directly, without needing to write low-level SQL for most common scenarios. The primary ways to query data are through Language Integrated Query (LINQ) and by executing raw SQL queries.

LINQ Queries

LINQ is a powerful feature of .NET that allows you to write queries in C# or Visual Basic syntax. EF Core leverages LINQ to Objects providers to translate your LINQ queries into database-specific SQL.

A typical LINQ query involves:

Instantiating a DbContext.
Accessing a DbSet<TEntity> for the entity you want to query.
Using LINQ extension methods or query syntax to define your criteria.

Example:


                    using System.Collections.Generic;
                    using Microsoft.EntityFrameworkCore;

                    // Assume you have a DbContext named AppDbContext and a DbSet Products

                    public class ProductService
                    {
                        private readonly AppDbContext _context;

                        public ProductService(AppDbContext context)
                        {
                            _context = context;
                        }

                        public IEnumerable<Product> GetProductsByCategory(string categoryName)
                        {
                            var products = from p in _context.Products
                                               where p.Category == categoryName
                                               select p;
                            return products.ToList();
                        }

                        public Product FindProductById(int productId)
                        {
                            return _context.Products.Find(productId);
                        }
                    }

Raw SQL Queries

In scenarios where LINQ might not be expressive enough or for performance optimizations, EF Core allows you to execute raw SQL queries directly against the database.

You can execute:

Queries that return entities.
Queries that return primitive types or anonymous types.
SQL commands for modification (INSERT, UPDATE, DELETE).

Example:


                    public IEnumerable<Product> GetProductsUsingRawSql(string categoryName)
                    {
                        var products = _context.Products
                            .FromSqlRaw("SELECT * FROM Products WHERE Category = {0}", categoryName);
                        return products.ToList();
                    }

                    public int UpdateProductPrice(int productId, decimal newPrice)
                    {
                        return _context.Database.ExecuteSqlRaw("UPDATE Products SET Price = {0} WHERE Id = {1}", newPrice, productId);
                    }

Key Querying Concepts

As you explore EF Core querying, you will encounter several important concepts:

Projection: Selecting specific columns or transforming data into different types.
Filtering: Applying conditions to narrow down the results (e.g., using Where).
Sorting: Ordering the results (e.g., using OrderBy, OrderByDescending).
Grouping: Aggregating data based on certain criteria (e.g., using GroupBy).
Joining: Combining data from multiple related entities (e.g., using Join or navigation properties).
Query Tracking: How EF Core tracks changes to entities retrieved by a query.
Compiled Queries: Pre-compiling queries for potential performance gains.

Continue to the next sections to learn more about each of these topics.