LINQ轉換字串問題__0726已解決

visual studio c# linq

優悠 2018-07-25 17:55:32 ‧ 2417 瀏覽

目前程式碼

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，底下記得跟著變名稱

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

小碼農米爾

iT邦高手 1 級 ‧ 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();