使用联合时,DataTable列设置为只读

DataTable 稍后在我的程序中更新它,但是我收到一个 System.Exception 尝试修改我的某个列(IsUsed)时,错误为“Column IsUsed is read only”。这是真的,在 数据表 有 ReadOnly 属性设置为true。

以下是生成SQL的C代码:

public IgnoredPositionSet GetAllForReconciliation(int aReconciliationId)
{   
      SqlGenerator internalIgnoredPositionSqlGenerator = base.GetCoreSqlGenerator()
        .Select.Add(@"
          IP.SecurityId,
          IP.SecurityName,
          IP.Quantity,
          NULL AS AccountTypeName")
        .Join.Add($@"
          LEFT JOIN 
            (
              SELECT
                ReconciliationId,
                MatchId,
                SecurityId,
                SecurityName,
                SUM(QuantityTradeDate) AS Quantity
              FROM [PositionReconciliation.InternalPosition]
              GROUP BY
                ReconciliationId,
                MatchId,
                SecurityId,
                SecurityName
            ) IP
          ON IP.ReconciliationId = {ServiceTableAlias}.ReconciliationId
          AND IP.MatchId         = {ServiceTableAlias}.MatchId")
        .Where.Add("IsInternalPosition = 1");

      SqlGenerator externalIgnoredPositionSqlGenerator = GetCoreSqlGenerator()
        .Select.Add(@"
          NULL AS SecurityId,
          EP.SecurityName,
          EP.Quantity,
          AccountTypeName")
        .Join.Add($@"
          LEFT JOIN 
            (
              SELECT
                ReconciliationId,
                MatchId,
                SecurityName,
                SUM(QuantityTradeDate) AS Quantity
              FROM [PositionReconciliation.ExternalPosition]
              GROUP BY
                ReconciliationId,
                MatchId,
                SecurityName
            ) EP
          ON EP.ReconciliationId = {ServiceTableAlias}.ReconciliationId
          AND EP.MatchId         = {ServiceTableAlias}.MatchId")
        .Join.Add($@"
          LEFT JOIN
            (
              SELECT
                EP.ReconciliationId,
                EP.MatchId,
                A.AccountTypeId,
                AT.Name AS AccountTypeName
              FROM [PositionReconciliation.ExternalPosition] EP
              LEFT JOIN [Core.CustodianFund.AccountMap]      A  ON A.Id  = EP.CustodianAccountId
              LEFT JOIN [Reconciliation.Cash.AccountType]    AT ON AT.Id = A.AccountTypeId
              GROUP BY ReconciliationId, MatchId, AccountTypeId, AT.Name
            ) EPAT
          ON  EPAT.ReconciliationId = {ServiceTableAlias}.ReconciliationId
          AND EPAT.MatchId          = {ServiceTableAlias}.MatchId")
        .Where.Add("IsInternalPosition = 0");

      var internalPositions = GetEntitySet(internalIgnoredPositionSqlGenerator); //ReadOnly props are false
      var externalPositions = GetEntitySet(externalIgnoredPositionSqlGenerator); //ReadOnly props are false

      return GetEntitySet($"SELECT * FROM ({internalIgnoredPositionSqlGenerator} UNION ALL {externalIgnoredPositionSqlGenerator}) {ServiceTableAlias} WHERE {nameof(IgnoredPosition.ReconciliationId)} = {aReconciliationId}");

生成的SQL如下所示:

SELECT
  SERVICE_TABLE.*,
  IP.SecurityId,
  IP.SecurityName,
  IP.Quantity,
  NULL AS AccountTypeName
FROM [PositionReconciliation.IgnoredPosition] SERVICE_TABLE
LEFT JOIN
(
SELECT
ReconciliationId,
MatchId,
SecurityId,
SecurityName,
SUM(QuantityTradeDate) AS Quantity
FROM [PositionReconciliation.InternalPosition]
GROUP BY
ReconciliationId,
MatchId,
SecurityId,
SecurityName
) IP
ON IP.ReconciliationId = SERVICE_TABLE.ReconciliationId
AND IP.MatchId         = SERVICE_TABLE.MatchId
WHERE
(IsInternalPosition = 1)
 UNION ALL
  SELECT
  SERVICE_TABLE.*,
  NULL AS SecurityId,
  EP.SecurityName,
  EP.Quantity,
  AccountTypeName
FROM [PositionReconciliation.IgnoredPosition] SERVICE_TABLE
LEFT JOIN
(
SELECT
ReconciliationId,
MatchId,
SecurityName,
SUM(QuantityTradeDate) AS Quantity
FROM [PositionReconciliation.ExternalPosition]
GROUP BY
ReconciliationId,
MatchId,
SecurityName
) EP
ON EP.ReconciliationId = SERVICE_TABLE.ReconciliationId
AND EP.MatchId         = SERVICE_TABLE.MatchId
LEFT JOIN
(
SELECT
EP.ReconciliationId,
EP.MatchId,
A.AccountTypeId,
AT.Name AS AccountTypeName
FROM [PositionReconciliation.ExternalPosition] EP
LEFT JOIN [Core.CustodianFund.AccountMap]      A  ON A.Id  = EP.CustodianAccountId
LEFT JOIN [Reconciliation.Cash.AccountType]    AT ON AT.Id = A.AccountTypeId
GROUP BY ReconciliationId, MatchId, AccountTypeId, AT.Name
) EPAT
ON  EPAT.ReconciliationId = SERVICE_TABLE.ReconciliationId
AND EPAT.MatchId          = SERVICE_TABLE.MatchId
WHERE
(IsInternalPosition = 0)

DataSet

public DataSet GetDataSet(string                       aCommandText,
                          IEnumerable<DbDataParameter> aParameters            = null,
                          int?                         aCommandTimeoutSeconds = null,
                          bool                         aIsUpdateable          = true)
{
  var da

taSet = new DataSet
      {
        Locale             = CultureInfo.InvariantCulture,
        EnforceConstraints = false
      };

      return PerformDbOperation(new DbOperationInfo(aCommandText, aCommandTimeoutSeconds, aParameters),
                                aDbOperationInfo => 
                                {
                                  DataAdapter.SelectCommand       = aDbOperationInfo.Command;
                                  DataAdapter.MissingSchemaAction = aIsUpdateable ? MissingSchemaAction.AddWithKey : MissingSchemaAction.Add;
                                  DataAdapter.Fill(dataSet);

                                  if (dataSet.Tables.Count > 0)
                                    aDbOperationInfo.AffectedRowCount = dataSet.Tables[0].Rows.Count;

                                  return dataSet;
                                });
    }

我一直在玩这个,我发现没有 union 这个 只读

我知道这个问题以前也被问过类似的问题,但我不想绕圈子 只读 属性为真,我想从源头上解决这个问题。

更新:我相信这是因为这些列被归类为“computed”列,C代码将默认readonly为true。