How can we improve Entity Framework Core?

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

315 votes
Vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    JohnJohn shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    16 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • Yves GoergenYves Goergen commented  ·   ·  Flag as inappropriate

        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.

      • markmark commented  ·   ·  Flag as inappropriate

        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 AngasAlex Angas commented  ·   ·  Flag as inappropriate

        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 MJordan M commented  ·   ·  Flag as inappropriate

        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 DoeJohn Doe commented  ·   ·  Flag as inappropriate

        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.

      • AnonymousAnonymous commented  ·   ·  Flag as inappropriate

        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.

      • Paweł SawiczPaweł Sawicz commented  ·   ·  Flag as inappropriate

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

      • cstaleycstaley commented  ·   ·  Flag as inappropriate

        And also:

        Cars.Delete(IEnumerable<int> ids)

        Or building on Keith's suggestion:

        Cars.Where(car => enumerableIds.Contains(car.Id)).Delete()

      • James D. SchwarzmeierJames D. Schwarzmeier commented  ·   ·  Flag as inappropriate

        I agree fully. There are a number of ideas out there very similar to this:

        http://data.uservoice.com/forums/72025-ado-net-entity-framework-ef-feature-suggestions/suggestions/1935605-addobject-or-attach-should-not-add-or-attach-whole

        http://data.uservoice.com/forums/72025-ado-net-entity-framework-ef-feature-suggestions/suggestions/2435952-support-a-removeall-command-that-does-not-require-

        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 relationship

        Ideally 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 BarrowsKeith Barrows commented  ·   ·  Flag as inappropriate

        Or maybe:
        context.tableName.Where(a => a.ID == myID).Delete()
        context.tableName.Where(a => a.ID == myID).DeleteAll()

      • SławekSławek commented  ·   ·  Flag as inappropriate

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

      Feedback and Knowledge Base