Using the EF Core FromSqlRaw method in a Telerik Blazor grid

The extension method described here is available in version 1.10.0 of the Pixata.Blazor.TelerikComponents Nuget package. I made a sample project that shows the functionality (actually what I used to develop the method), although it should be noted that uses a local version of the method, not the one from the Nuget package.

My avid reader will no doubt remember my classic blog post on using Dapper to improve the performance with a Telerik Blazor grid. I’m still waiting for the Booker prize committee to contact me about it 😆.

As if that wasn’t exciting enough, I followed it with the revelation that EF Core can do just as well, possibly even slightly better. As that blog post ended up rather longer than I intended, I left the description of the new version of the extension method for another blog post. This is it 😎.

As the new method was based on the Dapper one, the usage is very similar. Therefore, a lot of this post will be very similar to the Dapper one (ie cut-and-paste job, watch out for the bits I forgot to change 🙄)

Hmm, just realised that I ended three consecutive paragraphs with an icon. That doesn’t bode well does it?

How to use the extension method

As before, I’m assuming you are using the Northwind database for this post. You’ll need to have created a DbContext and scaffolded at least the products table.

You’ll probably need a reference to the System.Data.SqlClient Nuget package, version 4.6.1. Not sure why, as my package should bring this in as a transient package, but when I tried it in a production app, I needed to add this reference.

Assuming you have references to both the Telerik.UI.for.Blazor and Pixata.Blazor.TelerikComponents packages (version 1.10.0 or higher), then your grid markup can look like this…

<TelerikGrid TItem="@Product"
             OnRead="@LoadData"
             ScrollMode="@GridScrollMode.Virtual"
             FilterMode="@GridFilterMode.FilterRow"
             Height="400px"
             RowHeight="40"
             PageSize="15">
  <GridColumns>
    <GridColumn Field="@nameof(Product.ProductName)" />
    <GridColumn Field="@nameof(Product.UnitPrice)" />
    <GridColumn Field="@nameof(Product.UnitsInStock)" />
  </GridColumns>
</TelerikGrid>

The LoadData method looks remarkably like the Dapper version, but takes a DbContext instead of a connection string…

private async Task LoadData(GridReadEventArgs args) {
  TelerikGridFilterResults<Product> data 
    = await args.GetData<Product>(_context, "Products", nameof(Product.UnitPrice));

The method is generic to the type that you use in the grid. The arguments to the method are…

The DbContext
The name of the table to be queried
The name of the default column to be used for sorting
You can add an optional argument to specify the sort direction, which by default is ascending. This argument is a value from the Telerik ListSortDirection enum

There is a further optional parameter, but I’ll leave that for the moment to keep things clear.

That’s all you need. The grid will load the data, and will respect the filtering and sorting options you set. As before, this is way faster than using regular EF Core code.

The return values

The code above ignores the values returned from the GetData method. Most of the time that’s fine, but you may want to do something extra with the data.

As a (slightly spurious) example, suppose you wanted to show some aggregate data below the grid. We will show the number of matching products, and the total value of their stock (ie the unit cost multiplied by the cost per unit).

When using EF Core, this is fairly easy with the built-in aggregates feature. When using FromSqlRaw, you have to roll your own code. The GetData method returns the information you need to do this.

The return value is a TelerikGridFilterResults<T> object (generic over the entity type) that contains three properties…

MatchingRows (int) – The number of rows that match the current filtering (which will be the total number of rows in the table if the user didn’t set any filtering)
SqlFilters (string) – The SQL needed to query the database with the same filtering
Parameters (SqlParameter[]) – The data values needed to pass as parameters

So, to add our aggregates, we define two properties to hold the values…

private int MatchingRows { get; set; }
private decimal TotalValue { get; set; }

We then capture the return values from the GetData method. As the method returns the number of rows, we can assign that directly to MatchingRows. We use the other two values to get the total value of stock…

private async Task LoadData(GridReadEventArgs args) {
  TelerikGridFilterResults<Product> data
    = await args.GetData<Product>(_context, "Products", nameof(Product.UnitPrice));
  MatchingRows = data.MatchingRows;
  TotalValue = MatchingRows == 0
    ? 0
    : await _context.Database
      .SqlQueryRaw<decimal>($"select sum(UnitsInStock * UnitPrice) as Value from products{data.SqlFilters}", data.Parameters)
      .SingleAsync();
}

As you can see, we use the value of data.MatchingRows directly for the number of rows that match, and make a separate database call to get the total cost of selected stock.

A couple of notes are in order here…

Instead of blindly querying the database, we check if the current filters matched any data. Apart from saving a database call if nothing matched, this also avoids an exception that I didn’t notice with the previous version. If a query does not return any data, then instead of the line of code returning zero (as I would have expected), an exception is thrown.
The attentive reader may notice that in the SQL, I named the return value as Value, which I didn’t do with the Dapper version. The reason for this is that the SQL that EF Core generates names it that way, and if you forget to add as Value to your SQL, you’ll get the perplexing exception "No column name was specified for column 1 of ‘t’. Invalid column name ‘Value’." This is understandable once you see the SQL that is generated, but took me a while to spot, as it never occurred to me that EF Core would modify my SQL.

Custom filtering

Sometimes you want to build your own filter for the Telerik grid. For example, we like using the filter row feature, but this only allows you to filter on one value, eg data whose date is after the entered value. What it doesn’t allow you to do is filter on a range, eg data whose date is between two dates.

In such cases, you can build your own filter using the <FilterCellTemplate> tag, as shown in the demos.

Whilst this works fine when using EF Core, it doesn’t work with FromSqlRaw, as all the data access is done manually, and the grid doesn’t know how to query the database. In this case, you need to tell the grid to rebind the data, passing your extra data values to the GetData method.

In a significant change from the Dapper version of the method, I decided to have the method accept a collection of CompositeFilterDescriptor objects (in the Telerik.DataSource namespace, and part of the Telerik Blazor package). This was done to allow much more powerful filtering…

More flexible filtering logic

The previous version of the code assumed that you wanted to do a logical && (logical “and”) on all of the filter criteria. This meant that if you wanted to do an || (logical “or”), you couldn’t. By creating a CompositeFilterDescriptor (CFD for short), you can do just this.

For example, suppose you want to filter the grid to show only products whose price is between 10 and 20 groats (or whatever unit of currency is assumed). You can create a CFD to do this. In order to cut down on the amount of boilerplate code you need to do this, I added a helper method, so all you need to do is…

private CompositeFilterDescriptor PriceTenToTwenty =>
  TelerikFilterHelper.CreateAnd(nameof(Product.UnitPrice),
    new OperatorValue(FilterOperator.IsGreaterThan, 10),
    new OperatorValue(FilterOperator.IsLessThan, 20));

I’ve wrapped this in a property to make it easier to use, but in reality you might not do it this way.

This can be passed to the GetData method as follows…

TelerikGridFilterResults<Product> data 
  = await args.GetData<Product>(_context, "Products",
                                nameof(Product.UnitPrice), [PriceTenToTwenty]);

Note that the method accepts a collection of CFDs, so you need to wrap them in some sort of collection. Here I’m using the fab new-ish collection initialiser syntax. If you’re on an older version of C#, use a List<CompositeFilterDescriptor> instead.

However, we haven’t gained a great deal, in that you could do this with the previous version of the method. The power comes when you want to use a logical “or” instead. Suppose you wanted to filter the data to show only products whose price was below 10 groats or more than 20. You can do that using a very similar helper method…

private CompositeFilterDescriptor PriceNotTenToTwenty =>
  TelerikFilterHelper.CreateOr(nameof(Product.UnitPrice),
    new OperatorValue(FilterOperator.IsLessThan, 10),
    new OperatorValue(FilterOperator.IsGreaterThan, 20));

As mentioned above, you can pass as many CFDs in as you like.

Filtering on multiple values

Another major improvement in this version is the ability to filter on multiple values. Suppose, under some bizarre set of circumstances you wanted to see products that contained the word “sauce” or “cha”, or that started with a “p” (I realise this is weird, it’s just an example 😆). You could use the CreateOr to do it as follows…

private CompositeFilterDescriptor WeirdNameFilter =>
  TelerikFilterHelper.CreateOr(nameof(Product.ProductName),
    new OperatorValue(FilterOperator.Contains, "sauce"),
    new OperatorValue(FilterOperator.Contains, "cha"),
    new OperatorValue(FilterOperator.StartsWith, "p"));

In reality, you would pick up the values and operators from controls in your UI, but this example should make it clear how it’s done.

The colleague that requested this feature had a use case where he was showing a list of people in a side bar, and wanted the grid to be filtered to entries associated with the selected user(s). The code shown above was exactly what he needed to achieve this.

Just to round things off, there is also a CreateSimple method that creates a simple CFD…

private CompositeFilterDescriptor NonZero =>
  TelerikFilterHelper.CreateSimple(nameof(Product.UnitPrice), )

This isn’t really needed, but is much neater than creating the CFD manually…

private CompositeFilterDescriptor PriceNotTen =>
  new() {
    FilterDescriptors = [
      new FilterDescriptor {
        Member = nameof(Product.UnitPrice),
        Operator = FilterOperator.IsNotEqualTo,
        Value = 10
      }
    ]
  };

I do have one more feature that I would like to add, but it’s a low priority, and will take some fiddling, so I’ll leave it for another day. Watch out for another ramble when/if I ever get it done!