Creating a new entity instance

This section discusses how to create a new entity and save it to the database and how to modify an existing entity and persist the changes. To create the entity in the persistent storage, two things have to be done: 1) the entity's data (which is new) has to be stored in the new entity object and 2) the entity data has to be persisted / saved in the persistent storage. Let's add the customer Foo Inc. to the database, do the following. First we'll instantiate an empty entity object, in this case a new Customer, and then fill it with data, after which we'll save it with the Save method.

var customer = new CustomerEntity();
customer.CustomerID = "FOO";
customer.Address = "1, Bar drive";
customer.City = "Silicon Valey";
customer.CompanyName = "Foo Inc.";
customer.ContactName = "John Coder";
customer.ContactTitle = "Owner";
customer.Country = "USA";
customer.Fax = "(604)555-1233";
customer.Phone = "(604)555-1234";
customer.PostalCode = "90211";
// save it
customer.Save();

Customer has another field, Region, which isn't given a value. Region can be NULL and will end up as NULL in the database as it has no value in the entity saved.

This will save the data directly to the persistent storage (database). The entity class instance customer itself is marked 'out of sync', which means that the entity's data is refetched from the database when you try to read one of the object's field's value.

This way, you can immediately refetch values which are set inside the database, e.g. default values for columns.

Sequences / Identity value refetch

The code is aware of sequences / identity columns and will automatically set the value for an identity / sequence column after the entity is physically saved inside Save(). The new value for sequenced columns is available to you after Save(), even though you haven't specified that the entity has to be refetched.

Because the entity saved is new (customer.IsNew is true), Save() will use an INSERT query. After a successful save, the IsNew flag is set to false and the State property of the Fields object of the saved entity is set to EntityState.Fetched (if the entity is also refetched) or EntityState.OutOfSync.

Inserting entities based on a query: InsertMulti

To insert new entities, the framework offers a route to insert the rows of a query's resultset as new entities, using the entity collection method InsertMulti. This method accepts a QuerySpec query and the type the new entities have to be.

InsertMulti creates a INSERT INTO table SELECT ... command. The SELECT ... portion is defined by a QuerySpec query and it's not required to specify fields from the entity type to insert; as long as the alias of the field is found as a field in the entity, it's accepted.

Limitations

The InsertMulti method has the following restrictions:

• No inheritance support. The target entity can’t be in an inheritance hierarchy.
• The SQL query can’t have CTE’s, nor paging directives, nor prefetch paths.
• The select query is defined using queryspec; Linq or the low level API aren't supported.
• The insert query can’t be batched.
• The query spec query can’t contain nested queries
• The runtime doesn’t support database specific clauses like ON DUPLICATE UPDATE at this time.

Supported databases

InsertMulti is supported on all supported databases. (This excludes IBM DB2)

Fields which are inserted

The engine produces an INSERT command based on the projection of the QuerySpec query. This means that if only a couple of fields of the entity are specified in the projection of the query, the INSERT command will contain only these fields.

Sequenced / Identity fields

If the entity has a sequenced field or an identity field and the field isn't present in the query's projection, the engine will insert the required sequence SQL into the projection to make sure the field receives the next value for the sequence specified. If the field is an identity field, only if the database requires a sequence specification it's inserted into the projection.

This means you don't need to specify a sequenced/identity field in the query to make it receive new values. If you want to insert existing values for a sequenced/identity field, you have to specify it in the projection. The engine will make sure the proper SQL statements are added to insert values into an identity field, if applicable.

Computed/readonly fields

If you specify a value for a computed/readonly field in the entity, it's removed from the projection in the generated INSERT command.

Projection helpers

To avoid typing a long list of fields in the projection, QuerySpec has specific projection definitions to specify all fields needed for the entity based on some characteristics. This is done by using the Projection class and its static members:

• Full will generate all fields of the entity
• FullNoPrimaryKey will generate all fields of the entity but not the field(s) which are in the primary key
• NonNullable will generate all non-nullable fields of the entity
• NonNullableNoPrimaryKey will generate all non-nullable fields of the entity but not the fields which are in the primary key

Unit of Work

The Unit of Work class UnitOfWork has a method to schedule insert entities directly calls: AddInsertMultiCall. The work added through this method is scheduled after regular inserts present in the Unit of Work and before any other calls added to the Unit of Work (e.g. for deleting entities directly).

Auditing

• The method RequiresTransactionForAuditEntities of the AuditorBase class receives the value DirectInsertEntities for the SingleStatementQueryAction typed input parameter.
• The method AuditDirectInsertOfEntities in the AuditorBase class receives the type of the target entity, the full QuerySpec select query and the number of rows which were inserted. This method has a protected virtual equivalent in EntityCore: OnAuditDirectInsertOfEntities.

Authorization

• The method CanBatchInsertEntitiesDirectly in the AuthorizerBase class receives the type of entity to be inserted. This method has a protected virtual equivalent in EntityCore: OnCanBatchInsertEntitiesDirectly. Use this method to deny inserts directly.

Examples

Full projection specification

The following query writes out the complete projection with all fields to insert. The query defines a projection using SalesOrderHeader entity fields however it inserts new rows of the InsertsSoh Entity. This illustrates that it's ok if the fields in the projection are from another entity; The only requirement is that the name of a field (or alias, in case it's aliased) in the projection is found as a field in the entity to insert.

var qf = new QueryFactory();
var q = qf.SalesOrderHeader
          .Where(SalesOrderHeaderFields.CustomerId == 1)
          .Select(SalesOrderHeaderFields.SalesOrderId, SalesOrderHeaderFields.RevisionNumber, SalesOrderHeaderFields.OrderDate, SalesOrderHeaderFields.DueDate,
                  SalesOrderHeaderFields.Status, SalesOrderHeaderFields.OnlineOrderFlag, SalesOrderHeaderFields.CustomerId, SalesOrderHeaderFields.ContactId,
                  SalesOrderHeaderFields.BillToAddressId, SalesOrderHeaderFields.ShipToAddressId, SalesOrderHeaderFields.ShipMethodId, SalesOrderHeaderFields.SubTotal,
                  SalesOrderHeaderFields.TaxAmt, SalesOrderHeaderFields.Freight, SalesOrderHeaderFields.ModifiedDate);
var collection = new InsertsSohCollection();
var result = collection.InsertMulti(q);

SET IDENTITY_INSERT [AdventureWorksUnitTests].[dbo].[Inserts_SOH] ON;

INSERT INTO [AdventureWorksUnitTests].[dbo].[Inserts_SOH]
            ([SalesOrderID],
             [RevisionNumber],
             [OrderDate],
             [DueDate],
             [Status],
             [OnlineOrderFlag],
             [CustomerID],
             [ContactID],
             [BillToAddressID],
             [ShipToAddressID],
             [ShipMethodID],
             [SubTotal],
             [TaxAmt],
             [Freight],
             [ModifiedDate])
SELECT [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[SalesOrderID]    AS [SalesOrderId],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[RevisionNumber],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[OrderDate],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[DueDate],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[Status],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[OnlineOrderFlag],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[CustomerID]      AS [CustomerId],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[ContactID]       AS [ContactId],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[BillToAddressID] AS [BillToAddressId],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[ShipToAddressID] AS [ShipToAddressId],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[ShipMethodID]    AS [ShipMethodId],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[SubTotal],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[TaxAmt],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[Freight],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[ModifiedDate]
FROM   [AdventureWorksUnitTests].[Sales].[SalesOrderHeader]
WHERE  (([AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[CustomerID] = @p1));

SET IDENTITY_INSERT [AdventureWorksUnitTests].[dbo].[Inserts_SOH] OFF 

Projection shortcut

The following query does the same as the query above, but defines the projection using the Projection.Full directive. It's required that the fields defined in the query, produced by Projection.Full have the same names as the fields in the entity to insert.

var qf = new QueryFactory();
var q = qf.SalesOrderHeader
          .Where(SalesOrderHeaderFields.CustomerId == 1)
          .Select(Projection.Full);
var collection = new InsertsSohCollection();
var result = collection.InsertMulti(q);

SET IDENTITY_INSERT [AdventureWorksUnitTests].[dbo].[Inserts_SOH] ON;

INSERT INTO [AdventureWorksUnitTests].[dbo].[Inserts_SOH]
            ([AccountNumber],
             [BillToAddressID],
             [Comment],
             [ContactID],
             [CreditCardApprovalCode],
             [CreditCardID],
             [CurrencyRateID],
             [CustomerID],
             [DueDate],
             [Freight],
             [ModifiedDate],
             [OnlineOrderFlag],
             [OrderDate],
             [PurchaseOrderNumber],
             [RevisionNumber],
             [rowguid],
             [SalesOrderID],
             [SalesPersonID],
             [ShipDate],
             [ShipMethodID],
             [ShipToAddressID],
             [Status],
             [SubTotal],
             [TaxAmt],
             [TerritoryID])
SELECT [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[AccountNumber],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[BillToAddressID] AS [BillToAddressId],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[Comment],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[ContactID]       AS [ContactId],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[CreditCardApprovalCode],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[CreditCardID]    AS [CreditCardId],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[CurrencyRateID]  AS [CurrencyRateId],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[CustomerID]      AS [CustomerId],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[DueDate],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[Freight],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[ModifiedDate],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[OnlineOrderFlag],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[OrderDate],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[PurchaseOrderNumber],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[RevisionNumber],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[rowguid]         AS [Rowguid],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[SalesOrderID]    AS [SalesOrderId],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[SalesPersonID]   AS [SalesPersonId],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[ShipDate],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[ShipMethodID]    AS [ShipMethodId],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[ShipToAddressID] AS [ShipToAddressId],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[Status],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[SubTotal],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[TaxAmt],
       [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[TerritoryID]     AS [TerritoryId]
FROM   [AdventureWorksUnitTests].[Sales].[SalesOrderHeader]
WHERE  (([AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[CustomerID] = @p1));

SET IDENTITY_INSERT [AdventureWorksUnitTests].[dbo].[Inserts_SOH] OFF 

Overruling sequence values using Unit of Work.

The following query defines 2 as constant for the field SetKey. It's aliased as SetKey in the projection and therefore will be picked up as the source for the SetKey field in the entity to insert.

The call is done through a unit of work object.

// source rows have id's with 1-4. 
var qf = new QueryFactory();
var q = qf.SequenceInsert.Where(SequenceInsertFields.SetKey == 1)
          .Select(SequenceInsertFields.Id, 
                  SequenceInsertFields.Name, 
                  Functions.Constant(2).As("SetKey"));
var collection = new InsertsSiCollection();
var uow = new UnitOfWork();
uow.AddInsertMultiCall(collection, q);
// ... other work added to the uow...
var result = uow.Commit(new Transaction(IsolationLevel.ReadCommitted, "UOW TRANS"), true);

INSERT INTO [AdventureWorksUnitTests].[dbo].[Inserts_SI]
            ([Id],
             [Name],
             [SetKey])
SELECT [AdventureWorksUnitTests].[dbo].[SequenceInserts].[Id],
       [AdventureWorksUnitTests].[dbo].[SequenceInserts].[Name],
       @p2 AS [SetKey]
FROM   [AdventureWorksUnitTests].[dbo].[SequenceInserts]
WHERE  (([AdventureWorksUnitTests].[dbo].[SequenceInserts].[SetKey] = @p3))