C ා data operation series - 14 in depth exploration of SqlSugar

0. Preface

In the last article, we know how to use SqlSugar, but we just know how to use it, as if walking in chains, which obviously does not meet the quality of a qualified programmer. Therefore, in this article, we will dig deep into it and explore the secrets behind it.

1. Fancy mapping

In the actual development, the entity class in the program and the table name of the database are not completely consistent, resulting in many reasons, for example, the team has different requirements for the database name and the program name, the database is established first and the program is developed later, or the program only uses a part of the table in the database and so on.

At this time, it is against the C ා convention that is superior to configuration, but it also conforms to the design philosophy of C ා because configuration is also a part of C ා. How can we establish the relationship between tables and entity classes from a practical point of view?

Let me take you to see if SqlSugar can complete this part gracefully:

1.1 Attribute setting

SqlSugar prefabricates some attributes, allowing us to establish a relationship between entity tables and database tables through attributes:

  • Sugarcable: data table used to define entity class mapping
public SugarTable(string tableName);
public SugarTable(string tableName, string tableDescription);

This is the two constructors of SugarTable, which allow setting the table name and data table description

  • SugarColumn: used to define the relationship between properties and columns in the data table
public string ColumnDataType { get; set; }// Data type of column, fill in data type of SQL
public string OldColumnName { get; set; }// It is used to generate database after table update. Fill in the original column name here
public bool IsNullable { get; set; }// Set whether the column is allowed to be NULL
public int Length { get; set; } // Set the data length of the column
public string ColumnDescription { get; set; }// Set the description name of the column
public bool IsIdentity { get; set; } 		// Set whether the column is an auto increment column
public bool IsPrimaryKey { get; set; }		//Set the column as primary key
public bool IsIgnore { get; set; }			// Without database operation, true will not query, add, etc
public string ColumnName { get; set; }		// Set the corresponding column name
public string DefaultValue { get; set; }	// Set the default value for this column

SqlSugar's Attribute configuration is very simple. You only need to configure the mapping of classes and tables and the mapping of columns for attributes.

1.2 dynamic configuration

Like EF, SqlSugar also supports dynamic configuration. Let me see how to realize dynamic configuration:

SqlSugar supports less dynamic configuration functions. It is better to design the database in advance, and then use dynamic configuration to make association.

Set the dynamic configuration of data table through SugarClient:


The methods are as follows:

public void Add(string entityName, string dbTableName);
public void Add(string entityName, string dbTableName, string dbTableShortName);
public void Add(MappingTable table);

Then set the dynamic configuration of columns through SugarClient:


The methods are as follows:

public void Add(string propertyName, string dbColumnName, string entityName);
public void Add(MappingColumn col);

Obviously, dynamic configuration does not support setting the rest of the column. Of course, SugarClient can also configure ignore fields:


The specific implementation method is as follows:

public void Add(string propertyName, string EntityName);
publiv void Add(IgnoreColumn col);

1.3 As alias mode

When SqlSugar is adding, deleting, modifying and querying, it adds alias processing to the data entity. Just use the method As(string newName).

Queryable<T>().As("newName")   //select * from newName

Alias query similar to SQL

2. Foreign key Association

Instead of setting the formal loading of navigation attributes in SqlSugar, a Mapper method is added: when querying, Mapper is called to map foreign key relationships to achieve the function of loading navigation attributes together.

First of all, it should be noted that in SqlSugar, the navigation property needs to be configured to be ignored to avoid being directly resolved to SQL, otherwise, it will prompt Sequence contains no elements.

Add several sample classes:

public class Person
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
    /// <summary>
    /// </summary>
    [SugarColumn(IsIgnore = true)]
    public Employee Employee { get; set; }

public class Employee
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
    public int Id { get; set; }
    public string Name { get; set; }
    public int PersonId { get; set; }
    [SugarColumn(IsIgnore = true)]
    public Person Person { get; set; }
    public int DeptId{get;set;}
    [SugarColumn(IsIgnore = true)]
    public Dept Dept{get;set;}

public class Dept
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
    public int Id { get; set; }
    public string Name { get; set; }
	[SugarColumn(IsIgnore = true)]
    public List<Employee> Employees{get;set;}

Use the Context class from the previous article:

public class DefaultContext
    public SqlSugarClient Client { get; }

    public DefaultContext(string connectionString, DbType dbType)
        Client = new SqlSugarClient(new ConnectionConfig
            ConnectionString = connectionString,//"Data Source=./demo.db",
            DbType = dbType,
            IsAutoCloseConnection = true,
            InitKeyType = InitKeyType.Attribute
        // ==New

        Client.CodeFirst.InitTables<Person, Employee, Dept>();
        Client.Aop.OnLogExecuting = (sql, paramters) => 

A brief introduction,

For InitTables, SqlSugar provides many overloaded versions, but the following three are recommended:

void InitTables(string entitiesNamespace);
void InitTables(string[] entitiesNamespaces);
void InitTables(params Type[] entityTypes);

In the first two, you can contract the namespace of entity classes, and then initialize all entity classes at once. The third initializes the incoming entity class type instance, or traverses the required class according to certain rules.

OnLogExecuting is a listening event of SqlSugar (although it is not an event, I think it is better to write it as an event mode). Its function is to monitor the SQL statements executed by the framework, which can be used for debugging or log monitoring.

Use Mapper to query one-to-one mapping types:

var results = context.Client.Queryable<Employee>().Mapper(t=>t.Person, p=>p.PersonId).ToList();

Use Mapper to query one to many mapping types:

var results = context.Client.Queryable<Dept>().Mapper(p => p.Employees, p => p.Employees.First().DeptId).ToList();

It should be noted that these two are fixed writing methods.

Among them, one-to-one requirements must start from the main object. The main object is to hold a foreign key to point to another table.

The one to many requirement starts from the section that owns the collection property (that is, "one") and the association is indicated as the collection. First(). Foreign key.

Another point is that the navigation properties of SqlSugar must be loaded manually and will not be loaded automatically, so there is no deep recursion problem at all.

3. Summary

In this article, we explore how to define the mapping relationship between tables and entity classes. In the next article, we will write a practical template class, including the basic database query function. The above is the content of this article, looking forward to the follow-up

Please pay attention to more My blog Mr. Gao's Cabin

Tags: C# Database SQL Attribute less

Posted on Sat, 23 May 2020 06:04:55 -0400 by Zeon