I've run into several situations where i need to save a new entity related to some other entity but the only value immediately available in the code corresponds to a NON-PK unique constraint of the related entity. So, I end up using the unique constraint to either fetch the whole entity or to execute a scalar query that returns the PK of the related entity.

It would be nice if the DQE supported using the ExpressionToApply property for a field to arrive at a formulation like the one shown below. Is it possible that something like what's shown below might be supported in the future?

INSERT INTO eRegistration (EventId, FirstName, LastName, Email)
SELECT (SELECT EventId FROM Event WHERE ShortName = ''), 'John', 'Doe', 'johndoe@domain.com'

Btw: I realize that the following threads deal with a related issue but it seems like there is a subtle but significant difference to my inquiry since I'm basically interested in a way to use a sub-query to insert a single row from an entity rather than a way to load a bunch of rows into one table from another table.

http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=6977&HighLight=1 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=3281&HighLight=1 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=6577&HighLight=1

I was just about to post the same question / feature request

In my situation, i'm working on calculating a checksum value on the imported information & would love to use sql to do the checksum for me.

Here is a sample of the SQL currently generated.

INSERT INTO [DB].[dbo].[Users] ( [UserName], [Password], [Checksum] ) VALUES ( @UserName, @Password, @Checksum )

I would love to be able to apply an Expression to the Checksum column to cause the following output

INSERT INTO [DB].[dbo].[Users] ( [UserName], [Password], [Checksum] ) VALUES ( @UserName, @Password, binary_checksum(@username,@password) )

Would it be possible to add support for this?

Regards, Shannon

The two requests aren't the same.

The first, is a request for: INSERT INTO (...) SELECT ... [FROM]...

and the second is a request for: INSERT INTO (...) VALUES(..., functioncall(..))

A VALUES() clause can't contain a scalar query, so a scalar query select has to be placed inside a SELECT clause instead of a VALUES clause.

Furthermore, saving an entity where a field has an expression set is a bit strange, as the entity has to be saved with the values it contains, not some expression.

It's not impossible though, but it can also be solved by a default constraint with a function call (second request). Wouldn't that be ok?

Hi Otis,

The only problem with having a default constraint is that it puts the checksum call in the db itself, thus if the checksum field was left null it would automatically put in the correct checksum value. Essentially the checksum is to stop a person manually going into the table and changing information.

I know that a person could easily profile the sql server to see what was going on and replicate the SQL to perform that insert/update but I'm not too worried about that as they could use the DB API to do that anyway.

Thanks, Shannon

scornish wrote:

Hi Otis,

The only problem with having a default constraint is that it puts the checksum call in the db itself, thus if the checksum field was left null it would automatically put in the correct checksum value. Essentially the checksum is to stop a person manually going into the table and changing information.

Ah so it always has to be called, despite the value passed in for the checksum column ? (so otherwise people could work around it and pass NULL ? ) (if not, I think I don't understand what you mean exactly)

So, from your response to my original post, Otis, it sounds like you would consider the scenario I described in that post as too marginal to add support for it?

Furthermore, saving an entity where a field has an expression set is a bit strange, as the entity has to be saved with the values it contains, not some expression.

I'm not sure I understand your point here, could you explain a bit further. I'm probably just being dense. The fact that UpdateEntitiesDirectly uses the ExpressionToApply seemed like it could open the door for a similar formulation for inserting rows.

Otis wrote:

Ah so it always has to be called, despite the value passed in for the checksum column ? (so otherwise people could work around it and pass NULL ? ) (if not, I think I don't understand what you mean exactly)

Yes it always has to be called regardless of the checksum column value. The only time I care about the stored checksum value is during a select. The checksum value should always explicitly use the binary_checksum function to calculate the value on an insert or an update.

During an insert or update, we should calculate a checksum value based on the parameter values and/or existing values.

During a select, we get the stored checksum value minus the calculated checksum value. We then enumerate the entities and check the 'checksum' value and if the value is not 0, (which means someone has changed a row value in the database) we throw an exception.

Hope this makes sense,

Shannon,

everettm wrote:

So, from your response to my original post, Otis, it sounds like you would consider the scenario I described in that post as too marginal to add support for it?

Emitting the SELECT clause is a little more complex than emitting a functioncall as the value (or an expression).

Furthermore, saving an entity where a field has an expression set is a bit strange, as the entity has to be saved with the values it contains, not some expression.

I'm not sure I understand your point here, could you explain a bit further. I'm probably just being dense. The fact that UpdateEntitiesDirectly uses the ExpressionToApply seemed like it could open the door for a similar formulation for inserting rows.

WHen I save customer - orders recursively, and insert a new order entity, I've to set the particular OrderEntity's field's ExpressionToApply property to the expression. This is a bit unintuitive as you would set field values on an entity, not some field's expression.

Of course, I could add that, it's just a bit unintuitive towards how you would use an entity normally.

scornish wrote:

Otis wrote:

Ah so it always has to be called, despite the value passed in for the checksum column ? (so otherwise people could work around it and pass NULL ? ) (if not, I think I don't understand what you mean exactly)

Yes it always has to be called regardless of the checksum column value. The only time I care about the stored checksum value is during a select. The checksum value should always explicitly use the binary_checksum function to calculate the value on an insert or an update.

During an insert or update, we should calculate a checksum value based on the parameter values and/or existing values.

Another possibility is to implement this in the entity class itself, or by a trigger. (just ideas, not to mitigate your point)

During a select, we get the stored checksum value minus the calculated checksum value. We then enumerate the entities and check the 'checksum' value and if the value is not 0, (which means someone has changed a row value in the database) we throw an exception.

Hope this makes sense,

Ok. Well, as I explained above, it's a bit unintuitive, though for pure expressions it's not a tough job to build in. The main part of the time will be put in testing if a db (we support a couple ) supports SELECT's in the INSERT statement (for scalarqueryexpressions) and if so, how to formulate the select to work around the lack of a FROM clause (which I think won't be possible on a couple of db's without a dummy table, which is possible on db2 and oracle, but I don't know if it works on access for example)

I've added it to the todolist. For v2.1's featurelist, it's on a priority of 7 (10 is the highest), so it will be done when prio 10, 9 and 8's are done and there's time left.