Foreign Key Relation Not Creating [DropDown] automatically in Blazor Studio

Rk_M · May 16, 2023, 4:33pm

Hi There,
I have two tables as below. As we see, there is a Foreign Key Relationship with Field, DATAMASTER_DB_ID...

When I generate simple CRUD app with [Radzen Studio] I see that it nicely created Add / Edit Pages with DropDown Component for DATAMASTER_DB_ID in Child table (i.e. T_DM_DATAHEADER). And those Drop downs are well populated with values from Parent table (i.e. T_DM_DATAMASTER).

But In Radzen Blazor Studio, that's not happening - Over here in Add/Edit page of T_DM_DATAHEADER, I see DATAMASTER_DB_ID is appearing as simple text box. Is this a Bug to be fixed? (Because if this is intentional change, then I have no clue "why" ).

DB Scripts Are Here:
DB_Objects.zip (1.9 KB)

enchev · May 17, 2023, 5:53am

Hi @Rk_M,

The problem is related to your other thread. The meta data of your database is wrongly identified resulting in missing lookup for Add/Edit page. We will do our best to address this in our next update later this week or early next week.

Rk_M · May 17, 2023, 11:25am

Thanks @enchev
That improvement would be very helpful. Waiting for it.

enchev · May 17, 2023, 11:28am

Here is an app generated with our local build of Radzen Blazor Studio after several fixes related to your database:

The only additional code added is the ignore for ID columns:

In the official release later this week this additional code will be generated automatically as well.

Rk_M · May 17, 2023, 5:58pm

Now this is what I call... some "serious improvement". Can't thank you @enchev /@Team enough for this. I downloaded 1.9.6 today and started seeing lots of good thing...

One area to look at:
~ Model is generated like this... well, fine with that

But then, as we already know.... [ID] is identity column and the Key Column - [DATAMASTER_DB_ID] is a DB Computed Column [as 'DTM' + RIGHT('000000 + CAST([ID] as VARCHAR(6)) , 6) ]
so it is obvious that user cannot enter value for it from [Add/Edit] Screen... But it is appearing on both... (For Edit - I will recommend to simply make it a Read-Only Input - that's fine, small matter), but for ADD screen it simply not functioning because of this... (whether you force to put a value or leave empty)

Other than the problem above, I'd like get your feedback on 2 more things... (These are not show stopper like various other issues, I am bugging you all with, for past few days)... these are like, Nice to Have...

(a) Indeed I see now "TimeSpan" is getting mapped better in [Add/Edit]. Great! But its a "TextBox" could we try to bring the power of RadzenDatePicker component with TimeOnly mode. That'd be so nice.

(b) For foreign keys, Indeed I now see, Dropdown is appearing... once again "Excellent".... but may be we can provide some option to user, so that they can utilize another field look up. For example, in Below case, probably, I wouldn't display "DATAMASTER_DB_ID", rather I'd display [DATAMASTER_CODE] which is another unique key in Master Table and provides better insights to users...

~ But again.... These last 2 comments are just "good to have" kind of features. I see great improvisation in 1.9.6 - And once again please extend my "thanks" to the whole team!!!

enchev · May 18, 2023, 6:36am

Hi @Rk_M,

You are right! Fields like DATAMASTER_DB_ID should not be part of Add/Edit forms - will be fixed immediately! For TimeSpan we've decided to use RadzenMask component - the conversion between string/TimeSpan is easier. There is an option also to use DropDownDataGrid instead DropDown if you check "Paging, sorting and filtering of lookup data".

Rk_M · May 18, 2023, 9:15am

Thanks for 1st one (Computed column appearing as enabled input in Add/edit Screen) - Yes! if that gets fixed soon, it will be great.

No worries about other two.

Sure! Mask can be used... I'll probably have to play a little with its pattern and validation to make sure, no one entering: 99:99:99 for Time field and it should be restricted from [00-23]:[00-59]:[00-59] for hh:mm:ss
For lookup also no worries, I think I have some work around with it, just little coding but I'll be able to display something like... {DATAMASTER_ID : DATAMASTER_DB_ID} CONCAT in that dropdown.

Rk_M · May 19, 2023, 12:18pm

Hi @enchev
Firstly, I don't mean to sound impatient.
Just wanted to check with you about two open topics, and any tentative ETA for these...

(a) "... Fields like DATAMASTER_DB_ID should not be part of Add/Edit forms - will be fixed immediately! "
(b) From another thread... " it will be much better if we always prefix table name with the schema if the schema is not default one (dbo)! We will do that in our next update!"

enchev · May 19, 2023, 12:20pm

We are running final tests - the update should be available within an hour.

Rk_M · May 19, 2023, 12:23pm

You guys are awesome

Rk_M · May 20, 2023, 3:32am

I can see 1.9.7... tried it. But "No" fundamental problem is not at all resolved.

I noticed, now you eliminated computed column(s) from both add / edit page (probably it should have not been removed from EDIT page, at least the Key Column... over there it should have been just a read only attribute).

But, anyways, main problem was with "Add" and still "Add" is not functioning... Not sure what was actually tested.

Upon further check, I could see... Form is still trying to submit those derived columns and for sure failing to insert.

Could you please review again? Thanks much

enchev · May 20, 2023, 5:49am

You are right, this field should be available for Add page. We've corrected our template and the fix will be part of our update next week.

UPDATE: Actually I've spoke to soon. Even if we add this field to Add page you cannot enter anything since you will get: The column "DATAMASTER_DB_ID" cannot be modified because it is either a computed column or is the result of a UNION operator.
The problem is a bit different in my opinion - we will check what can do to support such scenario.

Rk_M · May 20, 2023, 6:43am

To avoid confusion... I reiterate the scenario, ---

Table Name: [MSI].[T_DM_DATAMASTER]
[ID]: Identity Column: Not a Primary Key. Value(s) Like, 1, 2, 3....
[DATAMASTER_DB_ID]: Derived Column (Based on ID) and Primary Key. Values Like, DTM000001...
Other Columns

Now In -
Display: We should display all fields. Up to user to decide if I want to throw out something.
Edit Page: We must display DB-computed fields - if it's a KEY column otherwise we MAY not need to. Any Derived (Including if its Key Column) should just appear as Read-only (i.e. Disabled Text Box Or Lebel etc.)
Add Page: No DB derived column such as [ID] / [DATAMASTER_DB_ID] should appear at all.

Since I have already shared my database script, it might be worth just validating it (Display/add/edit scenarios) once before pushing next release. From 1.9.4 to 1.9.7 nearly 4 releases could not fulfill it

enchev · May 20, 2023, 6:51am

Well, you've posted your report 4 days ago and we addressed your feedback for your fairly unusual data base setup continually. You have required, computed primary key and another identity field which is not part of any keys. As I already mentioned will be very hard to provide the exact combination of EF DbContext description along with proper UI to support such case.

Feel free to post what code should we generate to support such scenario. You can apply desired modification manually in the app generated by Radzen Blazor Studio to let us know how we should support adding records with such setup.

enchev · May 22, 2023, 7:19am

We did extensive research on this topic and I'm afraid that we are not aware of any solution how such table should be described in EF to allow adding of records using the standard approach. The only way around we can suggest is to use stored procedure for insert.

Rk_M · May 22, 2023, 5:00pm

Hi @enchev
I guess my comment was misunderstood. I didn't try to say - "The problem is easy or difficult". For sure your product knows it better than me. I just tried saying that I was getting one after another version daily, but I still could not manage to perform CRUD. Anyways... We knew EF 6.0 onward its playing a lot of drama but how we were tackling the issue earlier -- Yes for sure by using SP. (You might be recalling we had past discussion back in March).

I was extremely unsatisfied with that approach, and I reached out to Microsoft product team and then got some hints to modify our code to make it work....

PFB the simplest solution... its just 1 line of code in Context page (after whatever automatically getting generated from radzen studio/ blazer studio). In nutshell - for DB computed columns we should have a SetBeforeSaveBehavior.... (Also in 1.9.8 I saw you are pulling such DB computation in "HasComputedColumnSQL" - which I'd say, is nice, but in this occasion, redundant. Anyways, please have a look at the pic below. Also, I can share the solution if you want (probably you don't need )...

Rk_M · May 22, 2023, 5:12pm

Infact one point - I'd like further to correct about my screenshot comment. It has nothing to do with trigger. Just because its [KEY] column, we can't save a NULL therefore we need to allow DB to generate the value before Save - that's why SetBeforeSaveBehavior is needed.

enchev · May 22, 2023, 5:16pm

Sure, we will add this setting and it will be available in our next update. Maybe you can hint your contact in Microsoft to fix their own EF scaffolding to support the same.

Rk_M · May 22, 2023, 5:22pm

ha ha Wish I could control... If I could I'd have asked for some ready to build-in UI designer for SQL DB. Now in Azure MS bringing Databricks, Datalakes, parquet and their On-prem SQL not having any provision to communicate and neither with SSIS. We still need some external 3rd party drivers etc.