Entity Framework等ORM技術讓更新資料表動作變得異常簡單。

在ADO.NET時代,若要更新Product資料表某一筆資料的兩個欄位,得先想好SQL指令建立SqlCommand,接著逐一新増SqlParameter @p1, @p2, @k1, @k2 並傳入各參數值,:

var sql = "UPDATE Product SET Col1 = @p1, Col2 = @p2 WHERE ColPK = @k1 AND ColPK2 = @k2"; 
SqlCommand cmd = cn.CreateCommand(sql); 
cmd.Parameters.Add("@p1", SqlDbType.VarChar).Value = p1值; 
cmd.Parameters.Add("@p2", SqlDbType.VarChar).Value = p2值; 
cmd.Parameters.Add("@k1", SqlDbType.VarChar).Value = k1值; 
cmd.Parameters.Add("@k2", SqlDbType.VarChar).Value = k2值; 

cmd.ExecuteNonQuery();
最後來個SqlCommand.ExecuteNonQuery(),大功告成。

有了EF,我們只需這樣做:

var item = entities.Product.Where(o => o.ColPK == k1值 && o.ColPK2 = k2值);
item.Col1 = p1值;
item.Col2 = p2值;
entities.SaveChanges();
是不是乾淨俐落許多?

今天踩到小圖釘一根。範例以下,程式透過PeriodId、CostCenter及JobType比對在DataGroup中抓出兩筆資料,將其Tag欄位改成現在時間的HHmmss字串,最後呼叫SaveChanges()將結果寫入DB。

        static void Test1()
        {
            using (var ctx = new XDBEntities())
            {
                var dateGrps = ctx.DataGroup.Where(o => o.PeriodId == "2021A"
                    && (o.CostCenter == "100" && o.JobType == "A" ||
                    o.CostCenter == "101" && o.JobType == "S")).ToList();
                foreach (var dg in dateGrps)
                {
                    Console.WriteLine("DataGroup:{0}-{1}", dg.CostCenter, dg.JobType);
                    dg.Tag = DateTime.Now.ToString("HHmmss");
                }
                ctx.SaveChanges();
            }
        }

測試結果如預期,兩筆資料的Tag欄位正確更新。但以上寫法並不實用,因為實務上查詢條件幾乎都由使用者輸入動態決定,怎麼可能直接寫死Where(o => …)?

針對動態條件,我最常用Database.SqlQuery<TElement>(String, Object[])處理自訂查詢,TElement可以傳入任何型別,只要屬性名稱跟資料庫欄位名稱對得上,EF可自動轉型生出指定物件型別,十分方便。憑著直覺,依著傳入變數產生WHERE條件字串及參數值,我將原本的.Where()查詢改寫成Database.SqlQuery<DataGroup>()搞定:

        static string[] keys = new string[] { "100-A", "101-S" };
        static void Test2()
        {
            using (var ctx = new XDBEntities())
            {
                int paramCount = 0;
                List<object> paramValues = new List<object>();
                string where = "PeriodId='2021A' AND (" +
                    string.Join(" OR ",
                    keys.Select(o =>
                    {
                        var p = o.Split('-');
                        var criteria = "CostCenter={" + paramCount++ +
                        "} AND JobType={" + paramCount++ + "}";
                        paramValues.Add(p[0]);
                        paramValues.Add(p[1]);
                        return criteria;
                    }).ToArray()) + ")";
                var dataGrps = ctx.Database.SqlQuery<DataGroup>(
                    "select * from datagroup where " + where, paramValues.ToArray())
                    .ToList();
                foreach (var dg in dataGrps)
                {
                    Console.WriteLine("DataGroup:{0}-{1}", dg.CostCenter, dg.JobType);
                    dg.Tag = DateTime.Now.ToString("HHmmss");
                }
                ctx.SaveChanges();
            }
        }

安全宣導:組裝SQL字串時不可直接串接使用者輸入內容,以免埋下致命的SQL Injection漏洞(如果你不知道什麼是SQL Injection,建議儘快雙手打上石膏裝殘,沒搞清楚前先別寫任何資料庫相關程式以免禍國殃民)。可參考上述程式的做法,用{0}{1}在SQL語法中嵌入參數,再以object[]傳入參數內容。{0}{1}標註源自string.Format,但絕對不是字串置換,背後EF會將其轉為DbParamater型別,故能杜絕SQL Injection風險。另外,當DB為SQL Server,{0}{1}也可用@p0、@p1取代,但不同資料庫符號規則有別,在Oracle則要寫成:p0、:p1,基於EF儘可能降低DB依賴的思維,我個人偏好{0}{1}寫法。

Database.SqlQuery<T>正確地取回兩筆資料,但修改Tag呼叫ctx.SaveChanges(),修改結果卻沒寫回資料庫。

經過一番研究,才知道自己錯在哪裡。SqlQuery有兩種:

Database.SqlQuery<TElement> vs DbSet<TEntity>.SqlQuery

二者的最大差異在於前者的TElement可以是任意類別,只要屬性能跟SELECT欄位對應即可,但Context永遠不追蹤傳回物件的改變,即使它是EF所定義的Entity型別(The results of this query are never tracked by the context even if the type of object returned is an entity type.);後者限定只能用於EF定義好的Entity,傳回結果時預設會追蹤其變化(By default, the entities returned are tracked by the context)。

找出原因,解決只在彈指之間,將ctx.Database.SqlQuery<DataGroup>()改成ctx.DataGroup.SqlQuery(),一切搞定!


Comments

# by tomexou

SqlQuery<T>中的T類別,屬性數量要和sql查詢欄位同數量,這點比較煩人,畢竟有些查詢多個欄位的,又得造一個pico。dapper.net這輕量型orm的查詢方式比較完美,數量不match T沒關係,還能傳回dynamic集合。

Post a comment