Entity Framework 6 Recipes Chinese translation series (14), entityframework

Entity Framework 6 Recipes Chinese translation series (14), entityframework

For the original intention of translation and why I chose Entity Framework 6 Recipes, see the beginning of this series.

3-6 set the default value in the query

Problem

　　You have a use case that sets the default value for the corresponding property when the query returns a null value. In our example, when the database returns a null value, '0' is used as the default value of the YearsWorked attribute.

Solution

Suppose you have a model 3-7, and you want to query employees through the model. In a database, a table representing employees contains an empty YearsWorked column. This column is mapped to the YearsWorked attribute in the Employee object. You want to set the YearsWorked with null values in the return row to the default value 0.

Figure 3-7 contains a model of the Employee object type. The object type includes an EmployeeId attribute, a Name attribute, and a YearsWorked attribute.

 

The example uses code-first in the Entity Framework. In code listing 3-11, we created an Employee class.

Code List 3-11.Employee entity class

1   public class Employee2     {3         public int EmployeeId { get; set; }4         public string Name { get; set; }5         public int? YearsWorked { get; set; }6     }

Next, create a context object in code list 3-12.

 1  public class EFRecipesEntities : DbContext 2     { 3         public EFRecipesEntities() 4             : base("ConnectionString") {} 5  6         public DbSet<Employee> Employees { get; set; } 7  8         protected override void OnModelCreating(DbModelBuilder modelBuilder) 9         {10             modelBuilder.Entity<Employee>().ToTable("Chapter3.Employee");11             base.OnModelCreating(modelBuilder);12         }13     }

 

Because we use the Code-First method, we can set the default value as shown in Code list 3-13. Note that the method in code listing 3-13 cannot be implemented (returned from the database) by default for an instance of the Employee entity type. On the contrary, the query result is a set of anonymous types. When the value of the YearsWorked column in the database table is null, the anonymous type attribute YearsWorked is set to 0 programmatically. therefore, the corresponding columns in the database still retain the null value, but we use 0 as its default value in the object Framework result set. Remember, the anonymous type shown in code listing 3-13 is a class dynamically created at runtime Based on the attributes in braces following the new keyword.

Code List 3-13.Use LINQ and Entity SQL to fill the default value with Null values

1 using (var context = new EFRecipesEntities () 2 {3 // Delete the previous test data 4 context. database. executeSqlCommand ("delete from chapter3.employee"); 5 // Add new test data 6 context. employees. add (new Employee 7 {8 Name = "Robin Rosen", 9 YearsWorked = 310}); 11 context. employees. add (new Employee {Name = "John Hancock"}); 12 context. saveChanges (); 13} 14 15 using (var context = new EFRecipesEntities () 16 {17 Console. wri TeLine ("Employees (using LINQ)"); 18 var employees = from e in context. Employees19 select new {Name = e. Name, YearsWorked = e. YearsWorked ?? 0}; 20 foreach (var employee in employees) 21 {22 Console. writeLine ("{0}, years worked: {1}", employee. name, 23 employee. yearsWorked); 24} 25} 26 27 using (var context = new EFRecipesEntities () 28 {29 Console. writeLine ("\ nEmployees (using ESQL w/named constructor)"); 30 var esql = @ "select value Recipe3_6.Employee (e. employeeId, 31 e. name, 32 case when e. yearsWorked is null then 033 else e. yearsWorked end) 34 from Employees as e "; 35 36 37 var employees = (IObjectContextAdapter) context ). objectContext. createQuery <Employee> (esql); 38 foreach (var employee in employees) 39 {40 Console. writeLine ("{0}, years worked: {1}", employee. name, 41 employee. yearsWorked. toString (); 42} 43} 44 45 Console. writeLine ("\ nPress <enter> to continue... "); 46 Console. readLine ();

The output of code listing 3-13 is as follows:

Employees (using LINQ)Robin Rosen, years worked: 3John Hancock, years worked: 0Employees (using ESQL w/named constructor)Robin Rosen, years worked: 3John Hancock, years worked: 0

 

Principle

The method we use here is to use LINQ and eSQL to project the results to an anonymous set. When YearsWorked is null in the underlying database, the query sets it to 0.

In the LINQ method, we use the null-coalescing operator in C ??, When the value of YearsWorded in the database is null, 0 is allocated to it. The result is projected into an anonymous set.

In the Entity SQL method, when the value of YearsWorded in the database is null, we use the case statement to allocate 0 to YearsWorked. Here we demonstrate how to use Entity SQL to instantiate an instance of the Employee Entity type without setting the default value. To this end, we use the named constructor of the object type ). This constructor assigns values to attributes from parameters using the order defined by attributes in the object type. In our example, the attributes of the Employee object are defined in the following order: EmployeeId, Name, and YearsWorked. The order passed to the constructor parameters from the eSQL query is as follows. Unfortunately, no proper naming constructor syntax is supported in LINQ to Entiytes.

 

 

3-7 return multiple result counters from the Stored Procedure

Problem

　　You have a stored procedure that returns multiple result sets. You want to materialized from each result set to the entity instance.

 

Solution

Suppose you have a model 3-8 and a stored procedure 3-14 in the code list. The stored procedure returns a set of jobs and bids.

Figure 3-8 a code job and bid Model

 

Code List 3-14.Stored Procedures that return multiple result sets

1 create procedure Chapter3.GetBidDetails2 as3 begin4 select * from Chapter3.Job5 select * from Chapter3.Bid6 end

 

In our model, each job has zero or multiple bids. Our stored procedure returns all jobs and bids. We want to execute the stored procedure and instantiate all jobs and bids in the two result sets. This requirement is implemented by code listing 3-15.

Code List 3-15.Instantiate Jobs and Bids from multiple result sets returned by the Stored Procedure

 1  using (var context = new EFRecipesEntities()) 2             { 3                 var job1 = new Job {JobDetails = "Re-surface Parking Log"}; 4                 var job2 = new Job {JobDetails = "Build Driveway"}; 5                 job1.Bids.Add(new Bid {Amount = 948M, Bidder = "ABC Paving"}); 6                 job1.Bids.Add(new Bid {Amount = 1028M, Bidder = "TopCoat Paving"}); 7                 job2.Bids.Add(new Bid {Amount = 502M, Bidder = "Ace Concrete"}); 8                 context.Jobs.Add(job1); 9                 context.Jobs.Add(job2);10                 context.SaveChanges();11             }12 13             using (var context = new EFRecipesEntities())14             {15                 var cs = @"Data Source=.;Initial Catalog=EFRecipes;Integrated Security=True";16                 var conn = new SqlConnection(cs);17                 var cmd = conn.CreateCommand();18                 cmd.CommandType = System.Data.CommandType.StoredProcedure;19                 cmd.CommandText = "Chapter3.GetBidDetails";20                 conn.Open();21                 var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);22                 var jobs = ((IObjectContextAdapter) context).ObjectContext.Translate<Job>(reader, "Jobs",23                     MergeOption.AppendOnly).ToList();24                 reader.NextResult();25                 ((IObjectContextAdapter) context).ObjectContext.Translate<Bid>(reader, "Bids", MergeOption.AppendOnly)26                     .ToList();27                 foreach (var job in jobs)28                 {29                     Console.WriteLine("\nJob: {0}", job.JobDetails);30                     foreach (var bid in job.Bids)31                     {32                         Console.WriteLine("\tBid: {0} from {1}",33                             bid.Amount.ToString(), bid.Bidder);34                     }35                 }36 37                 Console.WriteLine("\nPress <enter> to continue...");38                 Console.ReadLine();39             }

Output from code list 3-15 is as follows:

Job: Re-surface Parking LogBid: $948.00 from ABC PavingBid: $1,028.00 from TopCoat PavingJob: Build DrivewayBid: $502.00 from Ace Concrete

 

Principle

At the beginning, I added two jobs and some bids, and then added them to the context. The SaveChanges () function was recently called to save it to the database.

Entity Framework 5.0 has already provided support for returning multiple result sets for stored procedures. To use this function, you must use the legacy ObjectContext object because the latest DbContext object does not provide direct support for this. To solve this problem, we use SqlClient to read the returned results of the stored procedure. In this mode, you need to create SqlConnection and SqlCommand. Set the name of the stored procedure to the command text of SqlCommand, and call the ExecuteReader () method to obtain a DataReader object.

With the reader object, we can use the Translate () method in the ObjectContext object to instantiate the Job object from the reader object. This method requires the following three parameters: reader, object set name, and merge option. The entity set name is required because an entity may have a result set containing multiple entity sets. The Entity Framework needs to know which entity set you want to use.

The merge option has some notes. We use the MergeOption. AppendOnly option to add the object instance to the context object and track it. The reason for using this option is to allow the Entity Framework to automatically associate jobs and bids. To achieve this, as long as jobs and bids are simply added to the context, the Entity Framework will automatically associate them. This saves us a lot of redundant code.

　　Another simple version of method Translate (), which does not require MergeOption. It will leave the context object to instantiate the object. The methods for these two versions are slightly different. objects created outside the context object will not be tracked.. If you use this simple version's Translate () method to read jobs, you will not be able to instantiate a new bits in the context. Because the Entity Framework does not reference any jobs associated objects. These jobs are created outside the context object.. In addition, you cannot modify the attributes of these instances and expect the Entity Framework to help you save these changes.

We use the ToList () method to forcibly enumerate each query. This is because the Translate () method returns ObjextResult <T>, which does not actually read results from reader. I need to force read results from reader before using the NextResult () method to process the next result set. In practice, most of us will use the NextResult () method in the code to continue searching for the result set returned by the stored procedure.

Although we didn't see it in this example, it should be noted that,The Translate () method bypasses the ing layer model. If you want to use an inheritance ing or an object that contains a composite property, the Translate () method fails.. The Translate () method requires the DbDataReader object to provide columns that match the object attributes.The matching process simply uses the name. If a column name cannot match an attribute, the Translate () method also fails.

 

 

Entity Framework exchange QQ group: 458326058. You are welcome to join us.

Thank you for your continued attention, my blog address: http://www.cnblogs.com/VolcanoCloud/