Difficulty with DataFilter / Filtering Datagrid

Radzen Blazor Studio
1

I'd like to filter the records returned when my page with the datagrid loads.

To be clear, I'd like to filter all the records being pulled from the database, I am not trying to preset a user controllable filter on the UI. In my example code below, I'm trying to filter and return records only where the Zip is 38111.

I've been trying to get the code working in the DataFilter with OData service page in my app but keep getting "Object reference not set to an instance of an object" errors in the browser.

My Datagrid (truncated):

        <RadzenDataGrid @ref="grid0" ColumnWidth="200px"  AllowFiltering="true" FilterMode="FilterMode.Advanced" AllowPaging="true" AllowSorting="true" ShowPagingSummary="true" PageSizeOptions=@(new int[]{5, 10, 20, 30})
            Data="@newOpportunities" Count=count LoadData=@Grid0LoadData  TItem="WestElmCRM.Server.Models.WestElmCRMTenantDB.NewOpportunity" RowSelect="@EditRow" >
            <Columns>
                <RadzenDataGridColumn TItem="WestElmCRM.Server.Models.WestElmCRMTenantDB.NewOpportunity" Property="Date_Created" Title="Date Created">
                </RadzenDataGridColumn>
                <RadzenDataGridColumn TItem="WestElmCRM.Server.Models.WestElmCRMTenantDB.NewOpportunity" Property="Street" Title="Street">
                </RadzenDataGridColumn>
                <RadzenDataGridColumn TItem="WestElmCRM.Server.Models.WestElmCRMTenantDB.NewOpportunity" Property="City" Title="City">
                </RadzenDataGridColumn>
                <RadzenDataGridColumn TItem="WestElmCRM.Server.Models.WestElmCRMTenantDB.NewOpportunity" Property="State" Title="State">
                </RadzenDataGridColumn>
                <RadzenDataGridColumn TItem="WestElmCRM.Server.Models.WestElmCRMTenantDB.NewOpportunity" Property="Zip" Title="Zip">
                </RadzenDataGridColumn>

And my code (also truncated):

        RadzenDataFilter<WestElmCRM.Server.Models.WestElmCRMTenantDB.NewOpportunity> dataFilter;

        protected override async Task OnAfterRenderAsync(bool firstRender)
        {
            await base.OnAfterRenderAsync(firstRender);

            if (firstRender)
            {
                await dataFilter.AddFilter(new CompositeFilterDescriptor()
                {
                    //Property = "Stage.Name",
                    //FilterValue = "Incoming",
                    Property = "Zip",
                    FilterValue = "38111",
                    FilterOperator = FilterOperator.Equals
                });
            }
        }

        protected async Task Grid0LoadData(LoadDataArgs args)
        {
            try
            {
                var filter = args.Filter;
                if (dataFilter.Filters.Any())
                {
                    filter = !string.IsNullOrEmpty(filter) ?
                        $"({dataFilter.ToODataFilterString()}) and ({filter})" : dataFilter.ToODataFilterString();
                }

                //var result = await WestElmCRMTenantDBService.GetNewOpportunities(filter: $@"(contains(Street,""{search}"") or contains(Unit_Number,""{search}"") or contains(City,""{search}"") or contains(State,""{search}"") or contains(Carrier_Route,""{search}"") or contains(County,""{search}"") or contains(APN,""{search}"") or contains(Owner_1_Name,""{search}"") or contains(Owner_2_Name,""{search}"") or contains(Mailing_Care_Of_Name,""{search}"") or contains(Mailing_Address,""{search}"") or contains(Mailing_Unit_Number,""{search}"") or contains(Mailing_City,""{search}"") or contains(Mailing_State,""{search}"") or contains(Mailing_Carrier_Route,""{search}"") or contains(Mailing_County,""{search}"") or contains(Property_Class,""{search}"") or contains(Property_Type,""{search}"") or contains(Pool_Type,""{search}"") or contains(Last_Sale_Buyer_Name_1,""{search}"") or contains(Last_Sale_Buyer_Name_2,""{search}"") or contains(Prior_Sale_Buyer_Name_1,""{search}"") or contains(Prior_Sale_Buyer_Name_2,""{search}"") or contains(Loan_1_Type,""{search}"") or contains(Loan_1_Lender,""{search}"") or contains(Loan_1_Rate_Type,""{search}"") or contains(Loan_2_Type,""{search}"") or contains(Loan_2_Lender,""{search}"") or contains(Loan_2_Rate_Type,""{search}"") or contains(Loan_3_Type,""{search}"") or contains(Loan_3_Lender,""{search}"") or contains(Loan_3_Rate_Type,""{search}"") or contains(Loan_4_Type,""{search}"") or contains(Loan_4_Lender,""{search}"") or contains(Loan_4_Rate_Type,""{search}"") or contains(Assigned_To,""{search}"") or contains(Assigned_Cold_Caller,""{search}"") or contains(Assigned_Offer_Writer,""{search}"") or contains(Assigned_Contract_Writer,""{search}"") or contains(Assigned_Closer,""{search}"") or contains(Assigned_TC_Coordinator,""{search}"") or contains(Assigned_Disposition_Coordinator,""{search}"") or contains(Closed_Lost_Reason_Description,""{search}"") or contains(Previously_Assigned_To,""{search}"") or contains(Offer_Seller_Carryback_Financing_Terms,""{search}"") or contains(Offer_Other_Amount_Description,""{search}"") or contains(COE_Days_Or_Date,""{search}"") or contains(TC_Name,""{search}"") or contains(TC_Email,""{search}"") or contains(TC_Phone,""{search}"") or contains(Buyer_Name,""{search}"") or contains(Buyer_Business_Name,""{search}"") or contains(Buyer_Email,""{search}"") or contains(Buyer_Phone,""{search}"") or contains(MLS_Status,""{search}"") or contains(MLS_Agent_Name,""{search}"") or contains(MLS_Agent_Phone,""{search}"") or contains(MLS_Agent_Email,""{search}"") or contains(MLS_Brokerage_Name,""{search}"") or contains(MLS_Brokerage_Phone,""{search}"") or contains(Lien_Type,""{search}"")) and {(string.IsNullOrEmpty(args.Filter)? "true" : args.Filter)}", expand: "AspNetUser,AspNetUser1,AspNetUser2,AspNetUser3,AspNetUser4,AspNetUser5,AspNetUser6,ClosedLostReason,Stage,AspNetUser7,Stage1", orderby: $"{args.OrderBy}", top: args.Top, skip: args.Skip, count:args.Top != null && args.Skip != null);
                //var result = await WestElmCRMTenantDBService.GetNewOpportunities(filter: $@"(contains(Street,""{search}"") or contains(Lien_Type,""{search}""))", expand: "AspNetUser,AspNetUser1,AspNetUser2,AspNetUser3,AspNetUser4,AspNetUser5,AspNetUser6,ClosedLostReason,Stage,AspNetUser7,Stage1", orderby: $"{args.OrderBy}", top: args.Top, skip: args.Skip, count: args.Top != null && args.Skip != null);
                var result = await WestElmCRMTenantDBService.GetNewOpportunities(filter: filter, expand: "AspNetUser,AspNetUser1,AspNetUser2,AspNetUser3,AspNetUser4,AspNetUser5,AspNetUser6,ClosedLostReason,Stage,AspNetUser7,Stage1", orderby: $"{args.OrderBy}", top: args.Top, skip: args.Skip, count: args.Top != null && args.Skip != null);

                newOpportunities = result.Value.AsODataEnumerable();

From the line number in the browser error, I suspect that the "dataFilter" is never being created.

If I break out the CompositeFilterDescriptor creation, it indeed errors on the "await dataFilter" line:

                var cfd = new CompositeFilterDescriptor();
                cfd.Property = "Zip";
                cfd.FilterValue = 38111;
                cfd.FilterOperator = FilterOperator.Equals;

                await dataFilter.AddFilter(cfd);

How can I troubleshoot this further? I'm not entirely clear how my code is differing from the example.

Thanks in advance.

2

Hi @DB1234,

Do you have a RadzenDataFilter component in the page? Our demo uses one and the code you are trying to make run requires it.

If you don't want to use RadzenDataFilter you can check the newly introduced Query Builder feature. Or even add the filter expression:

filter = !string.IsNullOrEmpty(filter) ? $"(Zip eq '38111') and ({filter})" : filter;
3

Thanks for the reply, I think I understand now that I didn't have a RadzenDataFilter.

I was able to find the correct syntax for a static value:

var result = await WestElmCRMTenantDBService.GetNewOpportunities(filter: $@"(Zip eq 38111)", expand: <truncated>

When I try to use the Query Builder feature I get:

"Unexpected character at position 13"

It appears to be referring to the closing parenthesis because if I put a space in front of it:

filter: $@"(Zip eq 38111 )",

I get:

"Unexpected character at position 14"

4

It is probably the parenthesis. You can try removing them and the Query Builder should work. We will try to support this case as well.

UPDATE:

What is the type of Zip? If it is a string the expression should be as I have said in my first reply: Zip eq '38111'. Strings in OData should be quoted.

5

Thanks, Zip is an Integer in the database.

I can confirm that removing the parenthesis works:

filter: $@"Zip eq 38111", expand: <truncated>

Is it possible to filter by a field from a related Foreign Key / related table in the Expand?

I have a related table called "Stage". My current table has "Stage_ID" but I would like to filter by "Stage.Name"

I could filter on the ID value from my current table:

filter: $@"Stage_ID eq 1", expand: <truncated>

...but this hardcodes the ID.

I was hoping to get this code to work, but it doesn't seem to:

filter: $@"(Stage.Name eq 'Incoming')", expand: <truncated>
6

You need to use / to access child properties:

Stage/Name eq 'Incoming'

And Stage should be included in the Expanded property.

7

Thanks, to be clear, "Stage" is in the "expand:" section already.

Unfortunately adding the slash returns zero rows:

filter: $@"Stage/Name eq 'Incoming'", expand: <truncated>

I've set a breakpoint but all I can see at that point is the result is zero rows.

Is there anything else I can try to debug further?

8

Not really. The filter is correct. Check if the data in the database is actually Incoming and not something else.

9

Thanks, the table has 187 rows and the following query returns 159:

  select s.Name, no.* 
  from NewOpportunities no 
  inner join Stages s 
  on no.Stage_ID = s.ID
  where s.Name = 'Incoming'
10

Not sure what the problem could be then. Check the generated SQL in the Output window. Also inspect the URL of the HTTP request that is being made - you can check in the the browser's developer tools.

11

I switched back to the Static Zip filter to have something to compare against:

Static Zip, VS Output:

Request finished HTTP/2 GET https://localhost:5001/odata/WestElmCRMTenantDB/NewOpportunities?$filter=Zip+eq+38111&$top=10&$skip=0&$expand=AspNetUser%2cAspNetUser1%2cAspNetUser2%2cAspNetUser3%2cAspNetUser4%2cAspNetUser5%2cAspNetUser6%2cClosedLostReason%2cStage%2cAspNetUser7%2cStage1&$count=true - - - 200 - application/json;+odata.metadata=minimal;+odata.streaming=true 1701.0893ms

Static Zip, Browser Dev Tools:

https://localhost:5001/odata/WestElmCRMTenantDB/NewOpportunities?$filter=Zip+eq+38111&$top=10&$skip=0&$expand=AspNetUser%2cAspNetUser1%2cAspNetUser2%2cAspNetUser3%2cAspNetUser4%2cAspNetUser5%2cAspNetUser6%2cClosedLostReason%2cStage%2cAspNetUser7%2cStage1&$count=true

Stage.Name VS Output:

Request finished HTTP/2 GET https://localhost:5001/odata/WestElmCRMTenantDB/NewOpportunities?$filter=Stage%2fName+eq+%27Incoming%27&$top=10&$skip=0&$expand=AspNetUser%2cAspNetUser1%2cAspNetUser2%2cAspNetUser3%2cAspNetUser4%2cAspNetUser5%2cAspNetUser6%2cClosedLostReason%2cStage%2cAspNetUser7%2cStage1&$count=true - - - 200 - application/json;+odata.metadata=minimal;+odata.streaming=true 1437.5725ms

Stage.Name Browser Dev Tools:

https://localhost:5001/odata/WestElmCRMTenantDB/NewOpportunities?$filter=Stage%2fName+eq+%27Incoming%27&$top=10&$skip=0&$expand=AspNetUser%2cAspNetUser1%2cAspNetUser2%2cAspNetUser3%2cAspNetUser4%2cAspNetUser5%2cAspNetUser6%2cClosedLostReason%2cStage%2cAspNetUser7%2cStage1&$count=true

One note that there are two Foreign Keys between the NewOpportunities table and the Stage table.

In the Expand section, these are represented by "Stage" and "Stage1". They were autogenerated by the Infer database feature.

12

You can try with Stage1/Name in that case.

13

Great idea that ended up being it.

Interestingly enough, I had trimmed down the list in the "expand" section, and "Stage1" wasn't in it, only "Stage" and it still worked.

I can see now where the mapping from the database fields to Object properties happens in the DBContext.

@korchev is there any insight as to why the naming during infer ended up this way?

One FK named "Previous_Stage_ID" ended up being "Stage"
The other FK named "Stage_ID" ended up being "Stage1".

I'm fine to leave them and not change them, but just wondering.

14

Radzen Blazor Studio needs to generate unique property names hence it appends 1 after the second Stage.

15

That makes sense, I was wondering if there was a technical reason why it didn't try to retain more of the column name, changing "Previous_Stage_ID" to "PreviousStageID" versus "Stage".

Wondering if I'm misunderstanding a convention or standard with the tech stack.

16

The original foreign key property remains too. The entity property is added as a convenience. Consider the following

    [Table("Invoices", Schema = "public")]
    public partial class Invoice
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        [Required]
        public int OrderId { get; set; }

        public Order Order { get; set; }
    }

The Invoice table has OrderId column only. The Order property name is deduced from the related table (Orders).