Entity Framework 4.1: bypasses EF query ing, entityframework

Entity Framework 4.1: bypasses EF query ing, entityframework

Original article name: Entity Framework 4.1: Bypassing EF query mapping (8)

Address: http://vincentlauzon.wordpress.com/2011/04/21/entity-framework-4-1-bypassing-ef-query-mapping-8/

We can see the English tutorial recommended for Entity Framework 4.1. To help you look more convenient, we can translate it briefly. This is a series of 8 articles, and this is 8th articles.

This is the last article in this series. I will discuss how to bypass the query ing of EF.

Like all excellent frameworks, EF knows that it is not excellent enough to cover all corners. By allowing direct access to the database, EF supports opening the underlying ADO. NET Framework.

Three APIs are supported:

• DbContext. Database. ExecuteSqlCommand
• DbContext. Database. SqlQuery
• DbSet. SqlQuery

The first one is nothing special, just like the typical SqlCommand In ADO. NET.

Public int ExecuteSqlCommand (string SQL, params object [] parameters );

The second one is interesting.

Public IEnumerable <TElement> SqlQuery <TElement> (string SQL, params object [] parameters );

We can use this method to directly send SQL commands to the database, whether Stored Procedures or temporary SQL statements. The difference with ADO. NET is that it can directly convert the data in the DataReader of the query result to an object.

TElement can be any class. It is important that EF does not track returned objects,Even if they are objects of the object type. This is different from the third DbSet. The third method traces the returned objects.

Let's try DbContext. Database. SqlQuery:

Public IEnumerable <SprocReport> GetEntityList ()
{
Return Database. SqlQuery <SprocReport> ("SELECT LegalEntityBaseID, EntityName FROM dbo. LegalEntity ");
}

One best practice is to encapsulate these calls in the derived class of DbContext. The following is the definition of the SprocReport class we use.

Public class SprocReport
{
Public int LegalEntityBaseID {get; set ;}
Public string EntityName {get; set ;}
}

This class is not an entity and attributes are directly mapped:Cannot control ing. Even if you use complex types and overwrite mappings, these overwrites do not work.

See DbSet. sqlQuery, the objects returned by this method will be tracked and modified by EF. Therefore, if you modify these returned entities, when DbContext. when SaveChanges is called, it will be processed. On the other hand, the ing of Columns cannot be overwritten.

Another way to bypass EF ing management is to use Entity SQL. Remember that EF maps the Entity model to the physical model before it is converted to the local underlying data storage (such as TSQL) for query, first, the LINQ query is converted to the object model (through eSQL syntax ).

For example, we can create an object set without defining it in DbContex:

Protected override void OnModelCreating (DbModelBuilder modelBuilder)
{
Base. OnModelCreating (modelBuilder );

ModelBuilder. Entity <SimpleEntry> (). HasEntitySetName ("MyEntry ");
ModelBuilder. Entity <SimpleEntry> (). ToTable ("MyEntry", "man ");
ModelBuilder. Entity <SimpleEntry> ()
. Property (s => s. ID)
. HasColumnName ("SimpleEntryID ");
ModelBuilder. Entity <SimpleEntry> ()
. Property (s => s. Name)
. HasColumnName ("SimpleEntryName ");

}

Then, we can expose the query:

Public IEnumerable <SimpleEntry> GetSimpleEntries ()
{
IObjectContextAdapter adapter = this;
Var entries = adapter. ObjectContext. CreateQuery <SimpleEntry> ("select value MyEntry FROM MyEntry ");

Return entries;
}

Here we use the underlying ObjectContext for query. This method is better than directly sending SQL statements to the database because the SQL statements sent to the database can be queried by using LINQ. Therefore, we can start with a simple query that returns any result, and then apply LINQ to it to get a valid query, without the need to query the entire table on the user side.

To convince ourselves that what I just said is true, let's give it a try.

Public IEnumerable <SimpleEntry> GetSimpleEntries ()
{
IObjectContextAdapter adapter = this;
Var entries = adapter. ObjectContext. CreateQuery <SimpleEntry> ("select value MyEntry FROM MyEntry ");
Var final = from e in entries
Where e. Name = "Mark"
Select e;
Var f = (System. Data. Objects. ObjectQuery <SimpleEntry>) final;
Var s = f. ToTraceString ();

Return entries;
}

If the value of s is output, you can see:

SELECT
[Extent1]. [SimpleEntryID] AS [SimpleEntryID],
[Extent1]. [SimpleEntryName] AS [SimpleEntryName]
FROM [man]. [MyEntry] AS [Extent1]
Where n 'mark' = [Extent1]. [SimpleEntryName]

This is a typical TSQL statement generated by EF. You will notice that the LINQ filter conditions are applied to SQL statements.

Now, if you want to intercept the Insert, Update, and Delete operations of an object, you have to rely on yourself. You need to override DbContext. SaveChanges, get objects in a specific State, implement your own data operation logic to save changes, and switch the state of these entities to Unmodified before calling base. SaveChanges. This can be used, but it is a special technique.

Reference page:

Http://www.yuanjiaocheng.net/ASPNET-CORE/core-view-start.html

Http://www.yuanjiaocheng.net/webapi/webapi-filters.html

Http://www.yuanjiaocheng.net/CSharp/csharp-dynamic-type.html

Http://www.yuanjiaocheng.net/CSharp/Csharp-Exception.html

Http://www.yuanjiaocheng.net/CSharp/csharp-generic-dictionary.html

Http://www.yuanjiaocheng.net/Linq/linq-query-syntax.html

Http://www.yuanjiaocheng.net/ASPNET-CORE/first.html

Http://www.yuanjiaocheng.net/mvc/mvc-helper-textbox.html

Http://www.yuanjiaocheng.net/ASPNET-CORE/core-authorize-attribute.html

Http://www.yuanjiaocheng.net/CSharp/csharp-array.html

Http://www.yuanjiaocheng.net/CSharp/Csharp-sortedlist.html