Entity Framework Code First uses DbContext to query, entitydbcontext

Entity Framework Code First uses DbContext to query, entitydbcontext

DbContext, DbSet, and DbQuery are three new classes introduced by Entity Framework Code First. DbContext is used to maintain database Session connections, track and save object changes, dbSet is used to track changes in the object class, And DbQuery is used to provide queries with you.

　　1. Use Set to query all records

To use DbContext for query, you must first ensure that the instances of DbContext release the resources after use. There are two ways to release the resources of the DbContext instance: using the using code block structure and calling the Dispose () of the DbContext instance () method.

using (var ctx = new PortalContext()){    foreach (var province in ctx.Provinces)    {        Console.WriteLine(province.ProvinceName);    }}

using (var ctx = new PortalContext()){    foreach (var province in ctx.Set<Province>())    {        Console.WriteLine(province.ProvinceName);    }}

　　2. sort and filter data using LINQ.

1>. sort by using LINQ

Sort by a LINQ expression:

using (var ctx = new PortalContext()){    var provinces = from p in ctx.Provinces                    orderby p.ProvinceNo                    select p;    foreach (var province in provinces)    {        Console.WriteLine(province.ProvinceName);    }}

Multi-field sorting in a LINQ expression:

using (var ctx = new PortalContext()){    var provinces = from p in ctx.Provinces                    orderby p.ProvinceNo descending, p.ProvinceName ascending                    select p;    foreach (var province in provinces)    {        Console.WriteLine(province.ProvinceName);    }}

Sort by the LINQ extension method:

using (var ctx = new PortalContext()){    var provinces = ctx.Provinces        .OrderBy(p => p.ProvinceNo);    foreach (var province in provinces)    {        Console.WriteLine(province.ProvinceName);    }}

The following describes how to sort multiple fields in the LINQ extension method:

var provinces = ctx.Provinces    .OrderByDescending(p => p.ProvinceNo)    .ThenBy(p => p.ProvinceName);foreach (var province in provinces){    Console.WriteLine(province.ProvinceName);}

Note: When multiple OrderBy fields are sorted by using the LINQ extension method, only the last OrderBy field is sorted.

In the following example, only the ProvinceName is sorted in ascending order:

var provinces = ctx.Provinces    .OrderByDescending(p => p.ProvinceNo)    .OrderBy(p => p.ProvinceName);

2>. Filter by using LINQ

Filter by using a LINQ expression:

using (var ctx = new PortalContext()){    var cities = from c in ctx.Cities                 where c.ProvinceID == 3                 select c;    foreach (var city in cities)    {        Console.WriteLine(city.CityName);    }}

Multi-field filtering using a LINQ expression:

using (var ctx = new PortalContext()){    var cities = from c in ctx.Cities                 where c.ProvinceID == 3 && c.CityID > 10                 select c;    foreach (var city in cities)    {        Console.WriteLine(city.CityName);    }}

Filtering and sorting of LINQ expressions:

using (var ctx = new PortalContext()){    var cities = from c in ctx.Cities                 where c.ProvinceID == 3 && c.CityID > 10                 orderby c.CityID                 select c;    foreach (var city in cities)    {        Console.WriteLine(city.CityName);    }}

Filtering by using the LINQ extension method:

using (var ctx = new PortalContext()){    var cities = ctx.Cities        .Where(c => c.ProvinceID == 3);    foreach (var city in cities)    {        Console.WriteLine(city.CityName);    }}

Multi-field filtering using the LINQ extension method:

using (var ctx = new PortalContext()){    var cities = ctx.Cities        .Where(c => c.ProvinceID == 3 && c.ProvinceID > 10);    foreach (var city in cities)    {        Console.WriteLine(city.CityName);    }}

Multi-field filtering and sorting in the linq extension method:

var cities = ctx.Cities    .Where(c => c.ProvinceID == 3 && c.ProvinceID > 10)    .OrderBy(c=>c.CityID);

3>. Select the read field for LINQ.

One field is read by a LINQ expression:

var cities = from c in ctx.Cities             where c.ProvinceID == 3 && c.CityID > 10             select c.CityName;

To read multiple fields using a LINQ expression:

var cities = from c in ctx.Cities             where c.ProvinceID == 3 && c.CityID > 10             select new { c.CityID, c.CityName };

The LINQ Extension Method reads one field:

using (var ctx = new PortalContext()){    var citieNames = ctx.Cities        .Where(c => c.ProvinceID == 3 && c.ProvinceID > 10)        .OrderBy(c => c.CityID)        .Select(c => c.CityName);    foreach (var cityName in citieNames)    {        Console.WriteLine(cityName);    }}

The LINQ Extension Method reads multiple fields:

using (var ctx = new PortalContext()){    var cities = ctx.Cities        .Where(c => c.ProvinceID == 3 && c.ProvinceID > 1)        .OrderBy(c => c.CityID)        .Select(c => new { c.CityID, c.CityName });    foreach (var c in cities)    {        Console.WriteLine("{0}-{1}", c.CityID, c.CityName);    }}

　　3. query local data

Entity Framework Code First queries Local data through the Local attribute of DbSet and queries usage of Local data: 1> when the data to be queried already exists and is in memory, instead of sending SQL statements to the database again for query; 2> when the latest data is still in the memory but not submitted to the database, the memory data is queried.

Example: memory data is not loaded locally at the beginning

using (var ctx = new PortalContext()){    var count = ctx.Cities.Local.Count;    Console.WriteLine("Cities in memory:{0}", count);}

Result returned after execution:

Cities in memory: 0

Example: load data to the memory by querying

using (var ctx = new PortalContext()){    foreach (var city in ctx.Cities)    {        Console.WriteLine(city.CityName);    }    var count = ctx.Cities.Local.Count;    Console.WriteLine("Cities in memory:{0}", count);}

Result returned after execution:

......

Cities in memory: 342

3.2> Load the data to the memory

using (var ctx = new PortalContext()){    ctx.Cities.Load();    var count = ctx.Cities.Local.Count;    Console.WriteLine("Cities in memory:{0}", count);}

Result After execution:

Cities in memory: 342

using (var ctx = new PortalContext()){    var expr = from c in ctx.Cities               select c;    expr.Load();    var count = ctx.Cities.Local.Count;    Console.WriteLine("Cities in memory:{0}", count);}

3.2> query based on local data

using (var ctx = new PortalContext()){    ctx.Cities.Load();    var cities = from c in ctx.Cities.Local                orderby c.ProvinceID                select c;    foreach (var city in cities)    {        Console.WriteLine("{0}", city.CityName);    }}

Clear memory data:

ctx.Cities.Local.Clear();

　　4. query a single object

DbContext API can use DbSet. Find to query and return a single object. The parameter value accepted by DbSet. Find is the primary key value to be queried. If no matching primary key value is found, null is returned.

Find rules for finding a single object:

1> Find an existing entity loaded from the database or an entity attached to DbContext from the memory;

2> Search for the newly added object that has not been submitted and saved to the database;

3> Search for entity objects not loaded into memory in the database.

using (var ctx = new PortalContext()){    var city = ctx.Cities.Find(1);    if (city != null)    {        Console.WriteLine(city.CityName);    }    else    {        Console.WriteLine("City not found!");    }}

If the object class has multiple primary keys, the Find method also accepts the parameter values of all primary keys. The Parameter order must be the same as that of the primary key column.

var city = ctx.Cities.Find(keyID, cityID);

The Single method can also be used to return a Single object after query:

using (var ctx = new PortalContext()){    var expr = from c in ctx.Cities                where c.CityID == 1                select c;    var city = expr.Single();}

The SingleOrDefault method returns a single object:

using (var ctx = new PortalContext()){    var expr = from c in ctx.Cities                where c.CityID == 1                select c;    var city = expr.SingleOrDefault();    if (city != null)    {        Console.WriteLine(city.CityName);    }    else    {        Console.WriteLine("City not found!");    }}

The difference between the Single and SingleOrDefault methods is that when a Single entity that meets the conditions does not exist, Single directly throws an exception, while SingleOrDefault returns the default value of class type null.

Reference page: http://qingqingquege.cnblogs.com/p/5933752.html