ShowEasy/BackEnd/EuroTran/SqlServerTest/Demos/8_JoinSql.cs

using OrmTest.Demo;
using OrmTest.Models;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace OrmTest.Demo
{
    /// <summary>
    /// Secure string operations
    /// </summary>
    public class JoinSql : DemoBase
    {
        public static void Init()
        {
            Where();
            OrderBy();
            SelectMerge();
            ConditionalModel();
            JoinExp();
        }

        private static void JoinExp()
        {
            var db = GetInstance();

            var exp= Expressionable.Create<Student>()
                .OrIF(1==1,it => it.Id == 11)
                .And(it=>it.Id==1)
                .AndIF(2==2,it => it.Id == 1)
                .Or(it =>it.Name == "a1").ToExpression();
            var list=db.Queryable<Student>().Where(exp).ToList();
            var list1 = db.Queryable<Student>().Where(exp).ToSql();
        }

        private static void ConditionalModel()
        {
            var db = GetInstance();
            List<IConditionalModel> conModels = new List<IConditionalModel>();
            conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "1" });//id=1
            conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Like, FieldValue = "1" });// id like '%1%'
            conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.IsNullOrEmpty });
            conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.In,FieldValue="1,2,3" });
            conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.NotIn, FieldValue = "1,2,3" });
            conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.NoEqual, FieldValue = "1,2,3" });
            conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.IsNot,FieldValue=null});// id is not null

            conModels.Add(new ConditionalCollections() { ConditionalList=new List<KeyValuePair<WhereType, SqlSugar.ConditionalModel>>()//  (id=1 or id=2 and id=1)
            {
                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();
            var student1 = db.Queryable<Student>().Where(conModels).ToSql();
        }

        private static void SelectMerge()
        {
            var db = GetInstance();
            //page join
            var pageJoin = db.Queryable<Student, School>((st, sc) => new object[] {
              JoinType.Left,st.SchoolId==sc.Id
            })
            .Where(st => st.Id==1)
            .Where(st => st.Id==2)
            .Select((st, sc) => new { id = st.Id, name = sc.Name })
            .MergeTable().Where(XXX => XXX.id == 1).OrderBy("name asc").ToList();// Prefix, is, not, necessary, and take the columns in select

            var pageJoin1 = db.Queryable<Student, School>((st, sc) => new object[] {
              JoinType.Left,st.SchoolId==sc.Id
            })
            .Where(st => st.Id == 1)
            .Where(st => st.Id == 2)
            .Select((st, sc) => new { id = st.Id, name = sc.Name })
            .MergeTable().Where(XXX => XXX.id == 1).OrderBy("name asc").ToSql();

        }
        private static void Where()
        {
            var db = GetInstance();
            //Parameterized processing
            string value = "'jack';drop table Student";
            var list = db.Queryable<Student>().Where("name=@name", new { name = value }).ToList();
            var list1 = db.Queryable<Student>().Where("name=@name", new { name = value }).ToSql();
            //Nothing happened
        }

        private static void OrderBy()
        {
            var db = GetInstance();
            //propertyName is valid
            string propertyName = "Id";
            string dbColumnName = db.EntityMaintenance.GetDbColumnName<Student>(propertyName);
            var list = db.Queryable<Student>().OrderBy(dbColumnName).ToList();
            var list1 = db.Queryable<Student>().OrderBy(dbColumnName).ToSql();

            //propertyName is invalid
            try
            {
                propertyName = "Id'";
                dbColumnName = db.EntityMaintenance.GetDbColumnName<Student>(propertyName);
                var list2 = db.Queryable<Student>().OrderBy(dbColumnName).ToList();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }
}