.net Entity Framework + mysql 使用中常見問題

1. 對于Mysql 表記錄進行更新時，如果更新内容沒變化，則entities.SaveChanges() 會引起錯誤資訊。

此原因為mysql傳回為0,而此架構則認識沒作任何更新會是錯誤。

解決方法是：在架構中修改架構構造添加事件，以下是代碼

#region 構造函數

        /// <summary>
        /// 請使用應用程式配置檔案的“Healthy2013Entities”部分中的連接配接字元串初始化新 Healthy2013Entities 對象。
        /// </summary>
        public Healthy2013Entities()
            : base("name=Healthy2013Entities", "Healthy2013Entities")
        {
            this.ContextOptions.LazyLoadingEnabled = true;
            OnContextCreated();
            this.SavingChanges += new EventHandler(Healthy2013Entities_SavingChanges);
        }

        void Healthy2013Entities_SavingChanges(object sender, EventArgs e)
        {
            var context = sender as ObjectContext;

            if (context == null) return;

            var updatedEntites = context.ObjectStateManager.GetObjectStateEntries(System.Data.EntityState.Modified);

            foreach (var ose in updatedEntites)
            {
                var props = ose.GetModifiedProperties();

                int modifyCount = 0;
                int propCount = 0;

                foreach (var item in props)
                {
                    var index = ose.OriginalValues.GetOrdinal(item);

                    if (ose.OriginalValues.GetValue(index).Equals(ose.CurrentValues.GetValue(index)))
                    {
                        modifyCount++;
                    }
                    propCount++;
                }
                if (modifyCount == propCount)
                {
                    context.Refresh(RefreshMode.StoreWins, ose.Entity);
                }

            }
        }

        /// <summary>
        /// 初始化新的 Healthy2013Entities 對象。
        /// </summary>
        public Healthy2013Entities(string connectionString)
            : base(connectionString, "Healthy2013Entities")
        {
            this.ContextOptions.LazyLoadingEnabled = true;
            OnContextCreated();
            this.SavingChanges += new EventHandler(Healthy2013Entities_SavingChanges);
        }

        /// <summary>
        /// 初始化新的 Healthy2013Entities 對象。
        /// </summary>
        public Healthy2013Entities(EntityConnection connection)
            : base(connection, "Healthy2013Entities")
        {
            this.ContextOptions.LazyLoadingEnabled = true;
            OnContextCreated();
            this.SavingChanges += new EventHandler(Healthy2013Entities_SavingChanges);
        }

        #endregion
           

2. 使用查詢語句時，如

var family = (from fl in entities.Tbl_Info_Family

                                      where fl.Family_ID == _familyID

                                      select fl);

是在下面使用到family 時，才去執行到SQL進行資料庫查詢。

是以在用到family時，foreach裡面不能再套一層foreach的資料庫查詢，會導緻錯誤。如果要這使用時，可以直接先使用.ToArray() 查詢出來直接foreach周遊結果

3. 關于SQL查詢語句中的in 對應到Linq中使用

                    var query = from mb in entities.Tbl_Info_Family_Member

                            where (from fm in entities.Tbl_Info_Family

                                   where fm.Family_ID >= 1 && fm.Family_ID <= 2

                                   select fm.Family_ID).Contains(mb.Family_ID)

                            select mb;

或者是 List<long> lst = new List<long>();

                    var query = from mb in entities.Tbl_Info_Family_Member

                            where lst.Contains(mb.Family_ID)

                            select mb;

4. 事務操作，對于Mysql表引擎預設使用MyISam是不支援事務的，是以需要修改成InnoDB

事務用法是： 

using (TransactionScope scope = new TransactionScope())

  {

                    HomeEntities entities = new HomeEntities();

                    //增加表記錄

                    Home hm = new Home();

                    entities.AddToHome(hm);

                    entities.SaveChanges();

//增加另外一條記錄，有ID關聯

                    Student sd = new Student();

                    sd.HomeID = hm.HomeID;

                    entities.AddToStudent(sd);

                    entities.SaveChanges();

                    scope.Complete();

}