Efficiently Map Related Objects in SqlSugar
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
Queryablemethod withJoinQueryInfosspecifies an inner join between theNoteandUsertables based onUserId. -
Conditional Clauses:
WhereIFandOrderByIFconditionally apply filters and ordering based on the provided expressions, offering flexibility for dynamic queries. -
Automatic Mapping: The
Mappermethod maps theUserobject to theNoteentity. It uses the foreign keyUserIdto establish this relationship, making it unnecessary to manually assign each field from theUsertable to theNote.
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.