Daelmo suggested here - http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=15502 - that some modification of the adapter would allow for using sql functions in insert commands.

How would one proceed to accomplish this?

For the specific case mentioned in the other thread (to set createDate and ModifiedDate). I orefer not to modify anything, but rather use the Validation Framwork (by Dynamically Injecting a Validator), and use the ValidateEntityBeforeSave() method to set these fields.

This is always called before Inserting or updating, and the validator can run for all or subset of the entities, and in there you can determine if the action is insert or update.

Hi Walaa -

Jer & I are trying to get ExpressionToApply to fire during an Insert. We need actual DB code to run, not just simple dates... so using Validators won't do the job for us.

Right now, we have to make two calls for every record. Insert, and then Update to get the ExpressionToApply to fire. Which, as you can imagine, is quite inefficient.

Any thoughts on customizing our Adapter? Thanks!

Ryan

Jer & I are trying to get ExpressionToApply to fire during an Insert. Right now, we have to make two calls for every record.

Sorry I'm not following. would you please explain in more details and post a code snippet.

Hi Walaa,

Here is my unit test:

        [TestMethod]
        public void InsertWithExpression()
        {
            using (DataAccessAdapter adapter = new DataAccessAdapter(false))
            {
                // create new entity
                GcLockSessionEntity newEntity = new GcLockSessionEntity(Guid.NewGuid())
                {
                    LockedByUserId = Guid.NewGuid()
                };
                // set field value to expression
                newEntity.Fields[(int)GcLockSessionFieldIndex.LockExpiresTime].ExpressionToApply =
                    new DbFunctionCall("DATEADD(mi,{0},getdate())", new object[] { 30 });
                newEntity.Fields[(int)GcLockSessionFieldIndex.LockExpiresTime].IsChanged = true;
                // insert new entity
                adapter.SaveEntity(newEntity);
            }
        }

Which results in the following sql insert statment. You can see that the value for @LockExpiresTime is undefined, even though we set the expression to apply for that field.

    Query: INSERT INTO [Northwind].[dbo].[gc_LockSession] ([LockSessionId], [LockedByUserId], [LockExpiresTime])  VALUES (@LockSessionId, @LockedByUserId, @LockExpiresTime)
    Parameter: @LockSessionId : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0a9ea093-c5dd-4e9e-b820-74a060591190.
    Parameter: @LockedByUserId : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 90b53189-79dd-4712-bc67-c09643be01c2.
    Parameter: @LockExpiresTime : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: <undefined value>.

On the database, that field is non nullable, so I get a sql exception when this code runs.

If I really want to set the expression, I have to save the entity twice: once as in insert with a placeholder value, and once as an update with the dbFunction:

        [TestMethod]
        public void UpdateWithExpression()
        {
            using (DataAccessAdapter adapter = new DataAccessAdapter(true))
            {
                // create new entity
                GcLockSessionEntity newEntity = new GcLockSessionEntity(Guid.NewGuid())
                {
                    LockExpiresTime = DateTime.Parse("1/1/1900"),// temp value
                    LockedByUserId = Guid.NewGuid()
                };          
                // perform insert:
                adapter.OpenConnection();
                adapter.SaveEntity(newEntity, true);
                // set field value to expression:
                newEntity.Fields[(int)GcLockSessionFieldIndex.LockExpiresTime].ExpressionToApply = new DbFunctionCall("DATEADD(mi,{0},getdate())", new object[] { 30 });
                // tell entity that it needs to update the db
                newEntity.Fields[(int)GcLockSessionFieldIndex.LockExpiresTime].IsChanged = true;
                newEntity.IsDirty = true;
                // perform update
                adapter.SaveEntity(newEntity);
                adapter.CloseConnection();
            }
        }

Because I set the placeholder value, SqlServer doesn't complain. The LLBLGen runtime is able to set the expression on update, and this is the generated update command:

    Query: UPDATE [Northwind].[dbo].[gc_LockSession] SET [LockExpiresTime]=DATEADD(mi,@LO1e1,getdate()) WHERE ( [Northwind].[dbo].[gc_LockSession].[LockSessionId] = @LockSessionId2)
    Parameter: @LO1e1 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 30.
    Parameter: @LockSessionId2 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0e338b77-ccd7-4b1f-a13f-acfbb0490c29.

Now, of course we could perform 2 commands for each inserted entity, but I was wondering out of curiosity , how much work it would be to modify the DataAccessAdapter in order to call my expression on insert.

You've to alter the DQE.

At line 508 of SqlServer's DynamicQueryEngine.cs, the code starts to emit the (@p1, @p2,...) block after VALUES in the insert query.

There, you've to check whether the field.ExpressionToApply is not null. If it's indeed not null, you've to run the same code as is present in the Update DQ method, at line 713 and further.

be sure you append the parameters from the Expression object in-place, so at the spot the parameter of the field value would normally be emitted.

Enable DQE tracing to see if your query is indeed emitted correctly.

To use your own DQE instance, you've to subclass DataAccessAdapter and override CreateDynamicQueryEngine and in there create an instance of your own DQE version. That's it

Thanks for the answer, Frans!

For the moment, I am adopting Simmotech's solution here and it seems to work very well.

It allows for insert into(Select ...) as well as the ability to use db function calls.

As per his example, the DQE stuff in put directly in the adapter, which is a pragmatic solution for now.