I have a relationship (not set up by me so bear with me):

1 Asset is related to 0..1 Income

Asset has an IncomeID field which is nullable as I expected.

Whilst trying to create some test assets, I got a UniqueConstraint exception which I didn't expect.

LLBLGen has added a UniqueConstraint on IncomeID which I can sort of see why but it won't work work if the field is nullable since two Nulls are considered duplicates.

Also, I saw the 'Asset' is Primary Key side. Not sure what that should be for a 1:0..1 relationship.

(Income has not been setup as yet - it is just an ID field so far - so I can just delete the UC temporarily from the database for my testing)

This sounds like the 'optional 1:1 relationship' mentioned by Frans in another thread. Looks like I am having trouble with them already!

I also tried just creating a new Income entity on the Asset entity I was creating but this still failed maybe because the Income needed to be created first and because I was using SaveEntityCollection on an EntityCollection<AssetEntity> (with recursion) this ordering restriction can't be picked up automatically?

I of course need to find out what the plan is for the Income table but if it should be an optional 1:1 relationship, how should I set it up so that I can a) Add AssetEntity without an IncomeEntity and b) Create the IncomeEntity at the same time as the AssetEntity?

Cheers Simon

Which runtime library version (build no.) are you using?

v3.5 Final March 30th

Optional 1:1 relationships in inheritance like scenarios are on the PK.

So say you have 'Customer' and 'SpecialCustomer'. SpecialCustomer has a PK which is an FK to Customer. In general you could say SpecialCustomer is a subtype of Customer, but you don't make it a subtype. Instead you make it a related entity. This has the advantage that when you fetch Customer, you won't get a join with SpecialCustomer for 'subtype retrieval' as with inheritance.

The optional FK with a UC... problem. If you define a 1:1 relationship where the FK side isn't the PK, a UC is required to make it a 1:1 relationship, otherwise it's a 1:n relationship. If you have a nullable field in the FK side's fields, the UC is a problem. There is a way around this: mark the relationship as model only.

This won't give the UC on the DB side. There's no other way for a 1:1 relationship which ISN'T model only: it has to have a counterpart which is defined at the DB level and it has to be a UC, otherwise it's equal to a 1:n relationship.

Ok I think I see what you're saying. I can design a 1:1 in the Model but it will effectively be m:1 in the database because of no UC.

Just to confirm: I have a Table called Asset with an ID PK I have another Table called Income with its own ID PK. These are standalone tables. ie they can exist independently of one another An Asset is sometimes linked to a single Income therefore needs a nullable IncomeID column.

Ideally I would have a constraint that guarantees uniqueness of non-null FKs but allows many null values - however there isn't so I can't and will have to have an Asset:Income m:1 relationship in the database. The Model-only 1:1 in LLBLGen will disguise this however and produce an Asset property on the Income that is a single entity rather than a collection. (what happens if the database is finger-poked so that there are two Assets related to an Income?)

I now have a 1: 0..1 relationship (I assume this is the same as a 1:1 but marks the FK nullable) which is marked as Model Only but the UC is still being generated.

(what happens if the database is finger-poked so that there are two Assets related to an Income?)

Only one will be returned. But this is a problem for data integrity.

Optional 1:1 relations are problamatic to implement in the database.

I now have a 1: 0..1 relationship (I assume this is the same as a 1:1 but marks the FK nullable) which is marked as Model Only but the UC is still being generated.

Make sure there is no other relation in the model that's being outputed in the update script. (old relation, that should have been removed).

Arggh!!!

You have to delete in Catalog Explorer as well. Everything twice!!

When you guys going to add indexing support into Catalog Explorer?

simmotech wrote:

You have to delete in Catalog Explorer as well. Everything twice!!

If you delete the relation on the Model Explorer, it will be marked for deletion on the Catalog Explorer. For UC is different, you have to mark for deletion directly on Catalog Explorer. Also, deleting a relation won't necessary will delete the UC.

simmotech wrote:

When you guys going to add indexing support into Catalog Explorer?

What do you mean by "indexing support"?

What do you mean by "indexing support"?

Have LLBLGen let me define indexes on the table/columns in Catalog Explorer.

In fact, v4.0 to replace MSSMS...

simmotech wrote:

What do you mean by "indexing support"?

Have LLBLGen let me define indexes on the table/columns in Catalog Explorer.

Indexes aren't a model concern, but a data concern, and used for performance optimizations at runtime. I.o.w.: it's silly to define them in the model as you don't know the runtime characteristics of the model as there's no data involved in the model. If you could define things in the relational model data it might be 'convenient' but it would actually make totally no sense at all, as there's no basis to define an index on field ABC in a model without data.

Btw, what do you mean 'deleting everything twice' ? The relational model data in the catalog explorer follows your model, things will be auto-deleted if necessary, you barely have to delete anything manually, and there are settings for fields / tables etc. to auto-delete from the model if they're orphaned for example. It's very low maintenance.

The unique constraints mentioned above didn't delete themselves although they were no longer needed by the model. I had tried all combinations of saving, validating, running update scripts, running create scripts and they kept coming back.

It was only when I went to the Catalog Explorer and toggled the deletion flag there then created a create script that I had a database without them.

Warning: The following is a loose collection of thoughts some which may seem critical and also so ill-ordered it might be considered a ramble. Just remember that no matter what, I still like using LLBLGen and made it my first choice.

It's very low maintenance.

I have already mentioned it before but where I am now development-wise. I want to just add fields on the Fields tab and not even need to look at the Field mappings tab. I then want to run a Create script and/or Update script and regen the code and that be it.

I get confused (I'm getting older!) with Sync selected, Edit Field, New Field, AutoField and AutoMap context menu things. I just want the database to be synced with the model I have just changed. I have to do one of these so it feels like a change twice.

I don't want to have the fields in the database be created in a different order than those in the Fields list. I want ID first always and I want Answer1, Answer2, Answer3 etc. in that order because that's how I created them and it looks silly when they are not in that order. Yet I look at an entity where everything is in the right order; I look at the Field mapping tab and they are still in the right order but the Catalog Explorer has them in reverse order and so that is how they get created in the database. How they got like that I have no idea So I have to remember to Set Field Order before creating Field Mappings and even then I am convinced that the Field Mappings come out differently but I can't work out under what circumstances. If I forget then I have to reorder them and then create the fields so it feels like twice again.

When I create a new field and type 'str' for string as the type and press Ctrl-Enter ready for the next, I have to start again because I forgot to press tab and the value isn't set until I do. (If this is a DevExpress grid BTW there is an option to post the edit on close which would solve this problem). Sure 'don't forget to hit tab!' is the obvious answer but I do and have to repeat the operation - hence twice.

Validation: Do I need the tick icon or the tick-database icon or both. I can't remember so I do both to be on the safe side - validation twice.

That's why I mentioned 'doing things twice'. I'm sure some of things are my fault but not all.

Indexes aren't a model concern, but a data concern, and used for performance optimizations at runtime. I.o.w.: it's silly to define them in the model as you don't know the runtime characteristics of the model as there's no data involved in the model. If you could define things in the relational model data it might be 'convenient' but it would actually make totally no sense at all, as there's no basis to define an index on field ABC in a model without data.

This sounds a bit theoretical to me and so I am definitely out of my depth there.

What is the point of a Model definition if there is no ultimate persistence of data. None really? An RDBS is one type of data store that could be applied to a model. Sure there might be others but I don't have any use for them and LLBLGen just has support for RDBS anyway (AFAIK).

What is Relational Model Data anyway. I can see you support multiple RMD for a given model simultaneously but I would imagine that 99% of users want to use their single chosen RDBS.

I wasn't suggesting that Indexes be supported on the Model but on the chosen Relational Model Data which until your comment, to me was just a visualization of the RDBS. You seem to suggest that an RMD is an independent Data Model. So where I was thinking Model->RDBS you are thinking Model->RMD->RDBS where RMD is an abstract concept that shouldn't be contaminated with 'data' or 'data-related' concepts such as an index. I now have two 'models' where I thought I had one. And I still will have to manually play with the database to make it work properly.

I would argue that since my RMD is defined in in a tree under a_ SQL Server node_ then it would be eminently reasonable to assume that it represented the database in SQL Server. Taking that a step further the creation of indexes on an RDBS here, being an essential part of making a useful working system, would be very convenient indeed. If you have no plans in this direction for future version then I find that unfortunate.

You mentioned that it might be 'convenient' but would actually make no sense at all. I disagree with that as it seems to make perfect sense to me. But it seems to be opposite thinking from what happens with the 'other Model' in that you can add all sorts of Settings, Custom properties, Attribute, Namespaces, Interfaces - all of which are not needed by the Model per se but are there because they are a 'convenient' way of setting them up and having a script and/or code use them further down the line. Why is marking a field or fields as requiring an index so different that it becomes an abomination to suggest it?

To me its all about the data. LLBLGen my preferred way of defining the structure of that data and generating code to deal with that data structure. Model-first seems a convenient way of defining the structure without having to use MSSMS then import it then generate code. I know I will still have to use MSSMS to define indexes currently but I always expected that LLBLGen would incorporate more of its functionality to make it a one-stop shop. Seems I was wrong about that and that was never the intention so I'm wondering whether I should go back to Database first and just use LLBLGen to generate code as the Model-part (two parts really with RMD) will only ever be an abstraction that shows part of the overall work but treats data as 'none of its concern'.

Cheers Simon

You posted a long piece of text, but I'm not debating design decisions here, sorry. Please don't take this the wrong way, but how our code works and how it's designed internally isn't a democracy where the users have a vote in. If you want to have it changed, please file a change request / feature request, we have a specific forum for that Keep it simple, and short, and we'll have a look, add it to the list of items to work on for future versions and eventually it will make it in (or denied forever ).

If I have to debate every decision I make on features I have no time writing actual code.