we have created stored procedures on the SQL Server and customized the entity model so that EF will use those stored procedures in place of dynamic queries.
In this blog, I discuss INSERT in detail. I want to understand how EF handles the following:
IDENTITY Key - How does EF retrieve the server-generated IDENTITY value?
GUID Key - How does EF retrieve the server-generated UNIQUEIDENTIFIER value?
TIMESTAMP Field - It is not part of the primary key, but is server-generated. I use such fields in the data model for the purposes of concurrency control.
Multi-table INSERT - With tables having referential relations, how does EF sequence the INSERTs and sets the generated master key value at the child level?
IDENTITY Keyef.Policy.policyId is an IDENTITY key. In order for EF to retrieve the key value for the just inserted record to update the entity object, we must do the following:
In the stored procedure ef.qp_InsertPolicy, we have a result setSELECT SCOPE_IDENTITY() AS policyId, ...;
In SSDL, under
In MSL, under
Now if we run this Console program using (WORKModel.WORKEntities we = new WORKModel.WORKEntities())
{
try
{
WORKModel.Policy p = new WORKModel.Policy();
p.expDt = DateTime.Parse("2008/09/01");
p.riskState = "AZ";
p.description = "An AZ Policy";
we.AddToPolicies(p);
we.SaveChanges();
Console.WriteLine("Newly Generated policyId: {0}", p.policyId);
Console.ReadLine();
}
catch (Exception e)
{
Console.WriteLine("Exception Occured: {0}", e.Message);
Console.ReadLine();
}
}We get Newly Generated policyId: 43And on the SQL Server, this is executed:exec [ef].[qp_InsertPolicy] @dtExpDt='2008-09-01 00:00:00',@vcRiskState='AZ',@vcDescription='An AZ Policy';Cool! Notice that we did not explicitly set the policyId property of p, but after SaveChanges() p.policyId was updated with the SQL Server's generated IDENTITY key for the newly inserted record. Also note that our stored procedure [ef].[qp_InsertPolicy] was executed instead of some dynamic query.
GUID Keyef.Driver.driverId is the primary key field of type UNIQUEIDENTIFIER and a default constraint settting it to NEWSEQUENTIALID() if NULL. It would be great if EF could treat GUID in the same way as IDENTITY. Unfortunately, since SQL Server does not have the facility to get the newly generated NEWSEQUENTIALID() the same way SCOPE_IDENTITY() works on IDENTITY, MS decides that EF will not support any other server-generated value types than IDENTITY as long as that field is part of the primary key. Seehttp://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2808788&SiteID=1.So the workaround solution is to use the parameterless (default) constructor to generate GUID to set the property (why parameterless constructor? Because it is the most common way to create a new instance. Of course, if the entity class has other (non-default) constructors, we should do the same.) Since Driver (DriverName as well) class is partial, add a new class file WORK.Custom.cs (as opposed to the auto-generated WORK.Designer.cs) which includes the following
public partial class Driver : global::System.Data.Objects.DataClasses.EntityObject
{
public Driver()
{
this.driverId = Guid.NewGuid();
}
}
public partial class DriverName : global::System.Data.Objects.DataClasses.EntityObject
{
public DriverName()
{
this.nameId = Guid.NewGuid();
}
}Here we will only need the constructor for Driver, and add that for DriverName for use in c below. With that, we can run this Console program:
using (WORKModel.WORKEntities we = new WORKModel.WORKEntities())
{
try
{
// Add a driver to an existing policy
WORKModel.Policy p = we.Policies.Include("Drivers")
.Where(w => w.policyId == 43)
.FirstOrDefault();
WORKModel.Driver d = new WORKModel.Driver();
d.age = 40;
d.Policy = p;
we.AddObject(@"Drivers", d); // To use AddObject, we must first set d.Policy as above.
//p.Drivers.Add(d);
we.SaveChanges();
Console.WriteLine("Newly Generated driverId: {0}", d.driverId);
Console.ReadLine();
}
catch (Exception e)
{
Console.WriteLine("Exception Occured: {0}", e.Message);
Console.ReadLine();
}
}We getNewly Generated driverId: edbf5506-d6f6-4946-aa14-0a2e0a71689dAnd this is executed on the SQL Server:exec [ef].[qp_InsertDriver] @uidDriverId='EDBF5506-D6F6-4946-AA14-0A2E0A71689D',@iPolicyId=43,@iAge=40;Ok, our stored procedure is used by EF instead of some dynamic query. The issue with this approach is that the .NET Guid.NewGuid() function does not gurantee the order of the guid generated. In other words, we can not be sure that a guid generated later is larger than that earlier. The implication is that since driverId is a clustered index, inserts with random driverId values will cause framentation. Our SQL NEWSEQUENTIALID() would solve the problem if utilized.
TIMESTAMP FieldEach of tables have a TIMESTAMP field, ts. Whenever a record is inserted/updated, that record's ts value will increase. Its only purpose is for consistency control. We will look at it more when we discuss UPDATE in the next blog. Here for INSERT, after a record is added in the database table, we want to retrieve the ts value to update the entity object. To do that, we must do the following:
In each INSERT stored procedure, we add a column in the result setSELECT SCOPE_IDENTITY() AS policyId, ts FROM ef.TableName WHERE pk = @pk; for each table.
In SSDL, under
In MSL, under
We will see in d. below that the entity object's ts property is updated after the inserts.
Multi-table INSERTRun this Console program:
using (WORKModel.WORKEntities we = new WORKModel.WORKEntities())
{
try
{
// Multi-table inserts
WORKModel.Policy p = new WORKModel.Policy();
p.expDt = DateTime.Parse("2008/09/01");
p.riskState = "AZ";
p.description = "An AZ Policy";
we.AddToPolicies(p);
WORKModel.Driver d = new WORKModel.Driver();
//Use the public constructor instead: WORKModel.Driver.CreateDriver(); // it does not generate PK
d.age = 40;
d.Policy = p;
we.AddObject(@"Drivers", d); // To use AddObject, we must first set d.Policy as above.
//p.Drivers.Add(d);
WORKModel.DriverName dn = new WORKModel.DriverName { Driver = d, effDt = DateTime.Parse("2008/5/1"), lastName = "Wang", firstName = "Ting" };
we.AddObject(@"DriverNames", dn);
we.SaveChanges();
Console.WriteLine("Policy.policyId: {0}; Driver.policyId: {1}; Driver.driverId: {2}; DriverName.driverId: {3}.", p.policyId, d.Policy.policyId, d.driverId, dn.Driver.driverId);
Console.WriteLine("\n");
Console.WriteLine("Policy.ts: {0}; Driver.ts: {1}; DriverName.ts: {2}.", p.ts.ToString1(), d.ts.ToString1(), dn.ts.ToString1());
Console.ReadLine();
}
catch (Exception e)
{
Console.WriteLine("Exception Occured: {0}", e.Message);
Console.ReadLine();
}
}We getPolicy.policyId: 56; Driver.policyId: 56; Driver.driverId: 3eac17c0-2c9c-4e60-b0
f4-98bfc679d01b; DriverName.driverId: 3eac17c0-2c9c-4e60-b0f4-98bfc679d01b.
Policy.ts: 00000089; Driver.ts: 000000810; DriverName.ts: 000000811.
And the following was excutued in order:
exec [ef].[qp_InsertPolicy] @dtExpDt='2008-09-01 00:00:00',@vcRiskState='AZ',@vcDescription='An AZ Policy';
exec [ef].[qp_InsertDriver] @uidDriverId='3EAC17C0-2C9C-4E60-B0F4-98BFC679D01B',@iPolicyId=56,@iAge=40;
exec [ef].[qp_InsertDriverName] @uidNameId='875FC71C-533C-47CB-B84D-BF161B8DCD82',@uidDriverId='3EAC17C0-2C9C-4E60-B0F4-98BFC679D01B',@vcLastName='Wang',@vcFirstName='Ting',@vcMiddleName=NULL,@dtEffDt='2008-05-01 00:00:00';
Nice. policyId generated on the server is retrieved to set p.policyId which in turn is used as a parameter of the driver inserting stored procedure. The ts properties on all three entity objects are updated after insert. We also note that all of our INSERT stored procedures are used.
No comments:
Post a Comment