Simple Static Delete and Update Methods
Allow for simple delete and update operations that do not require multiple round trips to the database. They could be static methods on each Entity. For example:
Cars.Delete(int ID)
or
Cars.Delete(Guid ID)
This would delete a car record with the associated ID if it exists. The code generation template should be smart enough to create the static methods using the correct data type.
For updates you would pass in the ID and a set of named parameters for the fields that need to be updated:
Car.Update(10, {Status="Sold", ModifiedDate=DateTime.Now}
I think people are doing this now using functions or executing SQL
John
shared this idea
16 comments
-
Anonymous
commented
i prefer
[TABLE].delete(object[] Keys) -
Yves Goergen
commented
The Linq-specified Delete method looks great. This adds "Delete" as an operation like "Count" to do something with the so-far selected records. Deleting records by their primary key only (much like the Find(params object[]) method works today) and updating only certain columns by a primary key (instead of updating all columns, even those not exposed in a form) is very important for disconnected ASP.NET MVC or Web API scenarios. You often have a link that deletes a record and only need to transfer its ID, so today I need multiple lines of code, or use Find() and retrieve the record first. When updating records in a multi-user environment, you don't want to overwrite columns with old values if the user hasn't really edited those in a specialised form.
-
mark
commented
This is important.
A complication that needs to be solved is how to synchronize the in-memory entities with store entities. This should be possible using the OUTPUT clause in SQL Server.
For DELETE, output the keys for the deleted rows and update the in-memory entities.
For UPDATE, output the keys and written columns.
For INSERT, output everything.There are two variations of bulk DML: DML by query (e.g. insert ... select) or client-provided data (e.g. SqlBulkCopy). In the latter case it might be necessary to write to a temp table first using SqlBulkCopy. After doing that the OUTPUT clause can be used in the actual DML statement.
One should be able to write:
someTable.Insert(myStoreQuery);
as well as
someTable.Insert(inMemoryCollection);
-
Alex Angas
commented
You can use RemoveAll and that does answer the question, however SQL Profiler shows one row being deleted at a time. An expectation a developer may have is that a one-line method named RemoveAll deletes the rows in a single SQL statement.
-
Jordan M
commented
I wouldn't have static methods on the entities themselves, this goes against the way the framework is currently designed.
I would add methods to the DbContext or DbSet<TEntity>, something similar to Keith Barrows suggestion.
-
John Doe
commented
Loading the object in memory and incrementing a property can cause incorrect results when concurrent transactions attempt to do the same. Using optimistic concurrency can eliminate the issue, but in high traffic scenarios, it doesn't seem feasible to reload the object and attempt to increment the property again since the other transactions will be forced to do the same. I'd like to be able to send Value = Value + 1 query to the database.
-
Anonymous
commented
Also, bulk inserts. Inserts should be possible using insert statements with value lists (multiple rows per statement), table values parameters and SqlBulkCopy. These techniques have different sweet spots for different row counts. For example, SqlBulkCopy has high startup overhead but is by far the fastest bulk insert API. It breaks even with TVPs at about 100 rows. TVPs break even with inserts as 2-10 rows. Please allow us to configure which technique we want to use.
-
Anonymous
commented
Can't you use the LINQ removeAll method?
-
Paweł Sawicz commented
I think there would be nice idea if you give method to delete collection. Without using foreach statement.
Now:
foreach(var item in collection)
{
db.Customers.Remove(item);
}
db.SaveChanges();and should be simply
db.Delete(collection);
db.SaveChanges(); -
Roger Hendriks
commented
-
cstaley
commented
And also:
Cars.Delete(IEnumerable<int> ids)
Or building on Keith's suggestion:
Cars.Where(car => enumerableIds.Contains(car.Id)).Delete()
-
James D. Schwarzmeier
commented
I agree fully. There are a number of ideas out there very similar to this:
I'm sure there are others too. These all point to a basic need: the ability to leverage the O/R mapping (i.e. SQL generation) capabilities of EF, but bypass the whole change tracking mechanism. It seems to me that there are seven basic things we need to be able to do...there might be more:
- Insert new rows
- Update row for a specific entity
- Update rows matching a WHERE clause (via LINQ, etc.)
- Delete row for a specific entity
- Delete rows matching a WHERE clause (again view LINQ or something)
- Associate entities in a many-to-many relationship
- Remove the association between entities in a many-to-many relationshipIdeally we could essentially build up a series of commands, and then have EF flush all commands out to the DB all at once -- but the key being that the application developer assumes responsibility for knowing what changes need to occur and in what order. Hopefully it would support objects using either foreign key properties OR navigation properties.
This would be especially helpful in some types of disconnected n-tier applications.
-
Keith Barrows
commented
Or maybe:
context.tableName.Where(a => a.ID == myID).Delete()
context.tableName.Where(a => a.ID == myID).DeleteAll() -
Sławek
commented
But with syntax like this:
context.Invoices.Where(i => i.Status == (int)InvoiceStatus.Open).Update(i => new { Status = i.Status + 1 });
context.Invoices.Where(i => i.Status == (int)InvoiceStatus.Open).Delete(); -
Kim Tranjan
commented
and DELETE too, please.
-
jredekop
commented
Allow for direct updates to the database using expressions.
Here is an example, but it only works with constants.
A real solution needs to be able to evaluate expressions, which could translate into nested SQL queries.