ejs-grid with complex iqueryable datasource using url-adapter issue with PerformFiltering

[Anjum Habib]

Hello

i'm new here and using syncfusion.ej2 for datagrid in asp.net core MVC.

datasource of grid is a complex iqueryable with child (foreign key) of Account

data display is working fine but i got NullReference Exception on PerformFiltering method

here is my view code

allowPaging="true" allowSorting="true" allowResizing="true" allowFiltering="true">

here is my Controller Code

public IActionResult Index(string type)

{

ViewBag.Accounts = accountDb.GetAll().ToList();

return View("Index", type);

}

public IActionResult GetList([FromBody]DataManagerRequest dm, string type = "")

{

IQueryable DataSource = voucherDb.GetAll(type).IncludeReference(t => t.FromAccount, t => t.ToAccount);

DataOperations operation = new DataOperations();

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

{

DataSource = operation.PerformSearching(DataSource, dm.Search); //Search

}

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

{

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

}

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

{

DataSource = operation.PerformFiltering(DataSource, dm.Where, dm.Where[0].Condition);

}

int count = DataSource.Cast().Count();

if (dm.Skip != 0)

{

DataSource = operation.PerformSkip(DataSource, dm.Skip); //Paging

}

if (dm.Take != 0)

{

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

}

var list = DataSource.ToList()

return dm.RequiresCounts ? Json(new { items = list, result = list, count = count }) : Json(list);

}

here is my Voucher Class (Entity)

public class Voucher : BaseEntity

{

[Key]

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]

public long VoucherId { get; set; }

public long Sno { get; set; }

public long? FromAccountId { get; set; }

public long? ToAccountId { get; set; }

public DateTime Date { get; set; }

[StringLength(5)]

public string TransType { get; set; }

public decimal Amount { get; set; }

public long? CurrencyId { get; set; }

public decimal ConversionRate { get; set; }

[StringLength(500)]

public string Narration { get; set; }

[ForeignKey("FromAccountId")]

public virtual Account FromAccount { get; set; }

[ForeignKey("ToAccountId")]

public virtual Account ToAccount { get; set; }

}

here is my Account Class (Entity)

public class Account : BaseEntity

{

[Key]

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]

public long AccountId { get; set; }

public string Title { get; set; }

public string Description { get; set; }

public int Head { get; set; }

public int Type { get; set; }

}

kindly help me solve issue. if you can provide a sample as per my scenario it will be appreciated

Edit:

i also tried with QueryableOperation operation = new QueryableOperation(); ​ but  got same error

it is working fine with List<Voucher> as datasource. 

but i need IQueryable<Voucher> as datasource so that filter and other data operations will performed on database for better performance.

[Ajith Govarthan]

Hi Anjum, 

  

Thanks for contacting Syncfusion support. 

  

Query: 'm new here and using syncfusion.ej2 for datagrid in asp.net core MVC. datasource of grid is a complex iqueryable with child (foreign key) of Account data display is working fine but i got NullReference Exception on PerformFiltering method 

  

Based on your query you are facing null reference issue while filtering in your Grid application. So, before we proceed to your query, please share the below details to find the root cause of the issue.  

  

1. Share the complete Grid code example.

  

2. Have you used foreignkey column in your Grid application?

  

3. Are you facing the null reference issue for foreignkey column alone?

  

4. Share the datasource values bound to the Grid component?

  

5. Share the video demonstration of your requirement.

  

6. Share the Syncfusion package version.

  

Regards, 

Ajith G. 

[Anjum Habib]

I found scenario of my problem.

when i apply sorting with filter then it worked find but without sort expression it gives error.

Here is my Grid Code:

@{

    List<object> filterColumns = new List<object>();

    if (!string.IsNullOrWhiteSpace(Model))

    {

        filterColumns.Add(new { field = "TransType", matchCase = false, @operator = "equal", predicate = "and", value = Model });

    }

    List<object> sortColumns = new List<object>();

    sortColumns.Add(new { field = "Date", direction = "Descending" });

}

    <script type="text/javascript">

        function transTypeFilterMenu(e, str) {

            debugger;

            if (e == "Select All" || str == "Select All") {

                return '';

            }

            return '<span id="TypeStr">' + str + '</span>'

        }

    </script>

    <script type="text/x-jsrender" id="transTypeTemplate">

        <span id="Trusttext">${TransType}</span>

    </script>

<ejs-grid id="Grid"

                                  allowPaging="true"

                                  allowSorting="true"

                                  allowResizing="true"

                                  allowFiltering="true">

                            <e-grid-filtersettings type="Menu" columns="filterColumns"></e-grid-filtersettings>

                            <e-grid-sortsettings columns="sortColumns"></e-grid-sortsettings>

                            <e-data-manager url="@Url.Action("GetList","Voucher",new { @area="Transactions"})" crossdomain="true" adaptor="UrlAdaptor"></e-data-manager>

                            <e-grid-columns>

                                <e-grid-column field="Sno" headerText="S.No" textAlign="Left" width="85"></e-grid-column>

                                <e-grid-column field="Date" format='dd/MM/yyyy' headerText="Date" textAlign="Left" width="120"></e-grid-column>

                                <e-grid-column field="FromAccountId" foreignKeyField="AccountId" foreignKeyValue="Title" dataSource="@ViewBag.Accounts" headerText="From Account" width="170"></e-grid-column>

                                @*<e-grid-column field="ToAccount.Title" headerText="To Account" width="170"></e-grid-column>*@

                                <e-grid-column field="ToAccountId" foreignKeyField="AccountId" foreignKeyValue="Title" dataSource="@ViewBag.Accounts" headerText="To Account" width="170"></e-grid-column>

                                <e-grid-column field="Amount" format="N2" headerText="Amount" textAlign="Right" width="140"></e-grid-column>

                                <e-grid-column field="TransType" headerText="Trans Type" textAlign="Center" filter="@(new { type="CheckBox", itemTemplate="${ transTypeFilterMenu(data.TransType, data.TransType) }"})" template="#transTypeTemplate" width="130"></e-grid-column>

                                @*<e-grid-column field="TransType" foreignKeyField="Key" foreignKeyValue="Description" dataSource="@ViewBag.VoucherTypes" filter="@(new { type="Excel"})" headerText="Trans Type" width="130"></e-grid-column>*@

                                <e-grid-column field="Narration" headerText="Narration" textAlign="Left" width="250"></e-grid-column>

                                <e-grid-column headerText="Action" freeze="Right" allowFiltering="false" allowSorting="false" allowSearching="false" textAlign="Center" width="85" template="#actionColumnTemplate"></e-grid-column>

                            </e-grid-columns>

                        </ejs-grid>

Have you used foreignkey column in your Grid application?

         Yes i'm using 2 foreignKey columns

Are you facing the null reference issue for foreignkey column alone?

        No ​i'm getting null reference issue on all columns (but when there is any sort expression it worked fine.)

 Share the datasource values bound to the Grid component?

       I'm using Voucher class (given above in original post)

Share the Syncfusion package version

      i'm using syncfusion ver 18.4.30

----------------------------

I'm getting another issue with same grid.

when i apply filter on foreignKey column (FromAccountId or ToAccountId) checkbox filter menu (on TransType column) doesn't appear (its continuous loading...)

but when i apply any other filter then it works fine

[Ajith Govarthan]

Hi Anjum Habib, 

Thanks for the update. 

 

Query: when i apply sorting with filter then it worked find but without sort expression it gives error. 

 

We have checked your Syncfusion package version and found that you have used the older version of packages. We have provided support for complex field filtering and sorting in the package version 19.1.54. So, to perform the sorting and filtering operation properly we suggest you update your packages to latest version. Please refer the below documentation for your reference. 

UG- links: 

( latest version) -https://ej2.syncfusion.com/javascript/documentation/release-notes/19.2.49/?type=all  

 https://ej2.syncfusion.com/javascript/documentation/release-notes/19.1.54/?type=all#grid 

Regards, 

Ajith G. 

[Anjum Habib]

I have updated syncfusion version to 19.2.44 but i got same issue. 

that is i got null reference exception when i apply filter on foreignKey colum without sort expression.

i have applied a trick (not a solution) to handle exception that when a filter is applied and if there is no sort expression i have added a default sort

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

{

     if (dm.Sorted == null || dm.Sorted.Count <= 0)

    {

        dm.Sorted = new List<Sort> { new Sort { Direction = "ascending", Name = "Sno" } };

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

    }

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

}

i got another issue (i mentioned earlier as well) when i apply a filter on foreignKey column i cannot get filter options on another column (i.e TransType). i have tried both filter types checkbox & Excel.

but if i change filter type of ForeignKey column to Excel then it works fine.

kindly help me resolve both issue so i can continue my work

[Ajith Govarthan]

Hi Anjum, 

Thanks for the update. 

Query: I have updated syncfusion version to 19.2.44 but i got same issue. that is i got null reference exception when i apply filter on foreignKey colum without sort expression.i have applied a trick (not a solution) to handle exception that when a filter is applied and if there is no sort expression i have added a default sort 

 

Based on your query you are facing some issues after updating the Syncfusion packages to latest version. So, to find the root cause of the issue please share the video demonstration of the reported issue and if possible, please share the issue reproducible sample to provide you the prompt solution at the earliest. 

Regards, 

Ajith G. 

[Anjum Habib]

Hello

1st problem solved by removing code that include reference entities in DataSource

IQueryable DataSource = voucherDb.GetAll(type).IncludeReference(t => t.FromAccount, t => t.ToAccount);

below code resolve issue

IQueryable DataSource = voucherDb.GetAll(type);

============================

But 2nd issue still exists. 

i.e when i apply filter on ForeignKey column another column filter menu (checkbox type) doesn't appear (i continually loading....)

ForeignKey column filter type is 'Menu' 

and for Checkbox filter i'm using template 

i also tried to change filter of Checkbox with and without template but it didn't work. 

but if i change ForeignKey column filter type to 'Excel' checkbox filter start working.....

this behavior is annoying to me. 

if you people can help me solve this issue. as i may need this in future in my project.

 

[Sujith Kumar Rajkumar]

Hi Anjum, 

 

We are glad to hear that your first problem has been resolved. 

 

As for your second problem – “when i apply filter on ForeignKey column another column filter menu (checkbox type) doesn't appear (i continually loading”, we checked this case with foreign key columns but unfortunately were still unable to reproduce it from our end. You can check the below sample for your reference, 

 

Sample: https://www.syncfusion.com/downloads/support/forum/167114/ze/GridURLAdaptorWithForeignKey-1588583320 

 

So please share us the following information to identify your exact problem case and validate further, 

 

• Share us a video demonstration of the problem to understand the problem better.
• Let us know how you have referenced the script and style file for the EJ2 controls in your application.
• If possible share us a simple sample to replicate the problem or try reproducing it in the above provided sample.

 

Regards, 

Sujith R