Dropdowndatagrid loads all the records

I have a dropdowndatagrid bound to an IQueryable. I have checked teh SQL Server Profiler, and the dropdowndatagrid generates 2 SQL statements:

SELECT TOP (1) 
    [c].[Id] AS [Id], 
    [c].[Abbreviation] AS [Abbreviation], 
    [c].[MemberState] AS [MemberState], 
    [c].[Name] AS [Name], 
    [c].[RowVersion] AS [RowVersion]
    FROM [dbo].[Countries] AS [c]
SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Abbreviation] AS [Abbreviation], 
    [Extent1].[MemberState] AS [MemberState], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[RowVersion] AS [RowVersion]
    FROM [dbo].[Countries] AS [Extent1]

So, no matter the PageSize I choose, all the records are loaded. As the documentation states:

DropDownDataGrid component can perform server-side sorting, paging and filtering when bound to IQueryable

I was expecting a query with a TOP clause to limit the number of records retrieved.

It seems that this is regression in our latest updates. It will be fixed in the next release.

Is this supposed to be fixed in v2.0.4? When changing between pages of the dropdowndatagrid no new SQL commands get executed

Yes, the issue was fixed:

One call for the first item, one call for count and one call to get data for the current page:

We will do our best to eliminate the need for first item call in the future.

You are right. It is fixed (definitely I need more sleep...)

Hello Vladimir,

the issue was fixed when there was no element selected in the dropdowndatagrid. When no element is selected I get this from a form that loads country data:

And it has:

One call for the first item, one call for count and one call to get data for the current page

as you said. However, when editing the same form and an item is selected I've got:

Where you can see:

  • Select top (2) -> the result of a SingleOrDefault query to get my entity (that has a FK to the country table)
  • The second select gets the selected country from the dropdowndatagrid
  • The call for the first item
  • The call to get all items (the one expanded in the bottom of the SQL Server Profiler)
  • One call to get the first iem again
  • One call to get the count
  • One call to get data for the current page

I also found out that when you filter in the dropdowndatagrid, the same search is triggered several times even if no letters changed from the filter.

We will do our best to fix your reports soon!

I've just published Radzen.Blazor 2.0.6 with optimizations for DropDownDataGrid queries. You will no longer have first item query as well.

Thank you Vladimir. It does not get all the records anymore! It loads the selected item twice but I can live with that.

The filtering does not trigger multiple similar queries, so that it is fixed too. Multiple similar queries when filtering also happens in a normal datagrid when you filter by a column.

The bad news is that v2.0.6 gives me this error that was not appearing in v2.0.5:

System.InvalidOperationException: The binary operator Equal is not defined for the types 'System.Object' and 'System.Int32'.
   at System.Linq.Expressions.Expression.GetEqualityComparisonOperator(ExpressionType binaryType, String opName, Expression left, Expression right, Boolean liftToNull)
   at System.Linq.Expressions.Expression.Equal(Expression left, Expression right, Boolean liftToNull, MethodInfo method)
   at System.Linq.Expressions.Expression.Equal(Expression left, Expression right)
   at System.Linq.Dynamic.Core.Parser.ExpressionHelper.GenerateEqual(Expression left, Expression right)
   at System.Linq.Dynamic.Core.Parser.ExpressionParser.ParseComparisonOperator()
   at System.Linq.Dynamic.Core.Parser.ExpressionParser.ParseLogicalAndOrOperator()
   at System.Linq.Dynamic.Core.Parser.ExpressionParser.ParseIn()
   at System.Linq.Dynamic.Core.Parser.ExpressionParser.ParseAndOperator()
   at System.Linq.Dynamic.Core.Parser.ExpressionParser.ParseOrOperator()
   at System.Linq.Dynamic.Core.Parser.ExpressionParser.ParseLambdaOperator()
   at System.Linq.Dynamic.Core.Parser.ExpressionParser.ParseNullCoalescingOperator()
   at System.Linq.Dynamic.Core.Parser.ExpressionParser.ParseConditionalOperator()
   at System.Linq.Dynamic.Core.Parser.ExpressionParser.Parse(Type resultType, Boolean createParameterCtor)
   at System.Linq.Dynamic.Core.DynamicExpressionParser.ParseLambda(ParsingConfig parsingConfig, Boolean createParameterCtor, ParameterExpression[] parameters, Type resultType, String expression, Object[] values)
   at System.Linq.Dynamic.Core.DynamicExpressionParser.ParseLambda(ParsingConfig parsingConfig, Boolean createParameterCtor, Type itType, Type resultType, String expression, Object[] values)
   at System.Linq.Dynamic.Core.DynamicQueryableExtensions.Where(IQueryable source, ParsingConfig config, String predicate, Object[] args)
   at Radzen.Blazor.RadzenDropDownDataGrid`1.SelectItemFromValue(Object value)
   at Radzen.DropDownBase`1.OnParametersSetAsync()
   at Microsoft.AspNetCore.Components.ComponentBase.CallOnParametersSetAsync()
   at Microsoft.AspNetCore.Components.ComponentBase.RunInitAndSetParametersAsync()

Please post more info how this DropDownDataGrid is bound

I'm trying to trace the problem. If I bound the DropDownDataGrid to an IQueryable it works fine. If I bound it to an IList it gives me that error.

I think I know how to handle this - it will be fixed in the next version!

This works if you uncoment the AsIQueryable():

@page "/"
@inherits IndexComponent
@layout MainLayout

<RadzenDropDownDataGrid TValue="int" FilterCaseSensitivity="FilterCaseSensitivity.CaseInsensitive"
                        AllowFiltering="true" AllowClear="true" Data="@countries" Change="@Change"
                        TextProperty="Name" ValueProperty="Id" @bind-Value="@selectedCountry" />

@code {
    public class Country2
    {
       public int Id { get; set; }
       public string Name { get; set; }
    }

    int selectedCountry = 1;
    IEnumerable<Country2> countries;

    protected override async Task OnInitializedAsync()
    {
       countries = new Country2[] {
          new Country2 { Id = 1, Name = "Country1" },
          new Country2 { Id = 2, Name = "Country2" },
       };//.AsQueryable();
    }

    void Change(object value)
    {
    }
}

but if not it fails with:

Error: System.InvalidOperationException: The binary operator Equal is not defined for the types 'System.Object' and 'System.Int32'.

at System.Linq.Expressions.Expression.GetEqualityComparisonOperator(ExpressionType binaryType, String opName, Expression left, Expression right, Boolean liftToNull)

at System.Linq.Expressions.Expression.Equal(Expression left, Expression right, Boolean liftToNull, MethodInfo method)

at System.Linq.Expressions.Expression.Equal(Expression left, Expression right)

at System.Linq.Dynamic.Core.Parser.ExpressionHelper.GenerateEqual(Expression left, Expression right)

at System.Linq.Dynamic.Core.Parser.ExpressionParser.ParseComparisonOperator()

at System.Linq.Dynamic.Core.Parser.ExpressionParser.ParseLogicalAndOrOperator()

at System.Linq.Dynamic.Core.Parser.ExpressionParser.ParseIn()

at System.Linq.Dynamic.Core.Parser.ExpressionParser.ParseAndOperator()

at System.Linq.Dynamic.Core.Parser.ExpressionParser.ParseOrOperator()

at System.Linq.Dynamic.Core.Parser.ExpressionParser.ParseLambdaOperator()

at System.Linq.Dynamic.Core.Parser.ExpressionParser.ParseNullCoalescingOperator()

at System.Linq.Dynamic.Core.Parser.ExpressionParser.ParseConditionalOperator()

at System.Linq.Dynamic.Core.Parser.ExpressionParser.Parse(Type resultType, Boolean createParameterCtor)

at System.Linq.Dynamic.Core.DynamicExpressionParser.ParseLambda(ParsingConfig parsingConfig, Boolean createParameterCtor, ParameterExpression[] parameters, Type resultType, String expression, Object[] values)

at System.Linq.Dynamic.Core.DynamicExpressionParser.ParseLambda(ParsingConfig parsingConfig, Boolean createParameterCtor, Type itType, Type resultType, String expression, Object[] values)

at System.Linq.Dynamic.Core.DynamicQueryableExtensions.Where(IQueryable source, ParsingConfig config, String predicate, Object[] args)

at Radzen.Blazor.RadzenDropDownDataGrid`1.SelectItemFromValue(Object value)

at Radzen.DropDownBase`1.OnParametersSetAsync()

at Microsoft.AspNetCore.Components.ComponentBase.CallOnParametersSetAsync()

at Microsoft.AspNetCore.Components.ComponentBase.RunInitAndSetParametersAsync()

It will work in the next version:

1 Like

Just published 2.0.7 - let me know if you spot anything!

The previous bug seems to be fixed, but now something that was also working in v2.0.5 is broken:

System.NotSupportedException: Unable to cast the type 'Manu.Application.Serialization.Contracts.BusinessPartnerLocationDto' to type 'System.Object'. LINQ to Entities only supports casting EDM primitive or enumeration types.
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.ValidateAndAdjustCastTypes(TypeUsage toType, TypeUsage fromType, Type toClrType, Type fromClrType)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.GetCastTargetType(TypeUsage fromType, Type toClrType, Type fromClrType, Boolean preserveCastForDateTime)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.CreateCastExpression(DbExpression source, Type toClrType, Type fromClrType)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.CastMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator1.Translate(ExpressionConverter parent, Expression linq) at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq) at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.Convert() at System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable1 forMergeOption)
at System.Data.Entity.Core.Objects.ObjectQuery1.<>c__DisplayClass41_0.<GetResults>b__1() at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
at System.Data.Entity.Core.Objects.ObjectQuery1.<>c__DisplayClass41_0.<GetResults>b__0() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func1 operation)
at System.Data.Entity.Core.Objects.ObjectQuery1.GetResults(Nullable1 forMergeOption)
at System.Data.Entity.Core.Objects.ObjectQuery1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__31_0() at System.Data.Entity.Internal.LazyEnumerator1.MoveNext()
at System.Collections.Generic.List1..ctor(IEnumerable1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at Radzen.Blazor.RadzenDropDownDataGrid1.LoadData(LoadDataArgs args)
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.AspNetCore.Components.ComponentBase.CallStateHasChangedOnAsyncCompletion(Task task)
at Radzen.Blazor.RadzenGrid1.Reload() at System.Threading.Tasks.Task.<>c.<ThrowAsync>b__139_0(Object state) at Microsoft.AspNetCore.Components.Rendering.RendererSynchronizationContext.ExecuteSynchronously(TaskCompletionSource1 completion, SendOrPostCallback d, Object state)
at Microsoft.AspNetCore.Components.Rendering.RendererSynchronizationContext.<>c.<.cctor>b__23_0(Object state)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at Microsoft.AspNetCore.Components.Rendering.RendererSynchronizationContext.ExecuteBackground(WorkItem item)

I'll try to find out what's failing

Please send me a project where I can reproduce and debug this. You are still using the old EF (6.x), not EF Core, right?

Yes, I'm using EF 6.4.0.
I'll prepare a project

Here it is Vladimir:

http://85.214.40.25/rz/BlazorRadzenTests.zip

Also note that if countries = null instead of countries = context.Countries in line 26 of index.razor, it will give this error too:

InvalidOperationException: The binary operator Equal is not defined for the types 'System.Object' and 'System.Int32'.

Both issues fixed in latest update.