Dynamic splicing expression

Time:2022-5-4

Will we encounter the following query requirements in the project?

For example, you need to find members that meet the following conditions:

 Condition group 1: male members aged 30-40

Condition group 2: female members aged 20-30

Condition group 3: members aged 60-80 with Unknown Gender

There are and relationships within condition groups, but there are or relationships between condition groups.

Many programmers may think of using where to splice condition groups directly, just like the method shown in the following picture:

Generated SQL statement:

 

According to the generated SQL statements, we will find that the SQL statements directly spliced with where are and,

The original result we wanted was the yes or relationship between groups, but now it has become the and relationship, which obviously does not meet our query needs.

To meet our query requirements, we have to use the method of dynamic splicing conditions, which I usually useExpression

Finally, the SQL statement we generated is:

From the query statement, we can see that our query requirements are met, and the relationship between groups is or.

The following is an example code:       

Dynamic splicing expressionDynamic splicing expression

namespace MyTest
{
    /// 
    ///Dynamic splicing expression
    /// 
    public class ExpressionTest : ApplicationService
    {
        public readonly IRepository _memberRep;
        public ExpressionTest(IRepository memberRep)
        {
            _memberRep = memberRep;

        }


        /// 
        ///Use dynamic splicing expression -
        ///        
        public async Task Test()
        {
            #Region description

            /*         
             *Condition group 1: male members aged 30-40
             *Condition group 2: female members aged 20-30
             *Condition group 3: members aged 60-80 with Unknown Gender
             *There are and relationships within condition groups, but there are or relationships between condition groups. 
            */
            #endregion

            #Region encapsulates query criteria

            var param = new List
            {
                New searchmemberinputdto {sex = 1, agestart = 30, ageend = 40}, // men aged 30-40
                New searchmemberinputdto {sex = 2, agestart = 20, ageend = 30}, // women aged 20-30
                New searchmemberinputdto {sex = 0, agestart = 20, ageend = 30} // 60-80 years old, gender unknown
            };

            #endregion

            #Region dynamic splicing expression

            var members = (await _memberRep.GetAllAsync()).Where(a => a.GroupId == AbpSession.GroupId && a.IsDeleted == false);

            Expression> expressions = s => false;

            foreach (var item in param)
            {
                expressions = expressions.Or(s => s.Sex == item.Sex && s.Age >= item.AgeStart && s.Age <= item.AgeEnd);
            }

            members = members.Where(expressions);

            var memberList = members.ToList();

            #endregion
        }


        /// 
        ///Methods of testing errors
        ///        
        public async Task TestError()
        {
            #Region description

            /*         
             *Condition group 1: male members aged 30-40
             *Condition group 2: female members aged 20-30
             *Condition group 3: members aged 60-80 with Unknown Gender
             *There are and relationships within condition groups, but there are or relationships between condition groups. 
             *
             Note: this method is wrong and cannot meet the query requirements
            */

            #endregion

            #Region encapsulates query criteria

            var param = new List
            {
                New searchmemberinputdto {sex = 1, agestart = 30, ageend = 40}, // men aged 30-40
                New searchmemberinputdto {sex = 2, agestart = 20, ageend = 30}, // women aged 20-30
                New searchmemberinputdto {sex = 0, agestart = 20, ageend = 30} // 60-80 years old, gender unknown
            };

            #endregion            

            var members = (await _memberRep.GetAllAsync()).Where(a => a.GroupId == AbpSession.GroupId && a.IsDeleted == false);

            foreach (var item in param)
            {
                members = members.Where(s => s.Sex == item.Sex && s.Age >= item.AgeStart && s.Age <= item.AgeEnd);
            }

            var memberList = members.ToList();

        }

    }

    /// 
    ///Conditions
    /// 
    public class SearchMemberInputDto
    {

        /// 
        ///Gender
        ///0 - unknown; 1 - male; 2 - female
        /// 
        public int Sex { get; set; }

        /// 
        ///Age - start value
        /// 
        public int AgeStart { get; set; }

        /// 
        ///Age end value
        /// 
        public int AgeEnd { get; set; }
    }

}

View Code

   This is a very simple usage. I have only made a brief description here. I hope it will be helpful to you.