Blazor Grid - Foreign Key Column with EF Core Navigation Property Resets Value

[Greg Wruck]

Hi,

I have found an issue that I can't get around when using the GridForeignColumn.

When I create my model classes, I normally create virtual navigation properties as illustrated below.

public partial class ControlAccount
{
    [Key]
    public Guid GUID { get; set; } = Guid.NewGuid();
    public int ID { get; set; }
    public string? Code { get; set; }
    public string? Description { get; set; }
    public string? Comments { get; set; }

    public Guid? WBSGUID_FK { get; set; }
    [ForeignKey("WBSGUID_FK")] public virtual WBS? WBS { get; set; }

    public Guid? OBSGUID_FK { get; set; }
    [ForeignKey("OBSGUID_FK")] public virtual OBS? OBS { get; set; }
}

However, I have found that if I use this entity to create a grid such as the following, I cannot change the ForeignKey columns. Refer to ControlAccountsPage.razor in the attached solution.  The attached video illustrates the behavior that allows me to change the foreign key value using the dropdown, but when I save the record, it reverts back to the previous value - I assume because the ForeignKey column does not change the model virtual navigation property.

<SfButton @onclick="SaveChanges">SAVE CHANGES</SfButton>
<SfButton @onclick="Requery">REQUERY</SfButton>
<SfGrid @ref="Grid" TValue="ControlAccount" DataSource="Items" AllowSelection="true" AllowSorting="true" >
    <GridEditSettings Mode="EditMode.Batch" AllowEditing="true"></GridEditSettings>
    <GridSelectionSettings Mode="SelectionMode.Cell"></GridSelectionSettings>
    <GridColumns> DataSource="Items"
        <GridColumn Field=@nameof(ControlAccount.ID)></GridColumn>
        <GridColumn Field=@nameof(ControlAccount.Code)></GridColumn>
        <GridColumn Field=@nameof(ControlAccount.Description)></GridColumn>
        <GridColumn Field=@nameof(ControlAccount.Comments)></GridColumn>
        <GridForeignColumn TValue="OBS" Field=@nameof(ControlAccount.OBSGUID_FK) HeaderText="Work Pack" ForeignDataSource="@OBSLU" ForeignKeyField=@nameof(OBS.GUID) ForeignKeyValue=@nameof(OBS.Code)></GridForeignColumn>
        <GridForeignColumn TValue="WBS" Field=@nameof(ControlAccount.WBSGUID_FK) HeaderText="WBS" ForeignDataSource="@WBSLU" ForeignKeyField=@nameof(WBS.GUID) ForeignKeyValue=@nameof(WBS.Code)></GridForeignColumn>
    </GridColumns>
</SfGrid>

The only way that I have been able to get around this problem (without changing my model classes) is to use a GridColumn with dropdown list Template and avoid the ForeignColumn altogether.  I then have access to events etc. where I can manually manipulate the virtual navigation property column.

Can you think of any other way around this?

Note that the attached solution will create a db in the MSSQLLocalDb.

Attachment: SyncfusionBlazorApp5_GridForeignKey_b73332d9.zip

[Monisha Saravanan]

Hi Greg,

Greetings from Syncfusion support.

We are validating your query at our end and we will update further details within two business days on or before 3^rd October 2022.

Until then we appreciate your patience.

Regards,

Monisha

[Monisha Saravanan]

Hi Greg,

Thanks for the patience.

We have checked your query and we suspect that the reported issue occurs due to the randomly generated Guid values.  We have checked the reported issue by using an integer value and it is working fine (i.e.) working as per our changed value in dropdown). So Instead of random values we suggest you to use static values in both foreignkey table and Grid datasorce table. Kindly check the attached sample and video demonstration for your reference.

<SfGrid @ref="Grid" TValue="ControlAccount" DataSource="Items" AllowSelection="true" AllowSorting="true" >

    <GridColumns> DataSource="Items"

        <GridForeignColumn TValue="OBS" Field="@nameof(ControlAccount.ID)" HeaderText="Fkey"  ForeignDataSource="@OBSLU" ForeignKeyField=@nameof(OBS.ID) ForeignKeyValue=@nameof(OBS.Code) ></GridForeignColumn>

        <GridForeignColumn TValue="OBS" Field=@nameof(ControlAccount.OBSGUID_FK) HeaderText="Work Pack" ForeignDataSource="@OBSLU" ForeignKeyField=@nameof(OBS.GUID) ForeignKeyValue=@nameof(OBS.Code)></GridForeignColumn>

    </GridColumns>

</SfGrid>

Sample: https://www.syncfusion.com/downloads/support/directtrac/general/ze/SY5C8F~11973976617.zip

Video: https://www.syncfusion.com/downloads/support/directtrac/general/ze/Video-Foreignkey-1501864841.zip

Please let us know if you have any concerns.

Regards,

Monisha

[Greg Wruck]

Hi Monisha, I have run your sample app and confirm that it works as expected with integers. However, my app uses guids exclusively for keys.  Can you please confirm that you observed the behavior I'm reporting with the Guids?

I don't think that the issue is due to the randomised data.  I get exactly the same problem when I use my static app data - that is why I created the sample app to confirm the behavior I was experiencing with static data.  If you suppress the drop / recreate / seed of the db at each run, you should be able to confirm that the problem exists with static data.

Is there anything else it could be?

[Greg Wruck]

Hi, I have done some more work on the sample that you sent through to understand it further and I think that the reason that your case worked is that the ID column that you linked the ForeignColumn to is not annotated with [ForeignKey] in the model.

I have created a new model for OBSID to test an integer key and have added an extra FK column to the grid view as follows and confirm that the problem is the same for Guid and Integer FK's.

    <GridColumns> DataSource="Items"
        <GridColumn Field=@nameof(ControlAccount.GUID) IsPrimaryKey="true" ></GridColumn>
        <GridColumn Field=@nameof(ControlAccount.ID) ></GridColumn>
        <GridColumn Field=@nameof(ControlAccount.Code)></GridColumn>
        <GridColumn Field=@nameof(ControlAccount.Description)></GridColumn>
        <GridForeignColumn TValue="OBS" Field="@nameof(ControlAccount.ID)" HeaderText="Fkey"  ForeignDataSource="@OBSLU" ForeignKeyField=@nameof(OBS.ID) ForeignKeyValue=@nameof(OBS.Code) ></GridForeignColumn>
        <GridForeignColumn TValue="OBSID" Field="@nameof(ControlAccount.OBSID_FK)" HeaderText="OBSID"  ForeignDataSource="@OBSIDLU" ForeignKeyField=@nameof(OBSID.ID) ForeignKeyValue=@nameof(OBSID.Code) ></GridForeignColumn>
        <GridForeignColumn TValue="OBS" Field=@nameof(ControlAccount.OBSGUID_FK) HeaderText="OBSGUID" ForeignDataSource="@OBSLU" ForeignKeyField=@nameof(OBS.GUID) ForeignKeyValue=@nameof(OBS.Code)></GridForeignColumn>
    </GridColumns>

However, I have now worked out exactly where the problem occurs - it is in the SaveChanges routine where I add the changed records to the context prior to saving and is probably not in the control itself.

        if (batchChanges.ChangedRecords.Any())
        {
            using (var db = await dbFact.CreateDbContextAsync())
            {
                foreach (var item in batchChanges.ChangedRecords)
                {
                    if (item != null)
                    {
                        db.ControlAccounts.Update(item);//***PROBLEM OCCURS HERE WHEN ATTATCHING THE CHANGED RECORD TO THE CONTEXT
//**There is a mismatch between the old navigation property and the new FK. 
//**EF Core updates the FK on the basis of the old navigation property - hence the change doesn't get saved.
                        await db.SaveChangesAsync();
                    }
                }
            }

Once I've understood and documented the problem, I'll post an updated solution.  I'm still not sure how to get around this problem.

[Monisha Saravanan]

Hi Greg,

Thanks for the update.

As per your last update we will wait to hear from you. If you face any difficulties at your end. Kindly get back to us. As always we will be happy to help you.

Regards,

Monisha