.net Entity Framework + mysql 使用中常见问题

来源：互联网发布：淘宝宝贝打折怎么设置编辑：程序博客网时间：2024/05/29 10:46

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

}

0 0