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.