SQL转LINQ遇集合操作类型不匹配错误，求解决方案

原始SQL查询 #

SELECT
    a.DocumentNumber, a.ItemNumber, 
    SUM(a.ItemAmount) AS ItemAmount 
FROM
    (SELECT
         SopNumbe AS DocumentNumber, ITEMNMBR AS ItemNumber, 
         XTNDPRCE AS ItemAmount
     FROM
         dbo.SOP30300 
     WHERE
         SOPNUMBE = '8403017              ' 
         AND XTNDPRCE <> 0
     UNION ALL
     SELECT 
         LAPTODNM AS DocumentNumber, LITMNMBR AS ItemNumber, 
         -1 * ta.APPTOAMT AS ItemAmount 
     FROM
         dbo.RM20201 a 
     LEFT JOIN
         dbo.TRM20201 ta ON a.APFRDCNM = ta.LAPFRDNM 
                         AND a.APTODCNM = ta.LAPTODNM 
     WHERE
         ta.LAPTODNM = '8403017              ') a
GROUP BY
    a.DocumentNumber, a.ItemNumber 
HAVING
    SUM(a.ItemAmount) > 0

尝试的LINQ代码 #

var result = (from sop in _librgContext.Sop30300
              where sop.Sopnumbe == "8403017" && sop.Xtndprce != 0
              select new InvoiceSummary
              {
                  DocumentNumber = sop.Sopnumbe,
                  ItemNumber = sop.Itemnmbr,
                  ItemAmount = sop.Xtndprce
              })
  .Union(
  from rm in _librgContext.Rm20201
  join ta in _librgContext.TRM20201 on new { Key1 = rm.ApplyFromDocumentNumber, Key2 = rm.ApplyToDocumentNumber } equals new { Key1 = ta.Lapfrdnm, Key2 = ta.Laptodnm } into joinResult
  from ta in joinResult.DefaultIfEmpty()
  where ta != null && ta.Laptodnm == "8403017" // Added null check for ta
                                               // && rm.GLPOSTDT <= CutoffDate // Uncomment if @CutoffDate is a parameter
  select new InvoiceSummary
  {
      DocumentNumber = ta.Laptodnm, // Adjusted to use LAPTODNM from ta
      ItemNumber = ta.Litmnmbr,
      ItemAmount = -1 * (ta != null ? rm.ApplyToAmount : 0)
  })
  .GroupBy(a => new { a.DocumentNumber, a.ItemNumber })
  .Where(g => g.Sum(a => a.ItemAmount) > 0)
  .Select(g => new InvoiceSummary
  {
      DocumentNumber = g.Key.DocumentNumber,
      ItemNumber = g.Key.ItemNumber,
      ItemAmount = g.Sum(a => a.ItemAmount)
  });   

报错信息 #

Unable to translate set operation when matching columns on both sides have different store types.

错误核心原因 #

LINQ的Union操作对两边集合的对应字段要求数据库存储类型完全匹配，包括字符串长度、数值类型、可空性等。代码中存在多个类型不匹配点：

1. 字符串字段长度不一致：原始SQL使用固定长度带空格的文档编号（如CHAR(21)类型），但LINQ中用了短字符串"8403017"，导致两边DocumentNumber的存储类型不匹配。
2. 数值字段可空性/类型不统一：Sop30300.Xtndprce与RM20201.ApplyToAmount可能存在类型（如decimal vs float）或可空性差异，计算后的ItemAmount类型无法对齐。
3. 误用Union：原始SQL用UNION ALL（保留重复项），但LINQ的Union会自动去重，不仅不符合原始逻辑，还会触发更严格的类型校验。

具体修复步骤 #

1. 对齐字符串字段长度：将文档编号补全到数据库字段的固定长度，比如如果Sopnumbe是CHAR(21)，就把"8403017"补空格到21位："8403017".PadRight(21)。
2. 统一数值字段类型：确保ItemAmount的计算结果类型与左边一致，比如如果Xtndprce是decimal，就用0m代替0，并处理可空值：-1 * (ta != null ? rm.ApplyToAmount.Value : 0m)。
3. 替换Union为Concat：Concat对应SQL的UNION ALL，不需要去重，类型校验更宽松，也完全贴合原始逻辑。
4. 简化JOIN逻辑：原始SQL中LEFT JOIN后加ta.LAPTODNM = ...的条件，等价于INNER JOIN，直接改成内连接可避免多余的空值判断。

修正后的LINQ代码 #

// 定义匹配数据库固定长度的目标文档编号
var targetDocNumber = "8403017".PadRight(21);

var result = (from sop in _librgContext.Sop30300
              where sop.Sopnumbe == targetDocNumber && sop.Xtndprce != 0
              select new InvoiceSummary
              {
                  DocumentNumber = sop.Sopnumbe,
                  ItemNumber = sop.Itemnmbr,
                  ItemAmount = sop.Xtndprce
              })
  .Concat(
  from rm in _librgContext.Rm20201
  join ta in _librgContext.TRM20201 
    on new { rm.ApplyFromDocumentNumber, rm.ApplyToDocumentNumber } 
    equals new { ta.Lapfrdnm, ta.Laptodnm }
  where ta.Laptodnm == targetDocNumber
  select new InvoiceSummary
  {
      DocumentNumber = ta.Laptodnm,
      ItemNumber = ta.Litmnmbr,
      ItemAmount = -1 * rm.ApplyToAmount.Value // 假设ApplyToAmount是可空decimal，取Value确保非空
  })
  .GroupBy(a => new { a.DocumentNumber, a.ItemNumber })
  .Where(g => g.Sum(a => a.ItemAmount) > 0)
  .Select(g => new InvoiceSummary
  {
      DocumentNumber = g.Key.DocumentNumber,
      ItemNumber = g.Key.ItemNumber,
      ItemAmount = g.Sum(a => a.ItemAmount)
  });

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