Entity Framework simple query

First, let's briefly review how to use Code First.

The first step is to first create an application on the console, and then add the Entity Framework through Nuget. The corresponding configuration will be added to packages. config and App. config at the same time.

Step 2: Add a data operation context entity class. Add two constructors and add an object class of Person. Add the corresponding data link configuration in the configuration file of App. config.

The third step is to generate the corresponding database.

EFContext. cs

    public class EFContext:DbContext    {        public EFContext()            : base("EFContext")        { }        public EFContext(string connectionstring)            :base(connectionstring)        {                       }        public DbSet<Person> Persons { get; set; }    }

App. config

<?xml version="1.0" encoding="utf-8"?><configuration>  <configSections>    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />  </configSections>  <connectionStrings>    <add name="EFContext" connectionString="Data Source=.;Database=EFContext;UID=sa;PWD=sa123;" providerName="System.Data.SqlClient"></add>  </connectionStrings>  <startup>    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />  </startup>  <entityFramework>    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">      <parameters>        <parameter value="v11.0" />      </parameters>    </defaultConnectionFactory>  </entityFramework></configuration>

Package. config

<?xml version="1.0" encoding="utf-8"?><packages>  <package id="EntityFramework" version="5.0.0" targetFramework="net45" /></packages>

Then a simple entity class is added.

    public class Person    {        public int PersonId { get; set; }        public string PersonName { get; set; }        public int Age { get; set; }        public string Address { get; set; }        public string Email { get; set; }    }

Final call

        static void Main(string[] args)        {            using (var db = new EFContext("EFContext"))            {                var persons = db.Persons.Where(t => t.PersonName == "aehyok").OrderByDescending(t => t.PersonId).ToList();                foreach (var p in persons)                {                    Console.WriteLine("The PersonName is {0} and Age {1}", p.PersonName, p.Age);                }            }            Console.ReadLine();        }

No data is displayed on the console after running, but the corresponding database EFContext and data table People can be viewed in the database.

Now, the database directly adds several pieces of data to the People table in the EFContext database created above.

 

Then run the program again. You can find data.

Now we can see that our first short answer query statement has been implemented.

The lifecycle of a database context starts with the creation of the object and ends with the release of the object (or GC collection). Therefore, we recommend that you use the "Using" encoding method during development, this removes the need to manually release objects. In addition, the management of database connections is transparent in EF, And we generally do not need to handle it manually, when an object is queried, the connection is opened. When the query result set is processed, the connection is automatically closed.

A query expression is a new feature of C #3.0 that consists of a group of statements similar to T-SQL or XQuery declarative statements, CLR does not directly read this query expression but converts it to the corresponding method call during compilation. Example:

            using (var db = new EFContext("EFContext"))            {                var persons = from p in db.Persons                              where p.PersonName == "aehyok"                              orderby p.PersonId descending                              select p;                foreach (var p in persons)                {                    Console.WriteLine("The PersonName is {0} and Age {1}", p.PersonName, p.Age);                }            }            Console.ReadLine();

The result is the same as above.

Method-based query is actually an extension method of a group of objects. Different from the method-based query, these methods can be directly recognized and run by CLR.

For example, we can convert the above method to the following code. The results are the same. The returned results are all "IQueryable" objects. The code here is actually the code we started to test the database creation.

            using (var db = new EFContext("EFContext"))            {                var persons = db.Persons.Where(t => t.PersonName == "aehyok").OrderByDescending(t => t.PersonId).ToList();                foreach (var p in persons)                {                    Console.WriteLine("The PersonName is {0} and Age {1}", p.PersonName, p.Age);                }            }            Console.ReadLine();

Of course, the execution results are the same.

EF also supports native SQL queries such:

            using (var db = new EFContext("EFContext"))            {                var persons = db.Persons.SqlQuery("select * from EFContext..People where PersonName='aehyok'");                foreach (var p in persons)                {                    Console.WriteLine("The PersonName is {0} and Age {1}", p.PersonName, p.Age);                }            }            Console.ReadLine();

You can directly use the concatenation of SQL statements. Of course, this is just the simplest example.

In addition, EF supports non-entity-type queries:

            using (var db = new EFContext("EFContext"))            {                var persons = db.Database.SqlQuery<string>("select PersonName from EFContext..People where PersonName='aehyok'");                foreach (var p in persons)                {                    Console.WriteLine("The PersonName is {0} ", p);                }            }

 

Of course, SQL commands without return values are also supported:

Using (var db = new EFContext ("EFContext") {var persons = db. database. executeSqlCommand ("update EFContext .. people set Address = 'China' where PersonName = 'aehyok '");}