Some time ago there were many Article This article introduces and analyzes LINQ to SQL. It can be said that it is a simple implementation of ORM, which is a problem that Microsoft has unbalanced impedance between data and objects. C #3.0 introduces some new features, such as extension method, Lambda expression, and anonymous type. In fact, these new features are used to serve LINQ. Program In the past, I was freed from writing a large number of SQL query statements, values, and assignments. I did a lot of work on intelliisense and compilation, so that these queries can be checked during compilation. Microsoft also released the ADO. NET Entity Framework, which is the next generation of ADO. net. It is a more powerful ORM than LINQ to SQL. Developers only need to focus on the development of object models in the domain, rather than how they interact with relational databases.
This article describes how to use Entity Framework by using a simple example. Before that, you must download ADO. NET Entity Framework runtime and tools. Many examples are available on the official website. Next we will officially start the ADO. NET Entity Framework Tour (Development Tool vs 2008 beta, taking the northiwind database as an example ).
Create a console Project (select. NET Framework 3.5), click Add new item, and you will see the options of ADO. NET Entity Data Model:
Then, the wizard appears. Select the database, select the table, view, and stored procedure, and follow the wizard step by step. Here, only the customers and orders tables are selected. Click northwind. edmx in the solution entity e view to view the entity information (which looks like the dbml design view of LINQ to SQL), and rename them customer and order.
Now we can query it. First reference the namespace:
UsingSystem. Data. objects;UsingNorthwindmodel;
First, we query all the customers,CodeIt is very similar to that in LINQ to SQL.
Using(NorthwindentitiesCTX =New Northwindentities()){Foreach(VaRCustomerInCTX. MERs mers ){Console. Writeline (customer. customerid );}}
Query a customer,
CustomerAlfki = CTX. MERs. Where ("It. customerid = 'alfki '"). First ();
"It. customerid = 'alfki' "indicates a query condition statement. The statement looks like a C # code and an SQL statement, and becomes an Entity SQL language, the specific syntax has a detailed reference in the help document.
You can also use LINQ to query (LINQ to entities) and reference the system. LINQ namespace. It seems that Entity Framework does not support the single method and will throw an exception.
CustomerAlfki = CTX. MERs. First <Customer> (IT => it. customerid ="Alfki");
Or
CustomerAlfki = (FromCInCTX. MERsWhereC. customerid ="Alfkia"SelectC). First ();
It is very easy to perform one-to-many queries in Entity Framework. However, it is worth noting that Entity Framework uses lazy load by default, which means associated data is loaded only when necessary.
In this example, If you directly query the orders associated with the customer, the query results are not found.
Foreach(VaROrderInAlfki. Orders ){Console. Writeline (order. orderid );}
You need to call alfki. Orders. Load () before using orders, and run it again to get the correct result.
// ================================================ ========================================================== =
ADO. NET Entity Framework, that is, the next generation of ADO. net. It is a more powerful ORM than LINQ to SQL. Developers only need to focus on the development of object models in the domain, rather than how they interact with relational databases. The previous article briefly introduced how to use the ADO. Net object framework in the project. From now on, it officially entered the learning journey of ADO. net. This article mainly introduces how to query in the ADO. Net object framework (taking the northwind database as an example ).
1. query using entitycommand
In the Entity Framework, we can query by entitycommand, which is like sqlcommand In ADO. net. The difference is that sqlcommand uses standard SQL statements to query databases, while entitycommand uses Entity SQL to query entitycontainer. Of course, the final Entity Framework converts Entity SQL into standard SQL statements to query databases.
Entityconnection Con = New Entityconnection ( "Name = northwindentities" ); Con. open (); Using ( Entitycommand Cmd = New Entitycommand ( "Select value c from northwindentities. MERs as C" , Con )){ Entitydatareader Reader = cmd. executereader (Commandbehavior . Sequentialaccess ); While (Reader. Read ()){ Console . Writeline ( "Id [{0}], contacttitle [{1}]" , Reader [ "Customerid" ], Reader [ "Contacttitle" ]) ;}}
First, create an entityconnection. It accepts a parameter to indicate which connection string is used in the config file.
<Connectionstrings>
<Add name = "northwindentities" connectionstring = "metadata =. \ northwind. CSDL |. \ northwind. SSDL |. \ northwind. MSL; provider = system. data. sqlclient; provider connection string = & quot; Data Source = localhost \ sqlexpress; initial catalog = northwind; Integrated Security = true & quot; "providername =" system. data. entityclient "/>
</Connectionstrings>
The connection string is different from the connection string used in ADO. net. Metadata: Specify the paths of the. CSDL/. SSDL/. MSL files. Provider: sqlclient, oledb, or ODBC is used. Provider connection string: the connection string used in the past. Providername indicates that entityclient is used.
Then construct entitycommand and read data through entitydatareader. It is worth noting that entitycommand accepts Entity SQL statements instead of standard SQL statements, for more information about Entity SQL syntax, see the help documentation.
2. query using objectquery
The Entity Framework provides a class named objectquery, which allows developers to query through Entity SQL. The query results are provided as objects.
Using (Northwindentities CTX = New Northwindentities ()){ Objectquery < Customer > Query = CTX. createquery < Customer > ( "Northwindentities. MERs" ); Objectresult < Customer > Result = query. Execute ( Mergeoption . Notracking );Foreach ( Customer C In Result ){ Console . Writeline ( "Id [{0}], contacttitle [{1}]" , C. customerid, C. contacttitle );}}
First, call the createquery method to create the objectquery object (New can also be used here, but the input parameters are different). It accepts the Entity SQL statement as a parameter. Call the execute Method for query. It accepts parameters of the mergeoption Enumeration type, indicating the method for resolving the conflict. The query results are saved in objectresult as objects (here is the customer.
The following code uses new:
Using(NorthwindentitiesCTX =New Northwindentities()){Objectquery<Customer> Query =New Objectquery<Customer> ("MERs", CTX );Foreach(CustomerCInQuery ){Console. Writeline ("Id [{0}], contacttitle [{1}]", C. customerid, C. contacttitle );}}
3. Ado. NET Entity Framework tool automatically generates entities and code for each object, helping developers reduce a lot of physical activity. In this way, we can simply write it:
Using(NorthwindentitiesCTX =New Northwindentities()){Foreach(CustomerCInCTX. MERs mers ){Console. Writeline ("Id [{0}], contacttitle [{1}]", C. customerid, C. contacttitle );}}
In fact, objectquery is also used for query. Of course, you can add more conditions to the query. in the previous article, it is not described here.
It is worth noting that the automatically generated object class has such a method, such as customer:
Public Static CustomerCreatecustomer (StringCustomerid,StringCompanyName ){CustomerCustomer =New Customer(); Customer. customerid = customerid; customer. companyName = companyName;ReturnCustomer ;}
Instead of providing constructors with parameters, the createcustomer static method is provided to construct the customer instance. This seems to be related to the problem of anemia and congestion in the previous period. Should the object have behavior, anemia or congestion? Although it is only a method, I believe this shows Microsoft's attitude.