0. Preface

In the previous article, we introduced the addition, deletion, modification and query of SqlSugar in detail, which has satisfied our use in daily engineering development. However, there are a few methods that are not commonly used in development, but are very useful. Let's see what else is interesting.

1. Unusual query

Previously, we introduced the query for a single table, which is also a relatively simple query mode. Although the development is sufficient, it will inevitably encounter some special situations. The following methods are to solve these unexpected problems.

1.1 multi table query

SqlSugar provides a special multi table query scheme, using IQueryable interface. Let's see how it works:

ISugarQueryable<T, T2> Queryable<T, T2>(Expression<Func<T, T2, object[]>> joinExpression);
ISugarQueryable<T, T2> Queryable<T, T2>(ISugarQueryable<T> joinQueryable1, ISugarQueryable<T2> joinQueryable2, Expression<Func<T, T2, bool>> joinExpression)
            where T : class, new()
            where T2 : class, new();
ISugarQueryable<T, T2> Queryable<T, T2>(ISugarQueryable<T> joinQueryable1, ISugarQueryable<T2> joinQueryable2, JoinType joinType, Expression<Func<T, T2, bool>> joinExpression)
            where T : class, new()
            where T2 : class, new();
ISugarQueryable<T, T2> Queryable<T, T2>(Expression<Func<T, T2, bool>> joinExpression) where T : class, new();

These methods belong to SqlSugar client class. SqlSugar provides method support of up to 12 generics. Of course, there are few associated queries that can meet 5 tables in the development. Unless it's a reporting process, you need to review whether the data table model is reasonable. Taking these four methods as examples, this paper introduces how to use multi table query:

Let's start with two model classes:

public class Person
{
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { 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; }
}

Briefly describe the relationship between two classes: an employee identity corresponds to a person, but a person does not necessarily have an employee identity.

OK, let's start with the first method:

var query = context.Client.Queryable<Person, Employee>((pr, em)=>new object[]
{
    JoinType.Left,
    em.PersonId == pr.Id
});

The first return is the connection method of two tables, for example: Left represents Left connection, Inner represents Inner connection, Right represents Right connection; the second return is the connection basis between two tables. This is a fixed form, which returns an Object array. The first is the connection method, and the second is the field through which to connect.

The generated SQL is similar to the following:

SELECT `pr`.`Id`,`pr`.`Name`,`pr`.`Age` FROM `Person` pr Left JOIN `Employee` em ON ( `em`.`PersonId` = `pr`.`Id` ) 

The second method:

var query = context.Client.Queryable(context.Client.Queryable<Person>(),
                context.Client.Queryable<Employee>(),
                (pr, em) => pr.Id == em.PersonId);

This method uses inner join to join two tables, and the last parameter is used to specify the join fields between the two tables.

The generated SQL is similar to the following:

SELECT  `pr`.`Id` AS `Person.Id` , `pr`.`Name` AS `Person.Name` , `pr`.`Age` AS `Person.Age` , `em`.`Id` AS `Employee.Id` , `em`.`Name` AS `Employee.Name` , `em`.`PersonId` AS `Employee.PersonId` , `em`.`DeptId` AS `Employee.DeptId`  FROM  (SELECT `Id`,`Name`,`Age` FROM `Person`  ) pr  Inner JOIN  (SELECT `Id`,`Name`,`PersonId`,`DeptId` FROM `Employee`  ) em   ON ( `pr`.`Id` = `em`.`PersonId` )  

The third method, based on the second method, specifies the connection method:

var query = context.Client.Queryable(context.Client.Queryable<Person>(),
                context.Client.Queryable<Employee>(),
                JoinType.Left,
                (pr, em) => pr.Id == em.PersonId);

the last one:

var query = context.Client.Queryable<Person, Employee>((pr, em) => pr.Id == em.PersonId);

Directly specify the contact between the two tables.

It should be noted that all methods only return a queryable object. If no subsequent projection (select) is performed, the primary key conflict may be prompted. Moreover, all methods will not be executed immediately before ToXXX.

1.2 query function

SqlSugar adds many of our common methods so that they can be mapped to sql statements. Let's look at what we support:

public class SqlFunc
{
    public static TResult AggregateAvg<TResult>(TResult thisValue);//Take the average statistics for this column
    public static int AggregateCount<TResult>(TResult thisValue);// Count the number of columns equal to count(x) in SQL
    public static int AggregateDistinctCount<TResult>(TResult thisValue);/ Return the quantity after de duplication
    public static TResult AggregateMax<TResult>(TResult thisValue);//Return maximum
    public static TResult AggregateMin<TResult>(TResult thisValue);// Return minimum
    public static TResult AggregateSum<TResult>(TResult thisValue);// Return total
    public static bool Between(object value, object start, object end);// Determine whether the value of the column is between two values
    public static int CharIndex(string findChar, string searchValue);// charindex of SQL
    public static bool Contains(string thisValue, string parameterValue);// Include or not
    public static bool ContainsArray<T>(T[] thisValue, object InField);// Whether the array contains
    public static bool ContainsArray<T>(List<T> thisValue, object InField);//List Sophie contains
    public static bool ContainsArrayUseSqlParameters<T>(List<T> thisValue, object InField);//
    public static bool ContainsArrayUseSqlParameters<T>(T[] thisValue, object InField);//
    public static DateTime DateAdd(DateTime date, int addValue, DateType dataType);// Time add
    public static DateTime DateAdd(DateTime date, int addValue);// Date add
    public static bool DateIsSame(DateTime date1, DateTime date2);// Is the time the same
    public static bool DateIsSame(DateTime? date1, DateTime? date2);//Is the time the same
    public static bool DateIsSame(DateTime date1, DateTime date2, DateType dataType);//Determine whether the time is the same according to DateType
    public static int DateValue(DateTime date, DateType dataType);// Return specific time value according to dateType
    public static bool EndsWith(string thisValue, string parameterValue);//Does the string end with some values
    public static bool Equals(object thisValue, object parameterValue);//Equal or not
    public static DateTime GetDate();//Return current database time
    public static string GetRandom();//
    public static TResult GetSelfAndAutoFill<TResult>(TResult value);//
    public static bool HasNumber(object thisValue);//Return whether greater than 0 and cannot be Null
    public static bool HasValue(object thisValue);// Is there a value and is not Null
    public static CaseThen IF(bool condition);// if judgment in sql
    public static TResult IIF<TResult>(bool Expression, TResult thenValue, TResult elseValue);// case when
    public static TResult IsNull<TResult>(TResult thisValue, TResult ifNullValue);// IsNull in sql
    public static bool IsNullOrEmpty(object thisValue);//Judge whether it is Null or empty
    public static int Length(object value);//Take the length
    public static TResult MappingColumn<TResult>(TResult oldColumnName, string newColumnName);// Column name mapping
    public static string MergeString(string value1, string value2);
    public static string MergeString(string value1, string value2, string value3, string value4);
    public static string MergeString(string value1, string value2, string value3, string value4, string value5);
    public static string MergeString(string value1, string value2, string value3, string value4, string value5, string value6);
    public static string MergeString(string value1, string value2, string value3);
    public static string MergeString(string value1, string value2, string value3, string value4, string value5, string value6, string value7);
    public static string Replace(object value, string oldChar, string newChar);// replace
    public static bool StartsWith(string thisValue, string parameterValue);
    public static Subqueryable<T> Subqueryable<T>() where T : class, new();
    public static string Substring(object value, int index, int length);// Get substring
    public static bool ToBool(object value);//Type conversion
    public static DateTime ToDate(object value);// Type conversion
    public static decimal ToDecimal(object value);// Type conversion
    public static double ToDouble(object value);// Type conversion
    public static Guid ToGuid(object value);// Type conversion
    public static int ToInt32(object value);// Type conversion
    public static long ToInt64(object value);// Type conversion
    public static string ToLower(object thisValue);// Type conversion
    public static string ToString(object value);// Type conversion
    public static TimeSpan ToTime(object value);// Type conversion
    public static string ToUpper(object thisValue);// Type conversion
    public static string Trim(object thisValue);// Remove leading and trailing spaces
}

Most of the methods here are simple and direct, so I will not demonstrate them one by one.

1.3 dynamic query

The query conditions we wrote before are fixed, at least we know what the final query conditions are when programming. But in the development process, sometimes it is not so early to know the final query conditions or the query needs to adjust the query conditions according to the user's input, so how to achieve it?

Common solutions are as follows:

• Use SQL statements to dynamically splice SQL statements, and then execute the return results according to the SQL statements
• Dynamic splicing of Lambda expressions when using Lambda expressions
• Get the IQueryable interface, and then query according to the condition adding method

These three methods have their own advantages and disadvantages. The obvious problem of using the query interface is that it has higher permissions for the application layer, and the same is true for using SQL statements. So it is more logical to use dynamic splicing of Lambda expressions.

Of course, SqlSugar provides two other solutions based on these three solutions:

It is the iconditional model and WhereIF mentioned in the previous article. Let's take a look at how IConditionalModel is used:

var conditions = new List<IConditionalModel>();
var query = context.Client.Queryable<Person>().Where(conditions);

You can pass in the IConditionModel type in Where. SqlSugar provides two supported implementation classes:

public class ConditionalCollections : IConditionalModel
{
    public ConditionalCollections();
    public List<KeyValuePair<WhereType, ConditionalModel>> ConditionalList { get; set; }
}
public class ConditionalModel : IConditionalModel
{
    public ConditionalModel();
    public string FieldName { get; set; }
    public string FieldValue { get; set; }
    public ConditionalType ConditionalType { get; set; }
    public Func<string, object> FieldValueConvertFunc { get; set; }
}

For the sibling ConditionModel in a collection, it means that the query conditions are all and relationships. ConditionCollections are different, where ConditionList represents a collection of key value pairs. The key is of type WhereType and ConditionModel is the value. Let's talk about where type:

public enum WhereType
{
    And = 0,
    Or = 1
}

And, Or. How to understand? That is to say, whether this key value pair is and Or with the previous relationship model.

Here's an example:

// and id=100 and (id=1 or id=2 and id=1) 
conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "100" });
conModels.Add(new ConditionalCollections() { ConditionalList=
new List<KeyValuePair<WhereType, SqlSugar.ConditionalModel>>()
{
    new  KeyValuePair<WhereType, ConditionalModel>
    ( WhereType.And ,
    new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "1" }),
    new  KeyValuePair<WhereType, ConditionalModel> 
    (WhereType.Or,
    new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "2" }),
    new  KeyValuePair<WhereType, ConditionalModel> 
    ( WhereType.And,
    new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "2" })
}
});
var student = db.Queryable<Student>().Where(conModels).ToList();

Continue to look at WhereIF, which is relatively simple to use:

ISugarQueryable<T> WhereIF(bool isWhere, Expression<Func<T, bool>> expression);

Example code:

var query = context.Client.Queryable<Person>().WhereIF(string.IsNullOrEmpty(input), p=>p.Age>10);

It's also easy to understand. If the result of the first parameter is False, the subsequent query will not be executed, otherwise it will be executed.

2. Some advanced playing methods

In addition to adding, deleting, and querying, SqlSugar also provides some other interesting mechanisms. Let's continue our exploration.

2.1 batch operation

SqlSugar provides a mode of recording many operations at once and then submitting them for execution. The previous operations only support batch insertion, batch modification and batch deletion. In this mode, SqlSugar also supports batch (insert, modify, delete). In other words, in a batch, you can insert, modify, or delete.

Let's see how to make this function work for us:

void AddQueue();

This method is available in IDeleteable, IInsertable, iupdatable and ISugarQueryable. Once this method is called, it means that the instruction will not be executed immediately until it is called SqlSugarClient.SaveQueues(). Save to the database by calling SaveQueues().

It should be noted that:

2.2 business

SQL itself supports transactions, most ORM supports transactions, and SqlSugar is no exception. How does SqlSugar implement a transaction on its own?

In sqlsugar client:

public void BeginTran();

Sqlsugar client will be marked as a transaction, indicating that subsequent operations are in the transaction until the transaction is committed or rolled back.

In SimpleClient:

public ITenant AsTenant();

Return an ITenant instance, and then submit or rollback the transaction through this instance.

2.3 native SQL execution

SqlSugar adds support for native Sql in many places.

For example, you can use Sql statements to query in the following ways:

var t12 = context.Client.SqlQueryable<Student>("select * from student").Where(it=>it.id>0).ToPageList(1, 2);

Execute SQL in this way:

context.Client.Ado.ExecuteCommand(sql, parameters)

Then, execute the stored procedure in the following ways:

context.Client.Ado.UseStoredProcedure()

3. Summary

Good ORM always has a variety of solutions, but also a variety of advantages. SqlSugar, so far, can come to an end. Of course, I still have some left to explore and excavate. Next, I will take Dapper as the final content of the C data operation series. After that, we will take you to understand and learn in the form of projects asp.net core.