数据库交互设计中Editable与Queryable的含义及相关示例咨询

Hey there! Let's break down these two terms clearly with concrete examples—since they pop up across different layers (procedures, repositories, services) and it's easy to mix them up.

Core Definitions #

First, let's nail down the basics:

• Queryable: A component designed exclusively for reading data. It has no ability to create, update, or delete records—only fetch them. This follows the principle of least privilege and keeps read logic isolated.
• Editable: A component that handles write operations (create/update/delete) alongside optional read capabilities. It's responsible for modifying the state of your database.

Queryable Examples #

Let's look at how this applies to different components:

Queryable Repository #

A repository that only exposes methods to retrieve data, no write actions. This is great for separating read-only data access from modification logic.

// C# example of a Queryable User Repository
public interface IQueryableUserRepository
{
    // Only read operations here
    Task<User> GetByIdAsync(Guid userId);
    Task<IEnumerable<User>> GetAllActiveUsersAsync();
    Task<User> GetByEmailAsync(string email);
    // No Add/Update/Delete methods allowed!
}

You'd use this in parts of your app that only need to display user data (like a user profile view) without modifying it.

Queryable Procedure #

A database stored procedure that runs only SELECT statements—no INSERT, UPDATE, or DELETE. It's optimized for read-heavy tasks.

-- SQL example of a Queryable Stored Procedure
CREATE PROCEDURE GetTop10LoyalCustomers
AS
BEGIN
    SELECT TOP 10 Id, FullName, TotalPurchases
    FROM Customers
    WHERE IsActive = 1
    ORDER BY TotalPurchases DESC;
END

Queryable Service #

A business logic service that only handles read-related rules. It wraps queryable repositories to add business context (like filtering active users or sorting by purchase history).

public class UserQueryService
{
    private readonly IQueryableUserRepository _userRepo;

    public UserQueryService(IQueryableUserRepository userRepo)
    {
        _userRepo = userRepo;
    }

    // Business-focused read operation
    public async Task<IEnumerable<User>> GetUsersEligibleForDiscountAsync()
    {
        var activeUsers = await _userRepo.GetAllActiveUsersAsync();
        // Apply business rule: users with >5 purchases get discounts
        return activeUsers.Where(u => u.TotalPurchases > 5);
    }
}

Editable Examples #

Now let's cover components that handle data modification:

Editable Procedure #

A stored procedure that includes write operations. It's used to encapsulate database modification logic (like updating a user's email with timestamp tracking).

-- SQL example of an Editable Stored Procedure
CREATE PROCEDURE UpdateUserEmail
    @UserId INT,
    @NewEmail VARCHAR(255)
AS
BEGIN
    -- Update the user's email and set last modified timestamp
    UPDATE Users
    SET Email = @NewEmail, UpdatedAt = GETDATE()
    WHERE Id = @UserId;
END

Editable Repository #

A repository that includes both read and write operations. It often extends a queryable repository to reuse read logic while adding modification methods.

// C# example of an Editable User Repository
public interface IEditableUserRepository : IQueryableUserRepository
{
    // Write operations added
    Task AddAsync(User user);
    Task UpdateAsync(User user);
    Task DeleteAsync(Guid userId);
    Task SaveChangesAsync();
}

This is used in parts of your app that need to modify user data (like an admin dashboard or user settings page).

Editable Service #

A business logic service that handles write operations, including pre-write validation and business rules. It uses editable repositories to persist changes.

public class UserManagementService
{
    private readonly IEditableUserRepository _userRepo;

    public UserManagementService(IEditableUserRepository userRepo)
    {
        _userRepo = userRepo;
    }

    // Business-focused update operation with validation
    public async Task UpdateUserEmailAsync(Guid userId, string newEmail)
    {
        // Step 1: Validate user exists
        var existingUser = await _userRepo.GetByIdAsync(userId);
        if (existingUser == null) throw new ArgumentException("User not found");

        // Step 2: Business rule - ensure email isn't already taken
        var userWithDuplicateEmail = await _userRepo.GetByEmailAsync(newEmail);
        if (userWithDuplicateEmail != null && userWithDuplicateEmail.Id != userId)
            throw new InvalidOperationException("Email is already associated with another user");

        // Step 3: Update and persist changes
        existingUser.Email = newEmail;
        await _userRepo.UpdateAsync(existingUser);
        await _userRepo.SaveChangesAsync();
    }
}

Quick Rule of Thumb #

• Use Queryable when your component only needs to read data (safer, follows single responsibility).
• Use Editable when your component needs to modify data (can include reads, but focuses on write operations).

内容的提问来源于stack exchange，提问作者Aboody