Skip to content

1.Queryable

Jump to bottom Edit New page
sunkaixuan edited this page Jun 18, 2019 · 80 revisions

1.Query conditions

1.1 By expression

//id=@id
var list=db.Queryable<Student>().Where(it => it.Id == id).ToList();
var list2=db.Queryable<Student>().WhereIF(id>0,it => it.Id == id).ToList();// if id>0  sql+=id=@id

//id=@id or name like '%'+@name+'%'
var list2 = db.Queryable<Student>().Where(it => it.Id == id||it.Name.Contains("jack")).ToList();


//Create expression 
var exp= Expressionable.Create<Student>()
                .And(it=>it.Id==1)
                .Or(it =>it.Name.Contains("jack")).ToExpression();
var list=db.Queryable<Student>().Where(exp).ToList();

1.2 By Sql

//id=@id
var list=db.Queryable<Student>().Where("id=@id",new { id=1}).ToList();
//id=@id or name like '%'+@name+'%'
var list2 = db.Queryable<Student>().Where("id=@id or name like '%'+@name+'%' ",new { id=1,name="jack"}).ToList();

1.3 By Dynamic

//id=1
var conModels = new List<IConditionalModel>();
conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "1" });//id=1
var student = db.Queryable<Student>().Where(conModels).ToList();

//Complex use case
List<IConditionalModel> conModels = new List<IConditionalModel>();
            conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "1" });//id=1
            conModels.Add(new ConditionalModel() { FieldName = "Student.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();

Multi-table query aliases (st,sc) are difficult to control

Converting multi-table queries into single-table queries to facilitate dynamic queries

var list = db.Queryable<Student, School>((st, sc) =>new JoinQueryInfos(
    JoinType.Left,st.SchoolId==sc.Id
))
.Select<ViewModelStudent>().MergeTable().Where(it=>it.Id=1).ToList();

SELECT * FROM  (
                     SELECT st.[ID] AS [Id],
                            st.[SchoolId] AS [SchoolId],
                            st.[Name] AS [Name],
                            st.[CreateTime] AS [CreateTime] 
                            FROM [STudent] stLeft JOIN [School] sc ON ( [st].[SchoolId] = [sc].[Id] ) 
                ) MergeTable
 Where Id=1-- No need to add st

1.4 Conditional builder

// use whereif
var query = db.Queryable<Student>()
                .WhereIF(!string.IsNullOrEmpty(name), it => it.Name.Contains(name))
                .WhereIF(id>0, it => it.Id==id).ToList();
//clone new Queryable
var query = db.Queryable<Student>().Where(it=>it.Id==1);
var list = query.Clone().Where(it => it.Name == "jack").ToList();//id=1 and name = jack
var list2 = query.Clone().Where(it => it.Name == "tom").ToList();//id=1 and name = tom

2. Join table query

2.1 Examples of three-table queries

 public class ViewModelStudent : Student
 {
        public string SchoolName{get;set;}// Class name +Property name
 }
 var list = db.Queryable<Student, School, DataTestInfo>((st, sc, di) => new JoinQueryInfos(
              JoinType.Left,st.SchoolId==sc.Id,
              JoinType.Left,st.Name==di.String
            ))
           .Select<ViewModelStudent>().ToList();
SELECT 
           sc.[Name] AS [SchoolName],--automatic recognition SchoolName
           st.[ID] AS [Id],st.[SchoolId] AS [SchoolId],
           st.[Name] AS [Name],st.[CreateTime] AS [CreateTime]
           FROM [STudent] st
           Left JOIN [School] sc ON ( [st].[SchoolId] = [sc].[Id] )  
           Left JOIN [DataTestInfo] di ON ( [st].[Name] = [di].[String] )

2.2 Join Queryable

var query1 = db.Queryable<Student, School>((st, sc) => new JoinQueryInfos(
    JoinType.Left,st.SchoolId==sc.Id
))
.Where(st => st.Name == "jack");

var query2 = db.Queryable<DataTestInfo>();
db.Queryable(query1, query2, (p1, p2) => p1.Id == p2.Int1).Select<ViewModelStudent>().ToList();
SELECT * FROM 
                     (SELECT [st].[ID],[st].[SchoolId],[st].[Name],[st].[CreateTime] FROM [STudent] st Left JOIN [School] 
                      sc ON ( [st].[SchoolId] = [sc].[Id] )   WHERE ( [st].[Name] = @Name0Join0 )) 
                      p1 
                      Inner JOIN
                     (SELECT [Int1],[Int2],[String],[Decimal1],[Decimal2],[Datetime1],[Datetime2],[Image1],[Image2], 
                      [Guid1],[Guid2],[Money1],[Money2],[Varbinary1],[Varbinary2],[Float1],[Float2] FROM [DataTestInfo] ) 
                      p2   ON ( [p1].[ID] = [p2].[Int1] )

2.3 Simple join

var list = db.Queryable<Order, OrderItem, Custom>((o, i, c) => o.Id == i.OrderId&&c.Id == o.CustomId)
                .Select<ViewOrder>()
                .ToList();
SELECT  c.[Name] AS [CustomName],
        o.[Id] AS [Id],o.[Name] AS [Name],
        o.[Price] AS [Price],
        o.[CreateTime] AS [CreateTime],
        o.[CustomId] AS [CustomId]
         FROM [Order] o  ,[OrderDetail]  i ,[Custom]  c  
         WHERE (( [o].[Id] = [i].[OrderId] ) AND ( [c].[Id] = [o].[CustomId] ))

2.4 Subquery

var list = db.Queryable<Order>().Take(10).Select(it => new
{
   customName=SqlFunc.Subqueryable<Custom>().Where("it.CustomId=id").Select(s=>s.Name),
   customName2 = SqlFunc.Subqueryable<Custom>().Where("it.CustomId = id").Where(s => true).Select(s => s.Name)
}).ToList();
SELECT * FROM (
                 SELECT  (SELECT TOP 1 [Name] FROM [Custom] WHERE it.CustomId=id) AS [customName] , 
                 (SELECT TOP 1 [Name] FROM [Custom] WHERE ( 1 = 1 )  AND it.CustomId = id) AS [customName2] ,
                 ROW_NUMBER() OVER( ORDER BY GetDate() ) AS RowIndex  FROM [Order] it ) 
T WHERE RowIndex BETWEEN 1 AND 10
var list2 = db.Queryable<Order>().Where(it => SqlFunc.Subqueryable<OrderItem>().Where(i => i.OrderId == it.Id).Any()).ToList();
SELECT [Id],[Name],[Price],[CreateTime],[CustomId] FROM [Order] it  WHERE (EXISTS ( SELECT * FROM [OrderDetail] WHERE ( [OrderId] = [it].[Id] ) ))
--equals WHERE it.id in(SELECT OrderId from orderdetail )

3.Return Type

List<Student> list = db.Queryable<Student>().ToList();

Student item = db.Queryable<Student>().First(it=>it.Id==1);

DataTable dataTable = db.Queryable<Student>().Select(it => it.Id).ToDataTable();

var json = db.Queryable<Student>().ToJson();

List<int> listInt=db.Queryable<Student>().Select(it => it.Id).ToList();

var dynamic = db.Queryable<Student>().Select<dynamic>().ToList();

var viewModel = db.Queryable<Student, School, DataTestInfo>((st, sc, di) => new JoinQueryInfos(
        JoinType.Left, st.SchoolId == sc.Id,
        JoinType.Left, st.Name == di.String
    ))
    .Select<ViewModelStudent>().ToList();

var newDynamic = db.Queryable<Student, School, DataTestInfo>((st, sc, di) => new JoinQueryInfos(
    JoinType.Left, st.SchoolId == sc.Id,
    JoinType.Left, st.Name == di.String
)) .Select((st,sc,di)=>new { name=st.Name,scid=sc.Id }).ToList();

var newClass= db.Queryable<Student, School, DataTestInfo>((st, sc, di) => new JoinQueryInfos(
    JoinType.Left, st.SchoolId == sc.Id,
    JoinType.Left, st.Name == di.String
)) .Select((st,sc,di)=>new ClassName{ name=st.Name,scid=sc.Id }).ToList();

var oneClass = db.Queryable<Order, OrderItem, Custom>((o, i, c) => new JoinQueryInfos(
    JoinType.Left, o.Id == i.OrderId,
    JoinType.Left, o.CustomId == c.Id
))
.Select((o, i, c) => c).ToList();

var twoClass = db.Queryable<Order, OrderItem, Custom>((o, i, c) => new JoinQueryInfos(
JoinType.Left, o.Id == i.OrderId,
JoinType.Left, o.CustomId == c.Id
))
.Select((o, i, c) => new { o,i}).ToList()

List<Dictionary<string, object>> ListDic = db.Queryable<Student, School, DataTestInfo>((st, sc, di) => new JoinQueryInfos(
JoinType.Left, st.SchoolId == sc.Id,
JoinType.Left, st.Name == di.String
)).Select<ExpandoObject>().ToList().Select(it => it.ToDictionary(x => x.Key, x => x.Value)).ToList(); ;

4.Useful queries

4.1 Group

var list = db.Queryable<Student>()
             .GroupBy(it => new { it.Id, it.Name }).Having(it => SqlFunc.AggregateAvg(it.Id) > 0)
             .Select(it => new { idAvg = SqlFunc.AggregateAvg(it.Id), name = it.Name }).ToList();
    SELECT AVG([Id]) AS[idAvg], [Name] AS[name]  FROM[Student] GROUP BY[Name],[Id] HAVING(AVG([Id]) > 0 )

4.2 Distinct

var list = db.Queryable<Student>().Distinct().Select(it => new { it.Name }).ToList();
SELECT  DISTINCT  [Name] AS [Name]  FROM [STudent]

4.3 Page

 int pageIndex = 1; 
 int pageSize = 20;
 int totalCount=0;
 var page = db.Queryable<Student>().ToPageList(pageIndex, pageSize, ref totalCount);
SELECT * FROM (SELECT [ID],[SchoolId],[Name],[CreateTime],ROW_NUMBER() OVER( ORDER BY GetDate() ) AS RowIndex  FROM [STudent] ) T WHERE RowIndex BETWEEN 1 AND 20

4.4 Union all

 var q1 = db.Queryable<Student>().Select(it=>new Model{ name=it.Name });
 var q2 = db.Queryable<School>().Select(it => new Model { name = it.Name });
 var list = db.UnionAll(q1, q2).ToList();
SELECT * FROM  (SELECT  [Name] AS [name]  FROM [STudent]  UNION ALL
SELECT  [Name] AS [name]  FROM [School] ) unionTable

4.5 SqlFunc Methods

//SqlFunc.MethodName
var getByFuns = db.Queryable<Student>().Where(it => SqlFunc.Between(it.Id,1,2)).ToList();

4.6 Order By

var list = db.Queryable<Student, School>((st, sc) =>new  JoinQueryInfos(JoinType.Left,st.SchoolId==sc.Id))
.OrderBy(st=>st.Id)
.OrderBy((st,sc)=>sc.Id,OrderByType.Desc)
.Select<ViewModelStudent>().ToList();

4.7 No Entity

var list = db.Queryable<dynamic>().AS("order ").Where("id=id", new { id = 1 }).ToList();

var list2 = db.Queryable<dynamic>("o").AS("order").AddJoinInfo("OrderDetail", "i", "o.id=i.OrderId").Where("id=id", new { id = 1 }).Select("o.*").ToList();

4.8 Other

var getAll = db.Queryable<Student>().ToList();
var getAllNoLock = db.Queryable<Student>().With(SqlWith.NoLock).ToList();
var getByPrimaryKey = db.Queryable<Student>().InSingle(2);
var sum = db.Queryable<Student>().Sum(it=>it.Id);
var isAny = db.Queryable<Student>().Where(it=>it.Id==-1).Any();
var isAny2 = db.Queryable<Student>().Any(it => it.Id == -1);
var getListByRename = db.Queryable<School>().AS("Student").ToList();
var getByWhere = db.Queryable<Student>().Where(it => it.Id == 1 || it.Name == "a").ToList();
var list= db.Queryable<Student>().AS("student2019").ToList();//select * from student2019

5.Mapper

5.1 one to one

 public class OrderItem {
        [SugarColumn(IsPrimaryKey =true ,IsIdentity =true)]
        public int ItemId { get; set; }
        public string ItemCode { get; set; }
        public decimal Pirce { get; set; }
        public int OrderId { get; set; }
        [SugarColumn(IsIgnore =true)]
        public Order Order{ get; set; }
    }
    public class Order {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int Id { get; set; }
        public string Name { get; set; }
        [SugarColumn(IsIgnore = true)]
        public List<OrderItem> Items { get; set; }
    }
var list= db.Queryable<OrderItem>().Mapper(it => it.Order, it => it.OrderId).ToList();

5.2 one to many

  public class Order
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int Id { get; set; }
        public string Name { get; set; }
        [SugarColumn(IsIgnore = true)]
        public List<OrderItem> Items { get; set; }
    }
    public class OrderItem {
        [SugarColumn(IsPrimaryKey =true ,IsIdentity =true)]
        public int ItemId { get; set; }
        public string ItemCode { get; set; }
        public decimal Pirce { get; set; }
        public int OrderId { get; set; }
        [SugarColumn(IsIgnore =true)]
        public Order Order{ get; set; }
    }
  var list= db.Queryable<Order>().Mapper(it => it.Items, it => it.Items.First().OrderId).ToList();

5.3 many to many

   public class ABMapping
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int AId { get; set; }
        public int BId { get; set; }
        [SugarColumn(IsIgnore = true)]
        public A A { get; set; }
        [SugarColumn(IsIgnore = true)]
        public B B { get; set; }

    }
    public class A {
        [SugarColumn(IsPrimaryKey =true ,IsIdentity =true)]
        public int Id { get; set; }
        public string Name { get; set; }
    }
    public class B
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int Id { get; set; }
        public string Name { get; set; }
    }
  var list= db.Queryable<ABMapping>()
                .Mapper(it => it.A,it=>it.AId)
                .Mapper(it => it.B, it => it.BId).ToList();

5.4 Same property name mapping

    public class A {
        [SugarColumn(IsPrimaryKey =true ,IsIdentity =true)]
        public int Id { get; set; }
        public string Name { get; set; }
        public string OrgId { get; set; }
        [SugarColumn(IsIgnore =true)]
        public B B { get; set; }
    }
    public class B
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int Id { get; set; }
        public string Name { get; set; }
        public string OrgId { get; set; }
    }
   var list= db.Queryable<A>().Mapper(it => it.B,it=>it.OrgId).ToList();
5.5 If both entities have parentId, I don't want to associate with parentId.
public class Tree
{
        [SqlSugar.SugarColumn(IsPrimaryKey =true)]
        public int Id { get; set; }
        public string Name { get; set; }
        public int ParentId { get; set; }
        [SqlSugar.SugarColumn(IsIgnore = true)]
        public Tree Parent { get; set; }
        [SqlSugar.SugarColumn(IsIgnore = true)]
        public List<Tree> Child { get; set; }
}
var list=db.Queryable<Tree>()
                   //parent=(select * from parent where id=it.parentid)
                   .Mapper(it=>it.Parent,it=>it.ParentId, it=>it.Parent.Id)
                   //Child=(select * from parent where ParentId=it.id)
                   .Mapper(it => it.Child, it => it.Id, it => it.Parent.ParentId)
                   .ToList();
5.6 Manual mode
public class ViewModelStudent:Student
{
     public School SchoolInfo { get; set; }
}
var result=db.Queryable<Student>().Take(10).Select<ViewModelStudent>().Mapper((itemModel, cache) =>
            {
                var allSchool =cache.Get(list => {
                    //list=db.Queryable<Student>().Select<ViewModelStudent2>().Take(10).ToList();
                    var allSchoolIds = list.Select(it => it.SchoolId).ToList();
                    return db.Queryable<School>().Where(it => allSchoolIds.Contains(it.Id)).ToList();//Execute only once
                });
                itemModel.SchoolInfo = allSchool.FirstOrDefault(it => it.Id == itemModel.SchoolId);//Every time it's executed
            }).ToList();

if is new dynamic,Need to add '(dynamic)'

db.Queryable<Order, OrderItem, Custom>((o, i, c) => new JoinQueryInfos(
    JoinType.Left, o.Id == i.OrderId,
    JoinType.Left, o.CustomId == c.Id
))
.Select((o, i, c) =>
(dynamic) new { orderName = o.Name, cusName=c.Name }).Mapper(it=> { it.orderName = "_"+it.orderName; }).ToList();

6.Data Cache

Create a class "HttpRuntimeCache" that inherits ICacheService

  ICacheService myCache = new HttpRuntimeCache();
  SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
  {
                ConnectionString = Config.ConnectionString,
                DbType = DbType.SqlServer,
                IsAutoCloseConnection = true,
                ConfigureExternalServices = new ConfigureExternalServices()
                {
                    DataInfoCacheService = myCache //Setting external cache service
                }
   });
   db.Queryable<Student>().Where(it => it.Id > 0).WithCache().ToList();

Remove Cache

db.Queryable<Student>().WithCache(1000).ToList();//Expiration date
db.Deleteable<Student>().RemoveDataCache().Where(it => it.Id == 1).ExecuteCommand();//Manually delete
  //Realization reference
  public class HttpRuntimeCache : ICacheService
    {
        public void Add<V>(string key, V value)
        {
            HttpRuntimeCacheHelper<V>.GetInstance().Add(key, value);
        }

        public void Add<V>(string key, V value, int cacheDurationInSeconds)
        {
            HttpRuntimeCacheHelper<V>.GetInstance().Add(key, value, cacheDurationInSeconds);
        }

        public bool ContainsKey<V>(string key)
        {
            return HttpRuntimeCacheHelper<V>.GetInstance().ContainsKey(key);
        }

        public V Get<V>(string key)
        {
            return HttpRuntimeCacheHelper<V>.GetInstance().Get(key);
        }

        public IEnumerable<string> GetAllKey<V>()
        {
            return HttpRuntimeCacheHelper<V>.GetInstance().GetAllKey();
        }

        public V GetOrCreate<V>(string cacheKey, Func<V> create, int cacheDurationInSeconds = int.MaxValue)
        {
            var cacheManager = HttpRuntimeCacheHelper<V>.GetInstance();
            if (cacheManager.ContainsKey(cacheKey))
            {
                return cacheManager[cacheKey];
            }
            else
            {
                var result = create();
                cacheManager.Add(cacheKey, result, cacheDurationInSeconds);
                return result;
            }
        }

        public void Remove<V>(string key)
        {
            HttpRuntimeCacheHelper<V>.GetInstance().Remove(key);
        }
    }

6.Async

var task1=db.Queryable<Order>().FirstAsync();
var task2 = db.Queryable<Order>().Where(it=>it.Id==1).ToListAsync();

//async no support ref  ,use RefAsync
RefAsync<int> total = 0;
Db.Queryable<Order>().ToPageListAsync(1, 2, total);

7.Json To Object

Setting IsJson = true serializes JSON strings to objects

public class UnitJsonTest
{
   [SqlSugar.SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
   public int Id { get; set; }
   [SqlSugar.SugarColumn(Length=2000, IsJson = true)]
   public Order Order { get; set; }
   public string Name{get;set;}
}
Db.Insertable(new UnitJsonTest() { Name="json1",Order = new Order { Id = 1, Name = "order1" } }).ExecuteCommand();
var list = Db.Queryable<UnitJsonTest>().ToList();

.

Add a custom sidebar
Clone this wiki locally