Prevent Duplicates with Custom DataAdaptor

[Joseph Murray]

Hi Support,

I'm using the SfGrid with a Custom DataAdaptor that interfaces with my DbContext for CRUD activities.  I have a column in my Entity that is configured as a Unique Index in my ModelBuilder.  When I add a row to the grid and I don't use a unique name, I don't receive any feedback from the UI other than the entry doesn't save when I change focus.  There are a couple of other items on this forum that have a similar issue but there doesn't seem to be a way to address this using the DataAdaptor.  

Referenced Issue

The first attempt I made was to use an EventCallback from my DataAdaptor component, which you can see in the InsertAsync function below.  That does not work.   When I register a handler for this in my Grid razor component, it never gets called.  
I also attempted to register the GridEvents OnActionFailure event and that does not fire when the DataAdaptor fails to insert.  

My preference would be to be able to handle this in the DataAdaptor where I could return the database error to the UI if it fails that validation but I don't see how that could be done with the overloaded InsertAsync function.  

I want to use the DataAdaptor for this work if possible as well as not loading my entities twice inside the razor control (one for the grid and one for the validation) to do this.  

My Grid:
```

<SfGrid TValue="VariableDefinition" Toolbar="@(new List<string>() { "Add", "Edit", "Delete", "Cancel", "Update" })" AllowResizing="true" AllowPaging="true">

    <GridEditSettings AllowAdding="true" AllowDeleting="true" AllowEditing="true" />

    <GridColumns>

        <GridColumn Field=@nameof(VariableDefinition.Id) HeaderText="Id" IsPrimaryKey="true" TextAlign="TextAlign.Right" Width="120" Visible="false" />

        <GridForeignColumn TValue="VariableCategory" Field=@nameof(VariableDefinition.VariableCategoryId) HeaderText="Category Name" ForeignKeyValue="Name" ForeignKeyField="Id" Width="150" >

            <SfDataManager Adaptor="Adaptors.CustomAdaptor">

                <TaurisDataAdaptor TValue="VariableCategory" WhereClause="@whereClause" />

            </SfDataManager>

        </GridForeignColumn>

        <GridColumn Field=@nameof(VariableDefinition.Name) HeaderText="Name" TextAlign="TextAlign.Right" Width="120" />

        <GridColumn Field=@nameof(VariableDefinition.DisplayName) HeaderText="Display Name" TextAlign="TextAlign.Right" Width="120" />        <GridColumn Field=@nameof(VariableDefinition.AggregationMethod) HeaderText="Aggregation Method" EditType="EditType.DropDownEdit" />

    </GridColumns>

    <GridEvents OnActionBegin="ActionBeginHandler" TValue="VariableDefinition"></GridEvents>

    <SfDataManager Adaptor="Adaptors.CustomAdaptor">

        <TaurisDataAdaptor TValue="VariableDefinition" OnResponse="OnResponse" />

    </SfDataManager>

</SfGrid>

```

My Custom DataAdaptor:

```

public class TaurisDataAdaptor<TValue> : Syncfusion.Blazor.DataAdaptor where TValue : class

{

    [Inject] IDbContextFactory<SystemDbContext> _dbContextFactory { get; set; } = default!;

    [Parameter] public string WhereClause { get; set; } = default!;

    [Parameter] public EventCallback<DataAdaptorResponse> OnResponse { get; set; }

    public override async Task<object> ReadAsync(DataManagerRequest dm, string? key = null)

    {

        using var context = await _dbContextFactory.CreateDbContextAsync();

        DbSet<TValue> dbSet = context.Set<TValue>();

        IEnumerable<TValue> DataSource = await dbSet.ToListAsync();

        DataResult DataObject = new();

        if (dm.Search != null && dm.Search.Count > 0)

        {

            // Searching

            DataSource = DataOperations.PerformSearching(DataSource, dm.Search);

        }

        if (dm.Sorted != null && dm.Sorted.Count > 0)

        {

            // Sorting

            DataSource = DataOperations.PerformSorting(DataSource, dm.Sorted);

        }

        if (dm.Where != null && dm.Where.Count > 0)

        {

            // Filtering

            DataSource = DataOperations.PerformFiltering(DataSource, dm.Where, dm.Where[0].Operator);

        }

        int count = DataSource.Cast<TValue>().Count();

        if (dm.Skip != 0)

        {

            //Paging

            DataSource = DataOperations.PerformSkip(DataSource, dm.Skip);

        }

        if (dm.Take != 0)

        {

            DataSource = DataOperations.PerformTake(DataSource, dm.Take);

        }

        return dm.RequiresCounts ? new Syncfusion.Blazor.Data.DataResult() { Result = DataSource, Count = count, } : (object)DataSource;

    }

    public override async Task<object> InsertAsync(DataManager dataManager, object record, string additionalParam)

    {

        if (record is null)

        {

            return null!;

        }

        List<TValue> entities = new List<TValue>()

        {

            (TValue)record

        };

        using var context = await _dbContextFactory.CreateDbContextAsync();

        await context.AddRangeAsync(entities);

        try

        {

            await context.SaveChangesAsync();

        }

        catch (Exception e)

        {

            await OnResponse.InvokeAsync(new DataAdaptorResponse()

            {

                IsError = true,

                Exception = e,

                Message = e.Message

            });

            throw e;

        }

        return record;

    }

    public override async Task<object> RemoveAsync(DataManager dataManager, object primaryColumnValue, string primaryColumnName, string additionalParam)

    {

        using var context = await _dbContextFactory.CreateDbContextAsync();

        var entity = context.Set<TValue>().Find(primaryColumnValue);

        context.Remove(entity);

        await context.SaveChangesAsync();

        return primaryColumnValue;

    }

    public override async Task<object> UpdateAsync(DataManager dataManager, object record, string primaryColumnName, string additionalParam)

    {

        using var context = await _dbContextFactory.CreateDbContextAsync();

        var entity = record as TValue;

        var updatedEntity = context.Update(entity);

        await context.SaveChangesAsync();

        return updatedEntity;

    }

}

public class DataAdaptorResponse

{

    public bool IsError {get;set;} = false;

    public string Message {get;set;} = default!;

    public Exception Exception {get;set;}

} ```

Thanks for your consideration.

Joe

[Prathap Senthil]

Hi Joseph,

Based on your reported issue, we suggest using the IsIdentity property in the primary column to avoid duplicate keys. If the primary key column value is auto-incremented in your database, kindly define the IsIdentity property of the GridColumn as true for the PrimaryKey column. When IsIdentity is enabled, it will be considered a read-only column when editing or adding a record. Refer to the provided documentation for further details.


API Link: https://help.syncfusion.com/cr/blazor/Syncfusion.Blazor.Grids.GridColumn.html#Syncfusion_Blazor_Grids_GridColumn_IsIdentity

If you don’t want the above suggestion, we recommend using the RowUpdating event to check for duplicate data. If duplicate data is detected, cancel the add action using args.Cancel = true. You can then display an error message or customize the action as needed. Please refer to the code snippet and documentation below for further reference.

<SfGrid @ref="@Grid" TValue="Order" ID="Grid" AllowPaging="true" Toolbar="@(new List<string>() { "Add", "Edit","Update","Delete","Cancel" })" Height="315">

              <SfDataManager AdaptorInstance="@typeof(CustomAdaptor)" Adaptor="Adaptors.CustomAdaptor"></SfDataManager>

              <GridEditSettings AllowAdding="true" AllowDeleting="true" AllowEditing="true" Mode="EditMode.Normal"></GridEditSettings>

              <GridPageSettings PageSizes="true"></GridPageSettings>

 

              <GridEvents RowUpdating="RowUpdatingHandler" TValue="Order"></GridEvents>

             

</SfGrid>

 

@code {

 

 

              public void RowUpdatingHandler(RowUpdatingEventArgs<Order> args)

              {

                             //if duplicates records finds using the args.Cancel property to cancel the add opertion.

              }

Reference:
https://blazor.syncfusion.com/documentation/datagrid/events#rowupdating


Regards,

Prathap S