Entity SQL (ESQL)

Entity SQL (ESQL) is a portable, SQL-like query language for querying data represented by an Entity Data Model (EDM). It provides a powerful way to interact with your conceptual model without directly referencing the underlying storage schema. ESQL offers capabilities beyond standard LINQ to Entities, especially for complex queries, dynamic queries, and scenarios where direct mapping to SQL is desired.

Key Concepts of Entity SQL

Entity SQL shares many similarities with Transact-SQL but is designed to work with entities and their relationships defined in the EDM.

Syntax and Structure

ESQL queries have a structure similar to SQL:

SELECT clause: Specifies the properties or computed values to retrieve.
FROM clause: Specifies the entity sets or views to query.
WHERE clause: Filters the results based on specified conditions.
ORDER BY clause: Sorts the results.
GROUP BY and HAVING clauses: For aggregation.

Working with Entities and Relationships

ESQL allows you to directly reference entity types, properties, and relationships defined in your EDM. You can navigate relationships using dot notation.

Built-in Functions

ESQL includes a rich set of built-in functions for string manipulation, date/time operations, mathematical calculations, and more, similar to SQL Server functions.

Example Usage

Here's a basic example of an ESQL query to retrieve all products with a price greater than 50:


SELECT VALUE p FROM AdventureWorksEntities.Products AS p WHERE p.ListPrice > 50

In this example:

AdventureWorksEntities represents your ObjectContext or DbContext.
Products is an entity set in your EDM.
p is an alias for the Products entity set.
ListPrice is a property of the Product entity.

Navigating Relationships

Consider an EDM with `Customers` and `Orders` entities, where `Customer` has a navigation property `Orders`.

                
SELECT c.CustomerId, c.FirstName, o.OrderId, o.OrderDate
FROM AdventureWorksEntities.Customers AS c
JOIN c.Orders AS o ON true
WHERE c.CustomerId = @customerId

This query retrieves customer details and their associated order information for a specific customer.

Entity SQL vs. LINQ to Entities

While both ESQL and LINQ to Entities query the Entity Framework, they have different strengths:

LINQ to Entities: Uses C# or VB.NET syntax, strongly typed, integrated with the .NET language, generally preferred for most development tasks.
Entity SQL: String-based, offers more dynamic query capabilities, can be easier for complex joins or when the query structure is determined at runtime, closer to SQL syntax.

When to use Entity SQL:

Dynamic query generation where the query structure is not known at compile time.
Complex queries that are difficult or verbose to express in LINQ.
When migrating from existing SQL-based data access logic.
Leveraging specific ESQL functions not directly exposed in LINQ providers.

Executing Entity SQL

You can execute Entity SQL queries using the ObjectContext.CreateQuery<T> method or the DbContext.Database.SqlQuery<T> method.

Using ObjectContext

using (var context = new AdventureWorksEntities())
{
    string sqlQuery = "SELECT VALUE p FROM AdventureWorksEntities.Products AS p WHERE p.ListPrice > @price";
    var priceParam = new ObjectParameter("price", 50M); // Assuming ListPrice is decimal

    var products = context.CreateQuery<Product>(sqlQuery, priceParam).ToList();

    foreach (var product in products)
    {
        Console.WriteLine(product.Name);
    }
}

Using DbContext (EF Core 5.0 and later)

For EF Core, you primarily use LINQ. However, for specific scenarios or raw SQL, you can use FromSqlRaw or ExecuteSqlRaw. Entity SQL itself is more of an EF6 concept for direct execution. If you're using EF Core and need something ESQL-like, you'd typically construct a LINQ query that generates the desired SQL.

Tip: When constructing ESQL queries dynamically, be mindful of SQL injection. Always use parameters to pass values into your queries.

Advanced Features

Pivoting and Unpivoting

ESQL provides syntax for pivoting and unpivoting data, allowing you to transform rows into columns and vice versa, which can be more complex in standard SQL.

Complex Type Projections

You can project complex types, including anonymous types, directly within your ESQL queries.

EntityClient Provider

The EntityClient provider allows you to execute ESQL queries against a conceptual model. It abstracts the underlying data source, enabling portability.

Conclusion

Entity SQL is a robust query language that complements LINQ to Entities, offering greater flexibility and control for certain data access scenarios within the Entity Framework. Understanding its syntax and capabilities can significantly enhance your ability to interact with your data model.

MSDN Documentation

Entity Framework

Related Topics

Entity SQL (ESQL)

Key Concepts of Entity SQL

Syntax and Structure

Working with Entities and Relationships

Built-in Functions

Example Usage

Navigating Relationships

Entity SQL vs. LINQ to Entities

Executing Entity SQL

Using ObjectContext

Using DbContext (EF Core 5.0 and later)

Advanced Features

Pivoting and Unpivoting

Complex Type Projections

EntityClient Provider

Conclusion