.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();

}