Frans,

Is it possible to implement the following INSERT statement in LLBLGen:

IF NOT EXISTS (SELECT * FROM [Table] WHERE AccountID=65 AND UserID=54 AND ItemID=460)
    INSERT INTO [Table] (AccountID, UserID, ItemID) VALUES (65 ,54 ,460)

Obviously I can write a Stored Procedure, but I was just wondering if there is a supported method for doing this in LLBLGen

Marcus

That;s not supported at the moment. That's a 2005 feature I pressume?

Otis wrote:

That;s not supported at the moment. That's a 2005 feature I pressume?

Nope its just standard T-SQL... I called it a "conditional insert".

It might be a good feature to include because it saves a round trip when inserting potentially duplicate rows which is very useful when you dont want INSERTs to fail and kill the transaction because of dupes.

Marcus wrote:

Otis wrote:

That;s not supported at the moment. That's a 2005 feature I pressume?

Nope its just standard T-SQL... I called it a "conditional insert".

It might be a good feature to include because it saves a round trip when inserting potentially duplicate rows which is very useful when you dont want INSERTs to fail and kill the transaction because of dupes.

It indeed saves a roundtrip and what's more important: it's a threadsafe test + insert combination. As the list of features for v2.0 first release is already defined and due to time constraints it's not wise to add stuff to that list, I'm not sure if I can add it to the first release of v2.0. It's not that hard I think, the save routine already accepts a filter, so that could be passed on to the DQE to generate an if statement, though I'm not sure if other databases support this as well. I'll add it to the todo list though

Otis wrote:

It indeed saves a roundtrip and what's more important: it's a threadsafe test + insert combination.

mmm... I think it would need to be wrapped in a trnasaction with an IsolationLevel of RepeatableRead or the new Snapshot (2005) for it be be thread safe.

Otis wrote:

As the list of features for v2.0 first release is already defined and due to time constraints it's not wise to add stuff to that list, I'm not sure if I can add it to the first release of v2.0. It's not that hard I think, the save routine already accepts a filter, so that could be passed on to the DQE to generate an if statement, though I'm not sure if other databases support this as well. I'll add it to the todo list though

Sounds good, have you published the feature list or is it a secret

I've been meaning to jot down some ideas I had for you for 2.0, but just didn't get time (as usual).

They mainly centered on the only area which I think has some room for improvement which is "Reading Data". LLBLGen does a great job for Insert / Update and Delete but performance for Read could improved. Eg. Caches for immutable data (immutable data could be marked as such in the designer).

PrefetchPath is GREAT, but only works with heavy Entities which don't have lightweight read only versions (like property/fields & nothing else). I have manually implemented interfaces for all my Entities and have created TypedList / TypedViews which implement this interface along with the original Entity via partial classes. This means that I can pass objects around my code and not worry about whether I'm passing an Entity or a TypedList etc...

I also support hierachies of the interface so that I can pass in Entities based on a subset of columns. IAccountEntity implements IAccountSummary which in turn implements IAccountIdentifier. This makes my Fetch code based very flexible and performance is optimised by only fetching exactly what is required. If I have already fetched a full AccountEntity and only want to display its name for instance I can pass IAccountSummary to my methods since both an AccountEntity or an AccountSummary entity since implement IAccountSummary. etc.

Being able to specify these interfaces in the designer would be a dream and then I could even specify the return type from Stored Proceedures to be a set of IAccountSummary etc... This would give the business logic a complete abstraction from the underlying souce of the data.

[UPDATE] AND HOW DID I FORGOT - the MOST important missing feature - Batch updates to the DB via UnitOfWork and SaveEntityCollection!

my 2 cents worth as they say!

I think you might be able to do this (Generate inferfaces etc) with custom properties, a plugin and a custom template or 2

Not totally sure though...

What I can tell you now is that the "Reading Data" will very much improve in v.2

That's classified information, I managed to get it from an insider

Answer wrote:

I think you might be able to do this (Generate inferfaces etc) with custom properties, a plugin and a custom template or 2

Isn't software great

Walaa wrote:

What I can tell you now is that the "Reading Data" will very much improve in v.2

That's classified information, I managed to get it from an insider

Ssst!

Marcus wrote:

Sounds good, have you published the feature list or is it a secret

For customers it's a secret still because we'll cut features if time's short. So posting a list will give long faces if there's no time left.

They mainly centered on the only area which I think has some room for improvement which is "Reading Data". LLBLGen does a great job for Insert / Update and Delete but performance for Read could improved. Eg. Caches for immutable data (immutable data could be marked as such in the designer).

Data reading is improved a lot, by removal of the 2 bottlenecks inside the entity creation code (name-to-index for fields creation and defaultvalue creation) plus all fields now share static info which means a much much lower memory footprint (>60%)

Caching of data is and will always be up to the developer, as caching data has to be done high up the application stack, not deep down in the DAL.

PrefetchPath is GREAT, but only works with heavy Entities which don't have lightweight read only versions (like property/fields & nothing else). I have manually implemented interfaces for all my Entities and have created TypedList / TypedViews which implement this interface along with the original Entity via partial classes. This means that I can pass objects around my code and not worry about whether I'm passing an Entity or a TypedList etc...

You can't avoid fetching data if you need to fetch the data. Prefetch paths do that for you, so they don't gain anything if the actions to be taken by the prefetch path fetcher is the same as manual fetching (m:1, single entities). The advantage of prefetch paths is that it can pull related entities in a 1:n scenario out of the db in 1 query and merge them for you. You can't do that manually. There you win performance. Typedlists fetch faster at the moment because they don't have the per-object overhead. The difference is greatly reduced in v2.

The designer should allow you to specify interfaces to implement as well. This is planned.

I also support hierachies of the interface so that I can pass in Entities based on a subset of columns. IAccountEntity implements IAccountSummary which in turn implements IAccountIdentifier. This makes my Fetch code based very flexible and performance is optimised by only fetching exactly what is required. If I have already fetched a full AccountEntity and only want to display its name for instance I can pass IAccountSummary to my methods since both an AccountEntity or an AccountSummary entity since implement IAccountSummary. etc.

Planned are 'fieldpaths' for fetches, which specify in a hierarchy, the fields to fetch. This can then be used to fetch all blobs for a set of entities for example, and merge them with the entities at hand. This reduces the initial fetch time and improves performance, especially in readonly scenario's.

Being able to specify these interfaces in the designer would be a dream and then I could even specify the return type from Stored Proceedures to be a set of IAccountSummary etc... This would give the business logic a complete abstraction from the underlying souce of the data.

Procs for persistence are on the list as well as selecting interfaces to implement. That is: interfaces to implement, so stubbing out methods in an interface you specify, IF specified you want it to stub out the interface (probably not). I've still think about if it's needed in the designer because we now have partial classes, so it's not really a big deal anymore.

[UPDATE] AND HOW DID I FORGOT - the MOST important missing feature - Batch updates to the DB via UnitOfWork and SaveEntityCollection!

Batching won't be supported for now, as it will take quite a piece of custom code to manage it all, and I've already explained to you why it is very hard to get this right in a hierarchical scenario, if not undoable.