如何基于strike列合并两个DataTables或List<OptionFields>集合

• 现有代码仅返回table2数据的核心原因：左连接操作后你直接select r，其中r仅代表右表（table2）的匹配行，完全没有用到左表（table1）的字段数据
• 关联逻辑缺陷：仅用strike作为关联键可能存在匹配误差，建议同时关联symbol、expiry、timestamp四个公共字段，避免不同标的、到期日、时间戳的同行权价数据错误匹配

第一步：定义合并结果类 #

因为原有OptionFields只能存储单类期权的数据，需要先新建合并类存储两边的指标：

public class MergedOptionFields
{
    // 公共唯一字段
    public string symbol { get; set; }
    public string expiry { get; set; }
    public decimal strike { get; set; }
    public string timestamp { get; set; }

    // 看涨期权（table1，option_type=C）的专属字段
    public decimal call_option_mid { get; set; }
    public int call_option_trade_count { get; set; }
    public decimal call_option_prev_day_close { get; set; }
    public decimal call_iv { get; set; }
    public int call_open_interest { get; set; }
    public int call_option_volume { get; set; }
    public decimal call_delta { get; set; }
    public decimal call_vega { get; set; }
    public decimal call_theta { get; set; }
    public decimal call_rho { get; set; }

    // 看跌期权（table2，option_type=P）的专属字段
    public decimal? put_option_mid { get; set; }
    public int? put_option_trade_count { get; set; }
    public decimal? put_option_prev_day_close { get; set; }
    public decimal? put_iv { get; set; }
    public int? put_open_interest { get; set; }
    public int? put_option_volume { get; set; }
    public decimal? put_delta { get; set; }
    public decimal? put_vega { get; set; }
    public decimal? put_theta { get; set; }
    public decimal? put_rho { get; set; }
}

看跌字段设为可空类型是为了兼容左连接中无匹配数据的场景，如果两个表行权价完全一一对应可以不用可空。

第二步：修改LINQ查询 #

方案1：内连接（两个表strike完全匹配时用，性能更高） #

var result = from dataRows1 in table1
             join dataRows2 in table2 
             on new { dataRows1.strike, dataRows1.symbol, dataRows1.expiry, dataRows1.timestamp } 
             equals new { dataRows2.strike, dataRows2.symbol, dataRows2.expiry, dataRows2.timestamp }
             select new MergedOptionFields
             {
                 symbol = dataRows1.symbol,
                 expiry = dataRows1.expiry,
                 strike = dataRows1.strike,
                 timestamp = dataRows1.timestamp,
                 // 赋值看涨字段
                 call_option_mid = dataRows1.option_mid,
                 call_option_trade_count = dataRows1.option_trade_count,
                 call_option_prev_day_close = dataRows1.option_prev_day_close,
                 call_iv = dataRows1.iv,
                 call_open_interest = dataRows1.open_interest,
                 call_option_volume = dataRows1.option_volume,
                 call_delta = dataRows1.delta,
                 call_vega = dataRows1.vega,
                 call_theta = dataRows1.theta,
                 call_rho = dataRows1.rho,
                 // 赋值看跌字段
                 put_option_mid = dataRows2.option_mid,
                 put_option_trade_count = dataRows2.option_trade_count,
                 put_option_prev_day_close = dataRows2.option_prev_day_close,
                 put_iv = dataRows2.iv,
                 put_open_interest = dataRows2.open_interest,
                 put_option_volume = dataRows2.option_volume,
                 put_delta = dataRows2.delta,
                 put_vega = dataRows2.vega,
                 put_theta = dataRows2.theta,
                 put_rho = dataRows2.rho
             };

方案2：左连接（保留table1所有行，无匹配table2数据时看跌字段为null） #

var result = from dataRows1 in table1
             join dataRows2 in table2
             on new { dataRows1.strike, dataRows1.symbol, dataRows1.expiry, dataRows1.timestamp }
             equals new { dataRows2.strike, dataRows2.symbol, dataRows2.expiry, dataRows2.timestamp }
             into lj
             from r in lj.DefaultIfEmpty()
             select new MergedOptionFields
             {
                 symbol = dataRows1.symbol,
                 expiry = dataRows1.expiry,
                 strike = dataRows1.strike,
                 timestamp = dataRows1.timestamp,
                 call_option_mid = dataRows1.option_mid,
                 call_option_trade_count = dataRows1.option_trade_count,
                 call_option_prev_day_close = dataRows1.option_prev_day_close,
                 call_iv = dataRows1.iv,
                 call_open_interest = dataRows1.open_interest,
                 call_option_volume = dataRows1.option_volume,
                 call_delta = dataRows1.delta,
                 call_vega = dataRows1.vega,
                 call_theta = dataRows1.theta,
                 call_rho = dataRows1.rho,
                 put_option_mid = r?.option_mid,
                 put_option_trade_count = r?.option_trade_count,
                 put_option_prev_day_close = r?.option_prev_day_close,
                 put_iv = r?.iv,
                 put_open_interest = r?.open_interest,
                 put_option_volume = r?.option_volume,
                 put_delta = r?.delta,
                 put_vega = r?.vega,
                 put_theta = r?.theta,
                 put_rho = r?.rho
             };

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