Creating a new entity instance

To create a new entity instance, you first have to instantiate an empty entity class instance. In our example we'll use the Customer entity and we'll create a new Customer instance.

CustomerEntity customer = new CustomerEntity();

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 class instance 2 the entity data (the entity instance) has to be persisted / saved in the persistent storage. Let's add the customer Foo Inc. to the database:

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_Number = "(604)555-1234";
customer.PostalCode = "90211";
// save it. We require an adapter for this
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.SaveEntity(customer, true);
}

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_Number = "(604)555-1234"
customer.PostalCode = "90211"
' save it. We require an adapter for this
Using adapter As New DataAccessAdapter()
    adapter.SaveEntity(customer, True)
End Using

CustomerEntity 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.

Info

If the entity is in a hierarchy of type TargetPerEntityHierarchy you don't have to set the discriminator value for the entity type, this is done for you automatically: just create a new instance of the entity type you want to use, and the discriminator value is automatically set and will be saved with the entity.

Refetch and Out of sync behavior.

The code above will save the data directly to the persistent storage (database). The SaveEntity overload used also accepts a boolean for refetch. This boolean, here the value true is specified, controls whether the data of the entity is refetched right after the save action. Adapter requires this so the entity class instance contains the latest data and is 'in-sync'.

If we would have passed false or used the overload SaveEntity(entity), the entity class instance customer would have been marked 'out of sync'. This means that the entity's data has to be refetched from the database prior to reading from one of the entities properties. If you do not require the saved entity for any further processing, you don't need to refetch it and you can save yourself a roundtrip by simply omitting the 'true' in the SaveEntity() call. You can also flag an entity automatically as 'fetched' again. See Entity state control for details.

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 SaveEntity(). The new value for sequenced columns is available to you after SaveEntity(), even though you haven't specified that the entity has to be refetched.

This can be helpful if you want to refetch the entity later. Because the entity saved is new (customer.IsNew is true), SaveEntity() 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.

Info

Fields which get their values from a trigger, from newid() or a default constraint calling a user defined function are not considered sequenced fields and these values will not be read back, so you'll have to supply a value for these fields prior to saving the entity. This isn't true for fields which are of type unique_identifier on SqlServer 2005 when the DQE is set in SqlServer2005/SqlServer2012 compatibility levels and the field has in the database a default value of NEWSEQUENTIALID(). See: Generated code - Database specific features

Inserting entities based on a query: InsertEntitiesDirectly

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

InsertEntitiesDirectly 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 InsertEntitiesDirectly 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

InsertEntitiesDirectly 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 UnitOfWork2 has a method to schedule insert entities directly calls: AddInsertEntitiesDirectlyCall. 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);
using(var adapter = new DataAccessAdapter())
{
    var result = adapter.InsertEntitiesDirectly(typeof(InsertsSohEntity), 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);
using(var adapter = new DataAccessAdapter())
{
    var result = adapter.InsertEntitiesDirectly(typeof(InsertsSohEntity), 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"));
using(var adapter = new DataAccessAdapter())
{
    var uow = new UnitOfWork2();
    uow.AddInsertEntitiesDirectlyCall(typeof(InsertsSiEntity), q);
    
    // ... other work added to the uow...
    
    var result = uow.Commit(adapter);
}

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))