Working with SQL Views in Entity Framework Core: Evolution from Query Types to Keyless Entity Types

Technical Background and Evolution

Throughout the development of Entity Framework Core, support for SQL views has undergone significant technical evolution. Early versions like EF Core 1.x lacked native view support, requiring developers to implement workarounds. With the release of EF Core 2.1, Microsoft introduced Query Types as a key feature specifically designed for handling read-only data sources, including database views, stored procedure results, and raw SQL query results. Query Types were conceived as lightweight entity types that do not participate in change tracking and do not support insert, update, or delete operations, but can seamlessly integrate with LINQ queries.

In EF Core 3.0, Microsoft further refined this concept by renaming Query Types to Keyless Entity Types and continued to enhance their functionality in subsequent versions. This renaming better reflects the essential characteristics of these entity types: they lack defined primary keys and therefore cannot be managed as standard entities for database tables. Keyless Entity Types received more comprehensive support in EF Core 5.0, including the ability to configure them via the [Keyless] data annotation.

Core Concepts Explained

Keyless Entity Types are special entity types in EF Core used to represent read-only data structures. Compared to regular entity types, they possess the following key characteristics:

• No defined primary key, thus cannot be uniquely identified by a key
• Do not participate in DbContext change tracking mechanisms
• Do not support data modification operations like insert, update, and delete
• Cannot be automatically discovered by convention and require explicit configuration
• Have specific limitations regarding navigation properties, such as not being able to serve as the principal end of a relationship

These characteristics make Keyless Entity Types particularly suitable for mapping database views, stored procedure result sets, or any read-only data sources that do not require persistent modifications.

Practical Case: Blog System View Implementation

The following demonstrates a complete workflow for implementing SQL views in EF Core using a concrete blog system case. Assume we have a blog system with two entities: Blog and BlogImage:

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
    public BlogImage BlogImage { get; set; }
}

public class BlogImage
{
    public int BlogImageId { get; set; }
    public byte[] Image { get; set; }
    public string Caption { get; set; }
    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

Now we need to create a view named ImageView that returns each blog's URL and corresponding image data. First, create the corresponding view in the database:

CREATE VIEW ImageView AS
SELECT 
    b.Url,
    bi.Image
FROM Blogs b
JOIN BlogImages bi ON b.BlogId = bi.BlogId

View Model Definition and Configuration

Next, create a corresponding view model class to represent the view's structure. This class should include all columns returned by the view and typically has read-only properties to prevent accidental modifications:

public class ImageViewModel
{
    public string Url { get; private set; }
    public byte[] Image { get; private set; }
}

In the DbContext, add a corresponding DbSet property for this view model. In EF Core 2.1, use the DbQuery<T> type; in EF Core 3.0 and later, use DbSet<T> but require special configuration:

public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<BlogImage> BlogImages { get; set; }
    public DbSet<ImageViewModel> ImageViews { get; set; }
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<ImageViewModel>(entity =>
        {
            entity.HasNoKey();
            entity.ToView("ImageView");
            
            // Explicitly configure mapping if column names differ from property names
            entity.Property(v => v.Url).HasColumnName("Url");
            entity.Property(v => v.Image).HasColumnName("Image");
        });
    }
}

Query Execution and Usage

Once configured, you can query view data just like regular entities. EF Core will translate LINQ queries into SQL queries against the view:

// Retrieve all view data
var allImages = context.ImageViews.ToList();

// Use LINQ for filtering
var filteredImages = context.ImageViews
    .Where(v => v.Url.Contains("example"))
    .ToList();

// Join queries with other entities
var blogDetails = context.Blogs
    .Join(context.ImageViews,
        b => b.Url,
        v => v.Url,
        (b, v) => new { Blog = b, Image = v.Image })
    .ToList();

Advanced Configuration and Best Practices

In real-world projects, view usage may involve more complex scenarios. Here are some advanced configuration techniques and best practices:

1. View Name Mapping: If the view name differs from the entity class name, explicitly specify it in the OnModelCreating method:

modelBuilder.Entity<ImageViewModel>()
    .HasNoKey()
    .ToView("dbo.ImageView");

2. Column Name Mapping: When view column names differ from entity property names, configure property mapping:

modelBuilder.Entity<ImageViewModel>(entity =>
{
    entity.HasNoKey();
    entity.ToView("ImageView");
    entity.Property(v => v.Url).HasColumnName("BlogUrl");
    entity.Property(v => v.Image).HasColumnName("BlogImageData");
});

3. Using Data Annotations: In EF Core 5.0 and later, you can use the [Keyless] data annotation to simplify configuration:

[Keyless]
public class ImageViewModel
{
    public string Url { get; set; }
    public byte[] Image { get; set; }
}

4. Performance Considerations: Since views are typically read-only, EF Core does not track changes for them, which can reduce memory usage. However, for complex views, evaluate query performance and consider using raw SQL queries when necessary:

var images = context.ImageViews
    .FromSqlRaw("SELECT * FROM ImageView WHERE Url LIKE {0}", "%example%")
    .ToList();

Version Compatibility Considerations

Different versions of EF Core have varying levels of view support, so pay attention to version compatibility during development:

• EF Core 2.1: Introduced Query Types concept, using DbQuery<T>
• EF Core 3.0: Renamed Query Types to Keyless Entity Types, using DbSet<T> with HasNoKey() configuration
• EF Core 5.0: Introduced [Keyless] data annotation for more concise configuration

For projects needing to support multiple EF Core versions, consider using conditional compilation or version detection to ensure code compatibility.

Common Issues and Solutions

Issue 1: Poor View Query Performance
Solution: Consider optimizing view definitions at the database level or splitting complex views into multiple simpler ones. For particularly complex queries, consider using stored procedures or table-valued functions.

Issue 2: Need to Pass Parameters to Views
Solution: EF Core does not natively support parameterized views. Consider using table-valued functions as alternatives or passing parameters via raw SQL queries.

Issue 3: Views Contain Computed Columns
Solution: Ensure property types in the view model match the data types of computed columns. For complex computation logic, add appropriate property conversion logic in the view model.

Conclusion and Future Outlook

Entity Framework Core's support for SQL views has evolved from limited early functionality to a mature solution today. Through Keyless Entity Types, developers can use database views in a type-safe manner while enjoying the convenience of LINQ queries. As EF Core continues to develop, future versions are expected to further optimize view-related features, including better performance, more flexible configuration options, and more comprehensive tooling support.

In practical development, judicious use of SQL views can significantly enhance the abstraction level of the data access layer and improve code maintainability. However, it's also important to be aware of view limitations, particularly regarding performance considerations. For complex business scenarios, it may be necessary to combine various technical approaches such as views, stored procedures, and raw SQL queries to achieve the optimal balance between performance and maintainability.