iT邦幫忙

0

LINQ轉換字串問題__0726已解決

目前程式碼

var sTYPE_L = new System.Collections.Generic.List<XXX>(){
               new XXX { iXXX_TYPE = "1", cXXX_REMARKS = "AA" },
               new XXX { iXXX_TYPE = "2", cXXX_REMARKS = "BB" },
               new XXX { iXXX_TYPE = "C", cXXX_REMARKS = "CC" },
               new XXX { iXXX_TYPE = "D", cXXX_REMARKS = "DD" }};

var dt = (from t1 in db.XXX
          where t1.bXXX_CANCEL == false
          select new
     {
           t1.iXXX_TYPE,
           XXX_TYPE = (from t2 in sTYPE_L
                       where t2.iXXX_TYPE == t1.iXXX_TYPE
                       select t2.cXXX_REMARKS).FirstOrDefault()
     }).Distinct();

錯誤訊息
{"Unable to create a constant value of type 'AYModel.XXX'. Only primitive types or enumeration types are supported in this context."}

錯在dt的XXX_TYPE,主要是資料庫資料顯示時,轉換字串,希望能提供解決方法

0726 完成程式碼

var sTYPE_L = new System.Collections.Generic.List<XXX>(){
               new XXX { iXXX_TYPE = "1", cXXX_REMARKS = "AA" },
               new XXX { iXXX_TYPE = "2", cXXX_REMARKS = "BB" },
               new XXX { iXXX_TYPE = "C", cXXX_REMARKS = "CC" },
               new XXX { iXXX_TYPE = "D", cXXX_REMARKS = "DD" }};

var dt = (from t1 in db.XXX
          where t1.bXXX_CANCEL == false
          select new
          {
            t1.iXXX_PK,
            t1.iXXX_TYPE
          }).ToList()
          .Select(t1 => new
          {
            t1.iXXX_PK,
            XXX_TYPE = (from t2 in sTYPE_L
                        where t2.iXXX_TYPE == t1.iXXX_TYPE
                        select t2.cPOINT_REMARKS).FirstOrDefault()
          }).Distinct();

補充: .Select(t1 => new 的t1可以命名成其他的,例如t5,底下記得跟著變名稱

1 個回答

1
fysh711426
iT邦研究生 4 級 ‧ 2018-07-25 22:31:29
最佳解答

問題應該在於 select 的子查詢 sTYPE_L 是記憶體資料,不是真正的資料表,
因此 EF 無法將 LINQ 轉換成 SQL 語法。

select new
{
      t1.iXXX_TYPE,
      XXX_TYPE = (from t2 in sTYPE_L
                  where t2.iXXX_TYPE == t1.iXXX_TYPE
                  select t2.cXXX_REMARKS).FirstOrDefault()
}

解法:
可以試試看,先將第一段查詢轉成 List 載入記憶體後,再做子查詢。

var dt = (from t1 in XXX
          where t1.bXXX_CANCEL == false
          select new
          {
              t1.iXXX_TYPE
          })
          .ToList()
          .Select(t1 => new
          {
              t1.iXXX_TYPE,
              XXX_TYPE = (from t2 in sTYPE_L
                          where t2.iXXX_TYPE == t1.iXXX_TYPE
                          select t2.cXXX_REMARKS).FirstOrDefault()
          }).Distinct();
優悠 iT邦新手 4 級 ‧ 2018-07-26 09:01:57 檢舉

來試試,謝謝你

我要發表回答

立即登入回答