They’re great for retrieving data, but when it comes to making updates they often lose efficiency.
A few years ago, convinced that there must be a better way, I spent some time searching online for anyone who had solved this problem.
Using the Mapping API, it is possible to write extension methods similar to those provided by Entity Framework. In the ideal situation you would not have to rely on reflection at all, however there are certain parts of this which I was unable to find a way to accomplish without still relying on reflection – in particular, getting at the underlying SQL statement of the query, including parameters and values.
In this article, I will focus on Delete(…), as that is the simpler of the two.
That internal type has an Object Query property, which is (not surprisingly) of type Object Query.
The internal implementation of Delete calls the Entity Framework Mapping API to determine the table name and primary key, and uses a little bit of reflection to get the SQL statement and parameters for the From/Join part of the query.
The overhead of this is a little bit more than if I were using ADO. In this case I have to execute one database call to retrieve the product, and one database call to update the data. Using only the built-in Entity Framework functionality, I would have to do something like this: This LOOKS pretty simple – I’m iterating through the context. Behind the scenes, Entity Framework is executing one SQL ‘select’ call to retrieve all the products, but when you call Save Changes, it is executing one SQL ‘update’ call .
At the time, I was using Entity Framework 5, and referenced a third party library called “Entity Framework. That library still works up to and including Entity Framework 6.0.
Note: This is not a very good example from the standpoint of keeping the Adventure Works database clean and correct, we are only interested in meeting each of the SQL Constraints, not the business logic. Creating a new record keeps with the mind set of working with objects, so you simply create a new object of the appropriate record type and set its properties.
will wrap up all of the changes you have made to the database and submit them as a single transaction, this is good news because if any of them changes fail, they all fail. When linking two records together, you have two choices, both of which I have given an example of.
The next article will focus on Update, and the third article in the series will cover two different types of bulk inserts.
All of the public methods presented here are extension methods on either is really a Db Query , it has a private Internal Query property which is an internal type used by LINQ.
For purposes of this article I will assume you have a basic working knowledge of LINQ, LINQ to SQL, or LINQ to Entities.