Powerful query by Ant Design Blazor and Efcore

YongQuan RAO — Sun, 11 Sep 2022 11:59:22 +0000

Ant Design Blazor is a worldwide popular Blazor component library. The component Table is particular powerful especially for Blazor Server project. It can seamlessly interact with Entity Framework, supporting pagination, sorting and filtering, and can get ranged and ordered dataset with very little effort. In this post, we will witness how easy to use it.

Step 1, we create a project with Visual studio 2022 Blazor Server template.

Add Ant Design reference by following https://github.com/ant-design-blazor/ant-design-blazor#import-ant-design-blazor-into-an-existing-project

Step 2, to make it simple, we can use code first to create Entity Framework dbContext and initialize some seed data with a separate sql script.

Add connectiong string to appsettings.json:

  "ConnectionStrings": {
    "DefaultConnection": "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=PropertyDB;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
  }

Define dbContext and entity:

    public class PropertyDbContext : DbContext
    {
        public PropertyDbContext(DbContextOptions<PropertyDbContext> options): base(options)
        {
        }

        public DbSet<Property> Properties { get; set; }
    }

    public class Property
    {
        public int Id { get; set; }
        public string Address { get; set; } = string.Empty;
        public string Suburb { get; set; } = string.Empty;
        public string Postcode { get; set; } = string.Empty;
        public string State { get; set; } = string.Empty;
    }

var config = new ConfigurationBuilder()
    .AddJsonFile("appsettings.json")
    .Build();
void BuildOptions(DbContextOptionsBuilder options) => options
    .UseSqlServer(config.GetConnectionString("DefaultConnection"));

builder.Services.AddDbContextFactory<PropertyDbContext>(BuildOptions);

Run migrations:

dotnet ef migrations add Initial
dotnet ef database update

Seed data:

INSERT INTO [dbo].[Properties]
           ([Address]
           ,[Suburb]
           ,[Postcode]
           ,[State])
     VALUES
           ('1 Elizabeth Rd', 'Sydney', '2000', 'NSW'),
           ('2 Elizabeth Rd', 'Sydney', '2000', 'NSW'),
           ('3 Elizabeth Rd', 'Sydney', '2000', 'NSW'),
           ('4 Elizabeth Rd', 'Sydney', '2000', 'NSW'),
           ('1 Queen St', 'Rosebery', '2018', 'NSW'),
           ('2 Queen St', 'Rosebery', '2018', 'NSW'),
           ('3 Queen St', 'Chatswood', '2067', 'NSW'),
           ('4 Queen St', 'Chatswood', '2067', 'NSW'),
           ('1 Ravenswood Ave', 'Carlingford', '2118', 'NSW'),
           ('2 Ravenswood Ave', 'Carlingford', '2118', 'NSW'),
           ('3 Ravenswood Ave', 'Epping', '2121', 'NSW'),
           ('4 Ravenswood Ave', 'Epping', '2121', 'NSW')

GO

For now, database and dbContext is ready.

Step 3, add Ant Design Table to razor page, and bind its data source to dbContext.

Index.razor.cs

    public partial class Index
    {
        [Inject]
        IDbContextFactory<PropertyDbContext> DbContextFactory { get; set; }

        private IQueryable<Property> Properties { get; set; }

        protected override async Task OnInitializedAsync()
        {
            var context = DbContextFactory.CreateDbContext();
            Properties = context.Properties;
        }
    }

Index.razor

<Table DataSource="Properties" TItem="Property">
    <Column TData="int"
            @bind-Field="context.Id"
            Sortable
            Filterable
            ShowSorterTooltip="false">
    </Column>

    <Column TData="string"
            Title="Address"
            @bind-Field="context.Address"
            Sortable
            Filterable
            ShowSorterTooltip="false">
    </Column>

    <Column TData="string"
            Title="Suburb"
            @bind-Field="context.Suburb"
            Sortable
            Filterable
            ShowSorterTooltip="false" SorterMultiple="5"/>

    <Column TData="string"
            Title="Postcode"
            @bind-Field="context.Postcode"
            Sortable
            Filterable
            ShowSorterTooltip="false" SorterMultiple="3">
    </Column>

    <Column TData="string"
            Title="State"
            @bind-Field="context.State"
            Sortable
            Filterable
            ShowSorterTooltip="false" SorterMultiple="4"/>
</Table>

That's all. Now let's see the effect. To verify it, we can refer to the sql query.

Scenario 1: pagination
AntDesign table by default uses page size = 10, which can be customized. When first time loaded, or page-navigated, it only fetches offset data range.

SQL:

exec sp_executesql N'SELECT [p].[Id], [p].[Address], [p].[Postcode], [p].[State], [p].[Suburb]
FROM [Properties] AS [p]
ORDER BY (SELECT 1)
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY',N'@__p_0 int,@__p_1 int',@__p_0=0,@__p_1=10

Scenario 2: sorting
Take column Postcode for instance.

SQL:

exec sp_executesql N'SELECT [p].[Id], [p].[Address], [p].[Postcode], [p].[State], [p].[Suburb]
FROM [Properties] AS [p]
ORDER BY [p].[Postcode]
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY',N'@__p_0 int,@__p_1 int',@__p_0=0,@__p_1=10

Scenario 3: filtering
Take column Id for instance.

SQL:

exec sp_executesql N'SELECT [p].[Id], [p].[Address], [p].[Postcode], [p].[State], [p].[Suburb]
FROM [Properties] AS [p]
WHERE ([p].[Id] > 5) AND ([p].[Id] < 10)
ORDER BY [p].[Id]
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY',N'@__p_0 int,@__p_1 int',@__p_0=0,@__p_1=10

Scenario 4: combination with multiple sorting and filtering

SQL:

exec sp_executesql N'SELECT [p].[Id], [p].[Address], [p].[Postcode], [p].[State], [p].[Suburb]
FROM [Properties] AS [p]
WHERE ([p].[Id] > 5) AND ([p].[Id] < 10)
ORDER BY [p].[Postcode], [p].[Suburb] DESC
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY',N'@__p_0 int,@__p_1 int',@__p_0=0,@__p_1=10

From the Blazor Server sample above, we can tell that by just simply binding the Table data source to Entity Framework dbContext, we can achieve data query pagination/offset, sorting and filtering. We can utilize this advantage for data read purpose even in production and it would significantly enhance database access performance and improve development productiveness.

GitHub: https://github.com/YongQuan-dotnet/AntTableSample

Build Blazor WebAssembly With Ant Design Blazor

YongQuan RAO — Sun, 29 Aug 2021 12:05:36 +0000

Introduction

In the past years, front-end web development has been dominated by Javascript-based framework React, Angular, and Vue, while Blazor web assembly(wasm), a production-ready front-end framework base on .Net runtime, has supplied another choice especially for C# developers. With the default Blazor wasm template, we can create a SPA with a nice-look UI. Alternatively, we can also use Ant Design Blazor, a modern Blazor template with high-quality components, to build rich and interactive interfaces. In this article, we will build Blazor projects with Ant Design of Blazor templates and components.

Create Blazor project with Ant Design Template

To start with it, we need firstly install Ant Design Template.
$ dotnet new --install AntDesign.Templates

With the template installed successfully, we can now create a Blazor project.
$ dotnet new antdesign -o MyAntDesignApp

Now we can run it with Visual Studio 2022.

We can add an ant-design button to see the effect.

There is also an option --full to create a project with comprehensive components.
$ dotnet new antdesign -o MyFullAntDesignApp --full

Import Ant Design of Blazor to Existing Project

We can also import Ant Design of Blazor to existing projects very easily.
Imagining we have an existing Blazor wasm project.

Firstly we install the package.
$ dotnet add package AntDesign

We can also find it through NuGet.

Then we register the service at Program.cs.
builder.Services.AddAntDesign();

Then we add the links of css and js to index.html.
<link href="_content/AntDesign/css/ant-design-blazor.css" rel="stylesheet" />
<script src="_content/AntDesign/js/ant-design-blazor.js"></script>

To use it globally, we can add the using statement at _Imports.razor.
@using AntDesign

To display the pop-up component dynamically, we need add the <AntContainer /> component in App.razor.

Now we can add an ant-design button as well.

Conclusion