TheLazy.Dev
Portfolio GitHub

Blog

antdesign |blazor |dotnet |efcore

Powerful query by Ant Design Blazor and Efcore

yongquan

YongQuan RAO

Posted on September 11, 2022

Powerful query by Ant Design Blazor and Efcore

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.

Image description

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"
  }
Enter fullscreen mode Exit fullscreen mode

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;
    }
Enter fullscreen mode Exit fullscreen mode

Register dbContext:

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

builder.Services.AddDbContextFactory<PropertyDbContext>(BuildOptions);
Enter fullscreen mode Exit fullscreen mode

Run migrations:

dotnet ef migrations add Initial
dotnet ef database update
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
        }
    }
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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.
Image description
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
Enter fullscreen mode Exit fullscreen mode

Scenario 2: sorting
Take column Postcode for instance.

Image description
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
Enter fullscreen mode Exit fullscreen mode

Scenario 3: filtering
Take column Id for instance.

Image description

Image description
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
Enter fullscreen mode Exit fullscreen mode

Scenario 4: combination with multiple sorting and filtering
Image description
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
Enter fullscreen mode Exit fullscreen mode

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

💖 💪 🙅 🚩
yongquan
YongQuan RAO

Posted on September 11, 2022

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related

Powerful query by Ant Design Blazor and Efcore
antdesign Powerful query by Ant Design Blazor and Efcore

September 11, 2022