c# - 访问 LINQ to Entities 集中的数组元素

Question

我在 Linq 查询中对字符串使用 Split 方法，但只需要第二个元素。我在下面的代码中得到一个“System.InvalidOperationException: 'Unrecognized expression node: ArrayIndex'”:

    var RMA_stops_all = (from rma in rDb.DistributionStopInformations
                         join line in rDb.DistributionLineItems on rma.UniqueIdNo equals line.UniqueIdNo
                         where line.RmaNumber != null 
                         &&
                         (line.DatetimeCreated > Convert.ToDateTime(dateToCheck_rma) &&
                         line.DatetimeCreated < Convert.ToDateTime(dateToCheck_rma).AddDays(7))
                         && rma.CustomerNo == TNGCustNo
                         select new
                         {
                             dtCreated = line.DatetimeCreated,
                             UniqueIdNo = rma.UniqueIdNo,
                             RmaNumber = line.RmaNumber,
                             RmaOriginalUniqueId = line.RmaOriginalUniqueId,
                             ItemSequenceNo = line.ItemSequenceNo,
                             ItemNumber = line.ItemNumber,
                             goodRMA_flag =  line.RmaNumber.Contains("/078"),
                             rmaGood = line.RmaNumber.Split(new string[] { "/" }, StringSplitOptions.None)[1]
                         }).ToArray();

如果我删除数组上的 [1] 它会起作用，我可以通过以下方式访问整个数据集的两个元素:

  foreach (var item in RMA_stops_all)
        {
         var right = RMA_stops_all.First().rmaGood[1];
         var left = RMA_stops_all.First().rmaGood[0];
            Console.WriteLine("left {0} - right{1} ",left.ToString(), right.ToString());
        }

编辑 - 呃。上面的“测试”完全没用(温和地指出) - 然而，下面确实证明它工作正常(一些返回值只有 1 个元素，因此额外的 if block - 输出符合预期:

 foreach (var item in RMA_stops_all)
            {
                string right, left;
                if (item.rmaGood.Length == 1)
                {
                     left = item.rmaGood[0].ToString();
                     right = "Not there";
                }
                else
                {
                 left = item.rmaGood[0].ToString();
                 right = item.rmaGood[1].ToString();
                }
                Console.WriteLine("left {0} - right{1} ", left, right);
            }

dbMonitor 的 SQL 输出:

SELECT t2.datetime_created AS "DatetimeCreated", 
       t1.unique_id_no AS "UniqueIdNo", 
       t2.rma_number AS "RmaNumber", 
       t2.rma_original_unique_id AS "RmaOriginalUniqueId",
       t2.item_sequence_no AS "ItemSequenceNo", 
       t2.item_number AS "ItemNumber", 
       (t2.rma_number LIKE :p3) OR (t2.rma_number LIKE :p4) AS "C1",
       t2.rma_number AS "RmaNumber1"
FROM cops_reporting.distribution_stop_information t1
INNER JOIN cops_reporting.distribution_line_items t2 
    ON t1.unique_id_no = t2.unique_id_no
WHERE (t2.rma_number IS NOT NULL) 
   AND (t2.datetime_created > :p0)
   AND (t2.datetime_created < :p1)
   AND (t1.customer_no = :p2)

Answer 1

我没有意识到 Linq to Entities 可以直接访问一些 SQL 函数，所以这可能对你有用:

var RMA_stops_all = (from rma in rDb.DistributionStopInformations
                     join line in rDb.DistributionLineItems on rma.UniqueIdNo equals line.UniqueIdNo
                     where line.RmaNumber != null 
                     &&
                     (line.DatetimeCreated > Convert.ToDateTime(dateToCheck_rma) &&
                     line.DatetimeCreated < Convert.ToDateTime(dateToCheck_rma).AddDays(7))
                     && rma.CustomerNo == TNGCustNo
                     select new
                     {
                         dtCreated = line.DatetimeCreated,
                         UniqueIdNo = rma.UniqueIdNo,
                         RmaNumber = line.RmaNumber,
                         RmaOriginalUniqueId = line.RmaOriginalUniqueId,
                         ItemSequenceNo = line.ItemSequenceNo,
                         ItemNumber = line.ItemNumber,
                         goodRMA_flag =  line.RmaNumber.Contains("/078"),
                         rmaGood = line.RmaNumber.Substring(line.RmaNumber.IndexOf("/")+1)
                     }).ToArray();

假设 RmaNumber 看起来像 #/# 并且没有您想要避免的额外斜线。