Azure Functions C# SQL绑定中GET/POST请求表连接处理最佳实践

1. 用组合替代继承，重构实体类 #

别用继承Location的方式，C#是单继承，后续要关联Customer就麻烦了。直接在Booking类里加Location属性，用组合承载关联数据：

[DataContract]
public class Booking
{
    [JsonProperty("id")]
    public Guid Id { get; set; }

    [JsonProperty("date")]
    public DateTime Date { get; set; }

    [JsonProperty("customer_id")]
    public string? CustomerId { get; set; }

    [JsonProperty("location_id")]
    public string? LocationId { get; set; }

    [JsonProperty("deleted")]
    public bool Deleted { get; set; }

    // 新增关联的Location属性
    [JsonProperty("location")]
    public Location? Location { get; set; }

    public override string ToString()
    {
        return JsonConvert.SerializeObject(this);
    }
}

// Location实体类
public class Location
{
    [JsonProperty("id")]
    public Guid Id { get; set; }

    [JsonProperty("name")]
    public string? Name { get; set; }

    // 其他Location字段按需添加
}

2. 调整GET请求的SQL查询，避免字段冲突 #

SQL Bindings会自动把查询列映射到实体的嵌套属性，但要注意字段名不能冲突（比如Booking和Location都有Id），所以要给关联表的列加前缀，明确映射关系：

[Function("GetBooking")]
public static HttpResponseData Run(
    [HttpTrigger(AuthorizationLevel.Function, "get", Route = "booking/{id}")] HttpRequestData req,
    [SqlInput(
        commandText: @"SELECT 
                          Booking.Id AS Id,
                          Booking.Date AS Date,
                          Booking.CustomerId AS CustomerId,
                          Booking.LocationId AS LocationId,
                          Booking.Deleted AS Deleted,
                          Location.Id AS Location_Id,
                          Location.Name AS Location_Name
                        FROM [dbo].[Booking] Booking
                        LEFT JOIN [dbo].[Location] Location ON Booking.LocationId = Location.Id
                        WHERE Booking.Id = @id",
        commandType: System.Data.CommandType.Text,
        parameters: "@id={id}",
        connectionStringSetting: "SQL_CONNECTION_STRING"
    )]
    IEnumerable<Booking> result
)
{
    return result.Any() 
        ? Response.OK(req, result.First()) 
        : Response.NotFound<Booking>(req);
}

SQL Bindings会自动把Location_Id映射到Booking.Location.Id，Location_Name映射到Booking.Location.Name，嵌套属性的映射是默认支持的。

3. POST插入后直接返回关联数据，不用额外调用接口 #

修改BookingOutput类，添加SqlInput绑定来查询插入后的完整关联数据，这样插入后直接拿到带Location的Booking对象：

重构BookingOutput

public class BookingOutput
{
    // 用于插入Booking的输出绑定
    [SqlOutput("[dbo].[Booking]", "SQL_CONNECTION_STRING")]
    public BookingEntity? BookingEntity { get; set; }

    // 插入后查询完整关联数据的输入绑定
    [SqlInput(
        commandText: @"SELECT 
                          Booking.Id AS Id,
                          Booking.Date AS Date,
                          Booking.CustomerId AS CustomerId,
                          Booking.LocationId AS LocationId,
                          Booking.Deleted AS Deleted,
                          Location.Id AS Location_Id,
                          Location.Name AS Location_Name
                        FROM [dbo].[Booking] Booking
                        LEFT JOIN [dbo].[Location] Location ON Booking.LocationId = Location.Id
                        WHERE Booking.Id = @id",
        commandType: System.Data.CommandType.Text,
        parameters: "@id={BookingEntity.Id}",
        connectionStringSetting: "SQL_CONNECTION_STRING"
    )]
    public Booking? FullBooking { get; set; }

    public HttpResponseData? Response { get; set; }
}

调整CreateBooking函数返回逻辑

[Function("CreateBooking")]
public async Task<BookingOutput> Run(
    [HttpTrigger(AuthorizationLevel.Function, "post", Route = "bookings")] HttpRequestData req)
{
    Response response = new(_logger, "Creating Booking");

    string body = await new StreamReader(req.Body).ReadToEndAsync();
    BookingEntity? bookingEntity = JsonConvert.DeserializeObject<BookingEntity>(body);

    if (bookingEntity is null)
    {
        return new BookingOutput()
        {
            Response = response.BadRequest(req)
        };
    }

    bookingEntity.Id = Guid.NewGuid();

    // SQL Bindings会先执行插入，再执行SqlInput查询返回FullBooking
    return new BookingOutput()
    {
        BookingEntity = bookingEntity,
        Response = Response.OK(req, FullBooking)
    };
}

额外优化点 #

• 区分实体和DTO：可以专门创建BookingWithLocationDto作为API返回的传输对象，和数据库实体BookingEntity分开，避免职责混淆。
• 缓存静态数据：如果Location数据很少变动，用Redis或内存缓存起来，减少JOIN查询的数据库压力。
• 验证外键存在：创建Booking前，先检查LocationId对应的Location是否存在，避免无效数据插入。

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