Scribbles for my bad memory

Introduction

When working with relational databases in .NET, one common requirement is to join related tables and map the results to objects. This can become cumbersome if done manually for each field. SqlSugar, a powerful ORM for .NET, simplifies this process by providing built-in methods for automatic mapping. This blog post will guide you through using SqlSugar’s Mapper feature to map related objects efficiently.

Setting Up Your Entities

Let’s start by defining two simple entities: Note and User. We want to map User information into each Note record based on a foreign key relationship.

public class Note
{
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
    public long Id { get; set; }
    public long UserId { get; set; }
    public string Content { get; set; } = string.Empty;

    [SugarColumn(IsIgnore = true)]
    public User? User { get; set; }  // Navigation property for User
}

public class User
{
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
    public long Id { get; set; }
    public string Username { get; set; } = string.Empty;
    public string? Gravatar { get; set; } = string.Empty;
}

Fetching and Mapping Data

Using SqlSugar, you can fetch data from the Note table and join it with the User table to get complete information for each note, including the associated user details. The Mapper method is key to simplifying this process.

Here’s how to write a method that retrieves paginated Note data along with related User information:

private async Task<PageData<Note>> GetPagedNotesAsync(int pageSize = 20, int pageNumber = 1,
    Expression<Func<Note, bool>>? filter = null, Expression<Func<Note, object>>? orderBy = null, bool isAsc = true)
{
    var pageData = new PageData<Note>
    {
        PageIndex = pageNumber,
        PageSize = pageSize
    };
    RefAsync<int> totalCount = 0;

    // Query with join and mapper
    var notes = await db.Queryable<Note, User>((n, u) => new JoinQueryInfos(
            JoinType.Inner, n.UserId == u.Id
        ))
        .WhereIF(filter != null, filter)
        .OrderByIF(orderBy != null, orderBy, isAsc ? OrderByType.Asc : OrderByType.Desc)
        .Mapper(n => n.User, n => n.UserId)  // Automatically map User based on UserId
        .ToPageListAsync(pageNumber, pageSize, totalCount);

    pageData.TotalCount = totalCount;
    pageData.DataList = notes;
    return pageData;
}

Key Steps Explained

• Join Query: The Queryable method with JoinQueryInfos specifies an inner join between the Note and User tables based on UserId.

• Conditional Clauses: WhereIF and OrderByIF conditionally apply filters and ordering based on the provided expressions, offering flexibility for dynamic queries.

• Automatic Mapping: The Mapper method maps the User object to the Note entity. It uses the foreign key UserId to establish this relationship, making it unnecessary to manually assign each field from the User table to the Note.

Conclusion

With SqlSugar’s Mapper feature, mapping related entities becomes a straightforward task. This reduces boilerplate code and improves code maintainability. By defining navigation properties and leveraging the power of Mapper, you can effortlessly join and map complex data structures, streamlining your development workflow.

Sharing this approach with fellow developers will not only save them time but also encourage the use of efficient, modern ORM practices in .NET applications.

Solution: put charset=utf8mb4 into the connection string.

            await Db.Queryable<Entity>().Where(_ => _.SampleFieldName== sampleFieldValue)
                .GroupBy("AnothterFieldNameInStringFormat")
                .Select<ResultModel>("AnotherFieldNameInStringFormat, Count(AnotherFieldNameInStringFormat) Total")
                .ToListAsync();