我正在尝试使用EF6更新记录。首先找到记录，如果它存在，则更新。
这是我的代码:
var book = new Model.Book
{
BookNumber = _book.BookNumber,
BookName = _book.BookName,
BookTitle = _book.BookTitle,
};
using (var db = new MyContextDB())
{
var result = db.Books.SingleOrDefault(b => b.BookNumber == bookNumber);
if (result != null)
{
try
{
db.Books.Attach(book);
db.Entry(book).State = EntityState.Modified;
db.SaveChanges();
}
catch (Exception ex)
{
throw;
}
}
}
每次我尝试使用上面的代码更新记录时，我都会得到这个错误:
{System.Data.Entity.Infrastructure。DbUpdateConcurrencyException:存储
更新、插入或删除语句受影响的数量超出预期
自实体以来，实体可能已被修改或删除
被加载。刷新ObjectStateManager条目
以下是我的后ria实体更新方法(Ef6时间框架):
public static void UpdateSegment(ISegment data)
{
if (data == null) throw new ArgumentNullException("The expected Segment data is not here.");
var context = GetContext();
var originalData = context.Segments.SingleOrDefault(i => i.SegmentId == data.SegmentId);
if (originalData == null) throw new NullReferenceException("The expected original Segment data is not here.");
FrameworkTypeUtility.SetProperties(data, originalData);
context.SaveChanges();
}
注意FrameworkTypeUtility.SetProperties()是我在NuGet上AutoMapper之前写的一个小工具函数:
public static void SetProperties<TIn, TOut>(TIn input, TOut output, ICollection<string> includedProperties)
where TIn : class
where TOut : class
{
if ((input == null) || (output == null)) return;
Type inType = input.GetType();
Type outType = output.GetType();
foreach (PropertyInfo info in inType.GetProperties())
{
PropertyInfo outfo = ((info != null) && info.CanRead)
? outType.GetProperty(info.Name, info.PropertyType)
: null;
if (outfo != null && outfo.CanWrite
&& (outfo.PropertyType.Equals(info.PropertyType)))
{
if ((includedProperties != null) && includedProperties.Contains(info.Name))
outfo.SetValue(output, info.GetValue(input, null), null);
else if (includedProperties == null)
outfo.SetValue(output, info.GetValue(input, null), null);
}
}
}
这段代码是一个测试的结果，该测试只更新了一组列，而没有首先进行查询以返回记录。它首先使用实体框架7代码。
// This function receives an object type that can be a view model or an anonymous
// object with the properties you want to change.
// This is part of a repository for a Contacts object.
public int Update(object entity)
{
var entityProperties = entity.GetType().GetProperties();
Contacts con = ToType(entity, typeof(Contacts)) as Contacts;
if (con != null)
{
_context.Entry(con).State = EntityState.Modified;
_context.Contacts.Attach(con);
foreach (var ep in entityProperties)
{
// If the property is named Id, don't add it in the update.
// It can be refactored to look in the annotations for a key
// or any part named Id.
if(ep.Name != "Id")
_context.Entry(con).Property(ep.Name).IsModified = true;
}
}
return _context.SaveChanges();
}
public static object ToType<T>(object obj, T type)
{
// Create an instance of T type object
object tmp = Activator.CreateInstance(Type.GetType(type.ToString()));
// Loop through the properties of the object you want to convert
foreach (PropertyInfo pi in obj.GetType().GetProperties())
{
try
{
// Get the value of the property and try to assign it to the property of T type object
tmp.GetType().GetProperty(pi.Name).SetValue(tmp, pi.GetValue(obj, null), null);
}
catch (Exception ex)
{
// Logging.Log.Error(ex);
}
}
// Return the T type object:
return tmp;
}
以下是完整的代码:
public interface IContactRepository
{
IEnumerable<Contacts> GetAllContats();
IEnumerable<Contacts> GetAllContactsWithAddress();
int Update(object c);
}
public class ContactRepository : IContactRepository
{
private ContactContext _context;
public ContactRepository(ContactContext context)
{
_context = context;
}
public IEnumerable<Contacts> GetAllContats()
{
return _context.Contacts.OrderBy(c => c.FirstName).ToList();
}
public IEnumerable<Contacts> GetAllContactsWithAddress()
{
return _context.Contacts
.Include(c => c.Address)
.OrderBy(c => c.FirstName).ToList();
}
//TODO Change properties to lambda expression
public int Update(object entity)
{
var entityProperties = entity.GetType().GetProperties();
Contacts con = ToType(entity, typeof(Contacts)) as Contacts;
if (con != null)
{
_context.Entry(con).State = EntityState.Modified;
_context.Contacts.Attach(con);
foreach (var ep in entityProperties)
{
if(ep.Name != "Id")
_context.Entry(con).Property(ep.Name).IsModified = true;
}
}
return _context.SaveChanges();
}
public static object ToType<T>(object obj, T type)
{
// Create an instance of T type object
object tmp = Activator.CreateInstance(Type.GetType(type.ToString()));
// Loop through the properties of the object you want to convert
foreach (PropertyInfo pi in obj.GetType().GetProperties())
{
try
{
// Get the value of the property and try to assign it to the property of T type object
tmp.GetType().GetProperty(pi.Name).SetValue(tmp, pi.GetValue(obj, null), null);
}
catch (Exception ex)
{
// Logging.Log.Error(ex);
}
}
// Return the T type object
return tmp;
}
}
public class Contacts
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Email { get; set; }
public string Company { get; set; }
public string Title { get; set; }
public Addresses Address { get; set; }
}
public class Addresses
{
[Key]
public int Id { get; set; }
public string AddressType { get; set; }
public string StreetAddress { get; set; }
public string City { get; set; }
public State State { get; set; }
public string PostalCode { get; set; }
}
public class ContactContext : DbContext
{
public DbSet<Addresses> Address { get; set; }
public DbSet<Contacts> Contacts { get; set; }
public DbSet<State> States { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
var connString = "Server=YourServer;Database=ContactsDb;Trusted_Connection=True;MultipleActiveResultSets=true;";
optionsBuilder.UseSqlServer(connString);
base.OnConfiguring(optionsBuilder);
}
}
这里是这个问题的最佳解决方案:在视图中添加所有的ID(键)。考虑将多个表命名为(First, Second和Third)
@Html.HiddenFor(model=>model.FirstID)
@Html.HiddenFor(model=>model.SecondID)
@Html.HiddenFor(model=>model.Second.SecondID)
@Html.HiddenFor(model=>model.Second.ThirdID)
@Html.HiddenFor(model=>model.Second.Third.ThirdID)
在c#代码中，
[HttpPost]
public ActionResult Edit(First first)
{
if (ModelState.Isvalid)
{
if (first.FirstID > 0)
{
datacontext.Entry(first).State = EntityState.Modified;
datacontext.Entry(first.Second).State = EntityState.Modified;
datacontext.Entry(first.Second.Third).State = EntityState.Modified;
}
else
{
datacontext.First.Add(first);
}
datacontext.SaveChanges();
Return RedirectToAction("Index");
}
return View(first);
}
这是实体框架6.2.0。
如果你有一个特定的DbSet和一个需要更新或创建的项:
var name = getNameFromService();
var current = _dbContext.Names.Find(name.BusinessSystemId, name.NameNo);
if (current == null)
{
_dbContext.Names.Add(name);
}
else
{
_dbContext.Entry(current).CurrentValues.SetValues(name);
}
_dbContext.SaveChanges();
但是，这也可以用于具有单个主键或复合主键的通用DbSet。
var allNames = NameApiService.GetAllNames();
GenericAddOrUpdate(allNames, "BusinessSystemId", "NameNo");
public virtual void GenericAddOrUpdate<T>(IEnumerable<T> values, params string[] keyValues) where T : class
{
foreach (var value in values)
{
try
{
var keyList = new List<object>();
//Get key values from T entity based on keyValues property
foreach (var keyValue in keyValues)
{
var propertyInfo = value.GetType().GetProperty(keyValue);
var propertyValue = propertyInfo.GetValue(value);
keyList.Add(propertyValue);
}
GenericAddOrUpdateDbSet(keyList, value);
//Only use this when debugging to catch save exceptions
//_dbContext.SaveChanges();
}
catch
{
throw;
}
}
_dbContext.SaveChanges();
}
public virtual void GenericAddOrUpdateDbSet<T>(List<object> keyList, T value) where T : class
{
//Get a DbSet of T type
var someDbSet = Set(typeof(T));
//Check if any value exists with the key values
var current = someDbSet.Find(keyList.ToArray());
if (current == null)
{
someDbSet.Add(value);
}
else
{
Entry(current).CurrentValues.SetValues(value);
}
}
当尝试使用Attach()和SaveChanges()组合更新记录时，我也有同样的问题，但我使用的是SQLite DB及其EF提供者(相同的代码在SQLServer DB中工作没有问题)。
I found out, when your DB column has GUID (or UniqueIdentity) in SQLite and your model is nvarchar, SQLIte EF treats it as Binary(i.e., byte[]) by default. So when SQLite EF provider tries to convert GUID into the model (string in my case) it will fail as it will convert to byte[]. The fix is to tell the SQLite EF to treat GUID as TEXT (and therefore conversion is into strings, not byte[]) by defining "BinaryGUID=false;" in the connectionstring (or metadata, if you're using database first) like so:
<connectionStrings>
<add name="Entities" connectionString="metadata=res://savetyping...=System.Data.SQLite.EF6;provider connection string="data source=C:\...\db.sqlite3;Version=3;BinaryGUID=false;App=EntityFramework"" providerName="System.Data.EntityClient" />
</connectionStrings>
链接到对我有效的解决方案:
SQLite实体框架6提供者如何处理指南?
Not related to this specific example, but I came across a challenge when trying to use EF and a DateTime field as the concurrency check field. It appears the EF concurrency code doesn't honor the precision setting from the metadata (edmx) i.e. Type="DateTime" Precision="3".
The database datetime field will store a millisecond component within the field (i.e. 2020-10-18 15:49:02.123). Even if you set the original value of the Entity to a DateTime that includes the millisecond component, the SQL EF generates is this:
UPDATE [dbo].[People]
SET [dateUpdated] = @0
WHERE (([PeopleID] = @1) AND ([dateUpdated] = @2))
-- @0: '10/19/2020 1:07:00 AM' (Type = DateTime2)
-- @1: '3182' (Type = Int32)
-- @2: '10/19/2020 1:06:10 AM' (Type = DateTime2)
正如您所看到的，@2是一个没有毫秒组件的STRING表示。这将导致更新失败。
因此，如果您打算使用DateTime字段作为并发键，那么在检索记录时必须从数据库字段中剥离毫秒/ tick，并且仅使用类似剥离的DateTime传递/更新字段。
//strip milliseconds due to EF concurrency handling
PeopleModel p = db.people.Where(x => x.PeopleID = id);
if (p.dateUpdated.Millisecond > 0)
{
DateTime d = new DateTime(p.dateUpdated.Ticks / 10000000 * 10000000);
object[] b = {p.PeopleID, d};
int upd = db.Database.ExecuteSqlCommand("Update People set dateUpdated=@p1 where peopleId=@p0", b);
if (upd == 1)
p.dateUpdated = d;
else
return InternalServerError(new Exception("Unable to update dateUpdated"));
}
return Ok(p);
当用新值更新字段时，也去掉毫秒
(param)int id, PeopleModel person;
People tbl = db.People.Where(x => x.PeopleID == id).FirstOrDefault();
db.Entry(tbl).OriginalValues["dateUpdated"] = person.dateUpdated;
//strip milliseconds from dateUpdated since EF doesn't preserve them
tbl.dateUpdated = new DateTime(DateTime.Now.Ticks / 10000000 * 10000000);
