LMES Extended Attribute Database Retrieval Guide

I. Configuration Instructions

1. Database Configuration

You must register the JSON_EXTRACT function in DbContext:

public class YourDbContext : AbpDbContext<YourDbContext>
{
    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);

        // Register JSON query function
        builder.HasDbFunction(() =>
            CMSDbFunctionsExtensions.JSON_EXTRACT(default, default));

        // Configure object extensions
        builder.TryConfigureObjectExtensions<YourDbContext>();
    }
}

2. Query Functionality

Supports two matching modes:

var extraProps = new ExtraPropertyDictionary
{
    { "name", "John" },     // Fuzzy match
    { "@status", "1" }      // Exact match (prefix with @)
};

var query = _repository.GetQueryable().JsonMatch(extraProps);

3. Frontend Parameter Format

Frontend can pass extended attributes via URL parameters in the following format:

url?Key=Value&ExtraProperties%5BKey%5D=Value

Explanation:

• %5B and %5D are URL encoded square brackets [ and ]
• Use ExtraProperties[Key] to specify dictionary Key
• Use Key=Value to assign value

4. Data Format Requirements

The content stored in the ExtraProperties field in the database must be a valid JSON object format, as follows:

{
    "key1": "value1",
    "key2": "value2"
}

Notes:

• Must be wrapped in curly braces {}
• Must be valid key-value pair format
• Does not support storing plain strings or arrays directly
• Key names must use double quotes
• Values support JSON basic types like strings, numbers, booleans

II. Performance Optimization Points

1. Query Optimization

public async Task<List<Order>> GetListAsync(OrderListInput input)
{
    // 1. Basic query
    var query = _repository.GetQueryable()
        .Where(x => x.Status == OrderStatus.Active);

    // 2. Necessary Includes
    query = query.Include(x => x.RequiredDetails);

    // 3. JSON query last
    if (input.ExtraProperties?.Any() == true)
    {
        query = query.JsonMatch(input.ExtraProperties);
    }

    // 4. Pagination
    return await query
        .Skip(input.SkipCount)
        .Take(input.MaxResultCount)
        .ToListAsync();
}

2. Database Optimization

-- Create JSON index
CREATE INDEX idx_order_extra_status
ON Orders ((JSON_EXTRACT(ExtraProperties, '$.status')));

-- Limit JSON field size
ALTER TABLE Orders
ALTER COLUMN ExtraProperties NVARCHAR(1000);

III. Usage Restrictions

1. Not Recommended Scenarios

• Full table scan on large tables
• Frequent updates to JSON fields
• Complex statistical queries
• High frequency query fields

2. Recommended Scenarios

• Dynamic extended attributes
• Non-core query fields
• Controllable data volume scenarios
• Low frequency query scenarios

IV. Monitoring Suggestions

1. Performance Monitoring:

• Record JSON queries taking >1s
• Monitor ExtraProperties field size
• Track index usage

2. Restriction Measures:

• Set query timeouts
• Limit JSON field size
• Control number of query conditions

V. Best Practices Summary

1. Configuration Standards:

• Must register JSON_EXTRACT function
• Establish necessary JSON indices
• Limit JSON field size

2. Query Standards:

• Prioritize filtering by normal fields
• Place JsonMatch at the end of query
• Must use pagination
• Use Include reasonably

3. Development Suggestions:

• Use normal columns for core fields
• Use ExtraProperties for dynamic extension fields
• Keep JSON data structure simple
• Avoid deep nested queries

By reasonably using ExtraProperties, you can ensure query performance while maintaining system flexibility.