Using version 5.6.2.

I have a 1:0..1 relation between Customer and ShipAddress. And I have the Lazy loading generates new flag set to True.

This VB code throws an exception:

        Dim C As New CustomerEntity
        Dim bs As New BindingSource
        bs.DataSource = C.ShipAddress
        C.LastName = "Sam"  'Non null constraint on Customers
        C.Save(True)

But this doesn't:

        Dim C As New CustomerEntity
        Dim O As ShipAddressEntity
        O = C.ShipAddress
        C.LastName = "Sam"
        C.Save(True)

The exception is in the Save:

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: 'An exception was caught during the execution of an action query: Violation of UNIQUE KEY constraint 'UC_479006845679949426d99bde391'. Cannot insert duplicate key in object 'Customer.ShipAddress'. The duplicate key value is (<NULL>).

My key in all tables is an IDENTITY field.

So, why does binding the new (empty) entity to a BindingSource then cause a NULL key to attempt to be saved?

I can't reproduce the latter in C#, the second one also fails. This is by design btw. See: https://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=5435

(14 years ago, but still valid). The thing is that the related entity has no fields changed, so it's not changed. It's also not receiving an FK from the related entity (as it's the PK side) so it will lead to phantom inserts. the new entity returned by the lazy loaded navigator is always a different instance as well, as the lazy loading effectively fails, the pk isn't set.

Don't rely on this for building a graph: lazy loading shouldn't be used for that (it should be avoided in general). Build the graph by creating new entity instances and assigning navigators.

We do acknowledge the documentation page https://www.llblgen.com/Documentation/5.6/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/SelfServicing/gencode_usingentityclasses_lazyloading.htm is misleading, the setting LazyLoadingWithoutResultReturnsNew is false by default, not new. We'll correct this. (it's correctly documented at the settings page).

My project is described a bit here (where I answered my own question) as to why I want lazy loading:

https://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=27016

Basically, I have two entities, one that has data for everyone (Names and such) and a second that has maybe-maybe-not-used data (like a shipping address)

I did it that way because to bring up an existing customer, users will only be searching in the first entity. I.e. why read the Shipping Address for all matching customers when I'm showing a results grid with only names in it?

These two entities have a 1:1 (or 1:1..0 - I'm not sure it matters?) relation, matched on their PKs. My always-have table has an IDENTITY column for it's PK. My second just has a PK as INT and NOT NULL. (This was what Llblgen created, which I think is correct)

When a user creates a new customer, they may or may not enter anything into the second entity. But my entry screen has input boxes for both. So, my screen has two BindingSource controls (I'm using Devexpress controls, and they insist to be bound through BindingSource controls.)

When I see the user creating a new customer, I assign my first BindingSource to the always-used entity, the second to the maybe-used entity. Then I go and save, and that's where things go awry.

The FIRST save I do into an empty database works fine. I have a single record in both tables. The second table may have all NULLs, but technically, that's ok, since I have the 1:1 set. That empty record needs to exist.

But all subsequent saves, with no changes to my code, fail with that exception.

If I remove assigning the maybe-used entity to a BindingSource, the save is always fine.

So what is it about assigning an empty entity to the Bindingsource, even if I do nothing else with either the BS or the entity - just the fact that I assigned one to the other - that then causes the save to fail?

Any why work the first time?

As I said in the other post, I have 35 years of table level thinking to unlearn, so I'm obviously doing something wrong.

Maybe the fix is to give the maybe-used entity it's own PK, not try and use the one from the always-used entity?

And I'm curious as to why you discourage lazy-loading in general (in other threads as well.)

timbered wrote:

My project is described a bit here (where I answered my own question) as to why I want lazy loading:

https://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=27016

Basically, I have two entities, one that has data for everyone (Names and such) and a second that has maybe-maybe-not-used data (like a shipping address)

I did it that way because to bring up an existing customer, users will only be searching in the first entity. I.e. why read the Shipping Address for all matching customers when I'm showing a results grid with only names in it?

These two entities have a 1:1 (or 1:1..0 - I'm not sure it matters?) relation, matched on their PKs. My always-have table has an IDENTITY column for it's PK. My second just has a PK as INT and NOT NULL. (This was what Llblgen created, which I think is correct)

When a user creates a new customer, they may or may not enter anything into the second entity. But my entry screen has input boxes for both. So, my screen has two BindingSource controls (I'm using Devexpress controls, and they insist to be bound through BindingSource controls.)

When I see the user creating a new customer, I assign my first BindingSource to the always-used entity, the second to the maybe-used entity. Then I go and save, and that's where things go awry.

The FIRST save I do into an empty database works fine. I have a single record in both tables. The second table may have all NULLs, but technically, that's ok, since I have the 1:1 set. That empty record needs to exist.

But all subsequent saves, with no changes to my code, fail with that exception.

If I remove assigning the maybe-used entity to a BindingSource, the save is always fine.

So what is it about assigning an empty entity to the Bindingsource, even if I do nothing else with either the BS or the entity - just the fact that I assigned one to the other - that then causes the save to fail?

It will insert a new NULL in the unique constraint bound column. In SQL Server, a column with a unique constraint can contain just 1 NULL value at most. So if you insert an entity with a NULL for a field which has a UC and there's already a NULL in that column from another entity, you'll get that exception. So if you plan to have NULLs for more rows in that FK field, you can't have a UC on that field. You can still define it as a 1:0..1 relationship in the designer, but the UC has to go. The designer doesn't tell you this however, it simply adds the UC for you. So the best way to avoid this error if you plan to have more NULLs is not to have a UC on it (I think there are check constraints you can apply instead to work around this problem, it's common people run into this, I'm sure you can find some constraint scripts if you don't have them yourself, to mitigate this quirk of SQL Server) (edit) e.g.: https://www.dbrnd.com/2018/03/sql-server-allow-multiple-null-values-in-unique-constraint/ Which is basically the same as: https://stackoverflow.com/questions/767657/how-do-i-create-a-unique-constraint-that-also-allows-nulls/767702#767702

so there are workarounds

Any why work the first time?

As I said in the other post, I have 35 years of table level thinking to unlearn, so I'm obviously doing something wrong.

I don't think that's it, it's a quirk of SQL Server you run into (at least that's what the error you get tells me). Oracle for instance allows many NULLs in a column like that.

Maybe the fix is to give the maybe-used entity it's own PK, not try and use the one from the always-used entity?

You could work around this by under the hood setting the 'no address' customers' address navigator an Address entity taht represents 'No address' but it's flaky. I'd remove the UC on the FK field in the database

And I'm curious as to why you discourage lazy-loading in general (in other threads as well.)

Lazy loading can be 'handy' if you fetch related data but only in a minor amount of situations. In many cases it results in many queries which can cause a lot of data being fetched. For instance, if you bind a set of orders to a grid and have the grid setup to display the related Customer entity's CompanyName in a column in the grid, the databinding logic of the grid will trigger lazy loading on each row in the grid, but one at a time, so you'll get many queries for that single set of rows. Much more efficient is to prefetch the data you want to work with, using a prefetch path. E.g. the orders in a grid example can be fetched with 2 queries using a prefetch path and these are merged in memory by the runtime for you. If you let lazy loading do its job, you'll get 1 query for the orders and per order 1 query for the related customer. Say there are 100 orders in the grid, that's 101 queries.

That might be 'ok' for now, but the main problem here is that it's hidden. You don't see it happening, only that it takes 'some time' to fetch the data for the grid. If multiple people use the same client on the same DB this can lead to performance problems, hence it's not a good pattern and why we didn't implement it in adapter and added ways to disable it in selfservicing completely as well.

In looking at the tables:

After the first save, there's a record with an ID of "1" and all other columns null, in the maybe-used table. So, the record got created from the new (empty) entity, and was correctly given an ID.

But I realized that, in thinking it through, it's not possible to do what I'm trying to do: Have the same ID in both tables point to the same record. Since the first table is an IDENTITY column, the code doesn't know what ID will be assigned, so there's no way to use that ID in the save to the second table. Duh!

I'm still not clear as to why it would work even once then. Nor why I only get the error if I first assign that empty entity to a binding source.

There's black magic going on that I am not privy to.

As to what you're saying about lazy loading, I understand those gotchas, and am designing my architecture specifically to avoid them (hence, this two-table idea in the first place.)

I'll adjust some things and relationships, and let you know if I get stuck again.

Thanks for your help!

After the first save, there's a record with an ID of "1" and all other columns null, in the maybe-used table. So, the record got created from the new (empty) entity, and was correctly given an ID.

But I realized that, in thinking it through, it's not possible to do what I'm trying to do: Have the same ID in both tables point to the same record. Since the first table is an IDENTITY column, the code doesn't know what ID will be assigned, so there's no way to use that ID in the save to the second table. Duh!

There is no magic happening here, this managed under the hood by LLBLGen framework. A feature called PK-FK Synch. If you are saving a graph of new entities (provided they have dirty fields to be saved), the framework saves the PK side of a relation first, then query the ID set in the database, and use that to fill the FK field at he FK side of the relation before saving it.

I'll adjust some things and relationships, and let you know if I get stuck again.

I'll close this thread for now. It will be re-opened if you post a reply. Or if you have a different question, please open a new thread.

Thanks,

Something in my brain isn't connecting with how this is supposed to work.

I adjusted my tables to not use the same key, but it's still not working the way I think it should.

Two tables (Parent & Child), with a 1:1 relationship, both with their own INDENTITY keys. There's a ChildId field in the Parent.

I use Llblgen to generate everything. All I've done is the two entities and one relationship.

I can't save more than one record in the Parent table, because the FK in the Parent is null (no Child record). But the 1:1 says that other record has to be there. Why isn't it?

I would think that if the framework is smart enough to create a UC because of a relationship, it should be smart enough not to violate it. no? I have a 1:1 relationship. Why isn't it creating the other side of the :1 even if the entity is all NULLs?

But assuming that is the way it's supposed to work, then how DO I create these related records? What is supposed to be the standard way of creating new entities that have relationships with Self Servicing?

Assuming I create an "As New ParentEntity" then go to save it, I either get 1) Can't save at all because Parent.Child = Nothing, or 2) I can save once, then not again because Parent.Child is all NULLs (which happens depends on the "not found returns new" flag.)

And, assuming that just removing the UC will solve the problem, is that something I can do in the Designer, or do I have to manually change the DDE script?

And, why does the designer put the UC there in the first place if it's a 1:0..1 relationship?

Again, my apologies for my ignorance.

You know how you get to a point when you've tried so many things, you can't remember what worked and what didn't? I passed that point hours ago.

I've been fiddling around enough to know that something else in my model is causing my write failures. So what I explained above may or may not be true. Probably more on the not side.

Having said that, there's still a few things I'm not clear on.

Does Llblgen treat 1:0..1 different than 1:1? It would still seem to me that a 1:1 requires both records to be created, even if one is empty. Llblgen doesn't seem to do that. And if it's 1:0..1 I wouldn't think a constraint would be placed on the 1 side, but I think there is.

And, is there an example somewhere of run-time creating new entities using a model that has multiple relationships between the entities? Like I said, how should it be done?

A 1:1 relationship can be constructed in 2 ways: - the FK side is also the PK of that entity - the FK side has a Unique Constraint and is not the PK of that entity.

We see a nullable FK with a UC as the only reason to construct a 1:0..1 relationship, so: pk side 1:0..1 fk side.

Our system doesn't support 'owned' entities in a way that you propose. Let's say I have Customer and Customer has two 1:1 relationships with Address: Customer.VisitingAddress 1:1 Address.CustomerVisiting and Customer.BillingAddress 1:1 Address.CustomerBilling

Customer is the FK side on both. If I instantiate a new Customer instance:

var myCustomer = new CustomerEntity();

it doesn't instantiate two AddressEntity instances as well. The main reason being that in this case for instance you might want to use the same AddressEntity instance for both visiting and billing. Another is that if you always instantiate an entity B with an entity A, the fields of B are part of A (like all fields in A have a 1:1 relationship with the pk field of A).

So how to do this in code?

var myCustomer = new CustomerEntity();
var visitingAddress = new AddressEntity();
var billingAddress = new AddressEntity();
myCustomer.VisitingAddress = visitingAddress;
myCustomer.BillingAddress = billingAddress;
// ... fill in the fields of all 3 entities here
//...
// persist the 3 in one go:
myCustomer.Save(true);

As 'Address' is on the PK side, you also could have inserted the Address first, and set the FK fields in myCustomer to the PK value of the Address entity. This too is a reason why we don't instantiate the entities together in one go.

In the Database, enforcing the 1:1 relationship in the case of a PK 1:1 FK/UC setup is a bit problematic if the FK side is nullable, in the case of SQL Server as it doesn't allow multiple NULL values in the FK field, as discussed above. In some other databases this isn't a problem. In that case, it's either a matter of creating a unique index with a where clause (as discussed above, see links) or remove the UC completely and rely on the .NET code for uniqueness.

Hummm...

I think I get what you're saying. Like I said I have a lot of database (and non-relational at that) stuff to relearn.

I will say that, this is my first production project using Llblgen, and there's no way I would go back. It has save me so much time and (until now) aggravation. And this bit is my own doing, just my learning curve.

Thank you both so much for your help, and patience. Your support excellent, and one of the reasons I made my purchase in the first place.

One last question: Is there a way to edit or remove the UC that Llblgen is creating? It's not on the UC page of the entity.

timbered wrote:

Hummm...

I think I get what you're saying. Like I said I have a lot of database (and non-relational at that) stuff to relearn.

I will say that, this is my first production project using Llblgen, and there's no way I would go back. It has save me so much time and (until now) aggravation. And this bit is my own doing, just my learning curve.

Thank you both so much for your help, and patience. Your support excellent, and one of the reasons I made my purchase in the first place.

Thanks for the kind words

One last question: Is there a way to edit or remove the UC that Llblgen is creating? It's not on the UC page of the entity.

If you're working database first: remove the UC from the database schema. Then use sync to update the relational model data in the project. The UC will be removed from the project and the relationship will be converted to a 1:n. Edit the relationship and switch it back to a 1:0..1 relationship. You'll get a warning that the relationship doesn't have a backing UC. That's fine, as you can't use a UC here. Generate the code and it should look the same.

If you're working model first, the designer will keep on creating the unique constraint for you, as it needs to make the model correct. When updating / using the DDL SQL script that you export, remove the UC creation on the table as the database schema itself can't have a unique constraint on that field.

In the schema in the database, then define the unique unclustered index with a where clause. The designer doesn't obtain indexes from the schema in database first so you'll keep the warning, but it's fine as you know why it's there and with the index you've solved the uniqueness at the schema level.

Ok, got everything almost working, thanks to you both!

One last question (I think I already said that several questions ago.)

1. I have the "Not found returns new" flag set to yes.
2. I have a 1:1 relation from Customer (PK) to ShipAddress (FK)
3. I do "Dim C As New CustomerEntity".

According to my debugger, Customer.ShipAddress now contains an empty ShipAddress entity. But when I C.Save(True), that empty ShipAddress does not get saved to a table.

However, if I do an additional step of:

1. C.ShippingAddress = New ShippingAddressEntity

and then save, the empty entity gets saved.

What's the difference between the two that's causing one to get saved, and the other not?

According to my debugger, Customer.ShipAddress now contains an empty ShipAddress entity. But when I C.Save(True), that empty ShipAddress does not get saved to a table.

I can't reproduce it. Both attempts fail unless one field has been set to some value, only then a Save(recurse=true) will succeed.

Do you set any of the ShipAddress fields before saving?

Huh. I thought it was designed that way.

No, I don't set anything in either of the child entities.

I'll try a few things and confirm that it's happening the way I think it is.

Do you have the "can't find returns new" flag set? Mine will fail too without that set.

Yes. I had that flag set to true.

When no field is set to a value in an entity that's an entity discovered through a graph traversal (so not the single entity you're saving but a related entity), it's considered 'not changed' and won't be inserted but simply skipped (to avoid phantom inserts as explained above).

How does the Save know the difference between an empty entity showing through the traversal and one created deliberately with New?

Both fail at my test.

timbered wrote:

How does the Save know the difference between an empty entity showing through the traversal and one created deliberately with New?

In both situations it sees an empty entity that has no fields set and therefore it considers the entity instance (the data inside the object) as non-existent and skips it. So they're equal, and thus both should fail.

That doesn't seem to be the case with me.

I'm not too worried about it. You can close this thread.

Thanks again for you help!