目录
零、官网链接
一、字段映射表
二、查询
1.freesql独特封装:between关键字查日期
var list = list.Where(a => a.Time.Between(time1, time2));
2.分页(每页 20 条数据,查询第 1 页)
var list = fsql.Select<Topic>()
.Where(a => a.Id > 10)
.Count(out var total) //总记录数量
.Page(1, 20)
.ToList();
3.Withsql(子查询,不建议)
class Topic
{
[Column(IsIdentity = true)]
public int Id { get; set; }
public string Title { get; set; }
public int Clicks { get; set; }
public DateTime CreateTime { get; set; }
public int CategoryId { get; set; }
}
fsql.Select<Topic>()
.WithSql("select * from Topic where clicks > @val", new { val = 10 })
.Page(1, 10)
.ToList()
//SELECT a.`Id`, a.`Clicks`, a.`CategoryId`, a.`Title`, a.`CreateTime`
//FROM (select * from Topic where clicks > @val) a
3.简单查询、映射查询
【技巧】打印sql、纠正映射
List<Student2022> list1 = freesql.Select<Student2022>().ToList(); //1.简单查询
//var sql1 = freesql.Select<Student2022>().ToSql();//【技巧】获取查询SQL
List<StuAndParent> list5 = freesql.Select<Student2022>().ToList<StuAndParent>();//2.查询后自动映射
//freesql.Select<Student2022>().ToList(x => new StuAndParent { xxx = x.id }) //【技巧】纠正映射
4.参数查询、自定义查询
//等于=、批量in、模糊like查询
freesql.Ado.QuerySingle<T>("select * from t1 where id = @id", new { id = 1 });//同时支持字典查询
freesql.Ado.Query<T>("select * from t1 where name like @name", new { name = "%" + searchText + "%" });//同时支持字典查询
var ids = new int[] { 1, 2, 3 };
List<T> list = freesql.Ado.Query<T>("select * from t1 where id in @ids", new { ids = ids });//仅支持 Array 和 IList 类型
List<StuAndParent> list2 = freesql.Ado.Query<StuAndParent>("SELECT * FROM Student_2022 A LEFT JOIN Parent B ON A.id=B.pid");//3.自定义SQL查询
5.左外连接(框架+导航属性)
List<StuAndParent> list2 = freesql.Ado.Query<StuAndParent>("SELECT * FROM Student_2022 A LEFT JOIN Parent B ON A.id=B.pid");//3.自定义SQL查询
List<StuAndParent> list3 = freesql.Select<Student2022, Parent>()//4.左外连接(框架,列出具体字段)
.LeftJoin(w => w.t1.id == w.t2.pid)
.ToList(w => new StuAndParent
{
id= w.t1.id,
name = w.t1.name,
pid = w.t2.pid,
pname = w.t2.pname
});
List<StuAndParent> list3_1 = freesql.Select<Student2022, Parent>()//5.左外连接(框架,映射结果)
.LeftJoin(w => w.t1.id == w.t2.pid)
.ToList(x=>new StuAndParent());
List<StuAndParent> list4 = freesql.Select<Student2022>() //6.左外连接(导航属性)
.LeftJoin<Parent>((student, parent) => student.id == parent.pid)//直接设置关联条件
.ToList(x=>new StuAndParent());//转化为StuAndParent实体
[Table(Name = "Student_2022")]
public class Student2022
{
[Column(IsPrimary = true)]
public int id { get; set; }
public string name { get; set; }
public int? ParentId { get; set; } // 【导航关联字段】(数据库不需要设置外键,但数据库必须要有这个字段)
[Navigate(nameof(ParentId))] // 设置导航属性,指定【导航关联字段】
public Parent Parent { get; set; } // 关联的 Parent 实体
}
public class Parent
{
[Column(IsPrimary = true)]
public int pid { get; set; }
public string pname { get; set; }
[Navigate(nameof(Student2022.ParentId))] // 设置导航属性,指定【导航关联字段】
public Student2022 Student { get; set; } // 关联的 Student2022 实体
}
public class StuAndParent
{
public int id { get; set; }
public string name { get; set; }
public int pid { get; set; }
public string pname { get; set; }
}
6.简单分表查询
//7.简单分表查询
var list6 = freesql.Select<Teacher>().ToList();
//假如是按月分表:[Table(Name = "log_{yyyyMM}", AsTable = "createtime=2022-1-1(1 month)")]注意:①需包含log_202201这张表 ②递增规律是一个月一次,确保他们存在。 ③确保有字段createtime。
[Table(Name = "Teacher_{yyyy}", AsTable = "time=2023-1-1(1 year)")]
public class Teacher
{
[Column(IsPrimary = true)]
public int id { get; set; }
public DateTime time { get; set; }
}
三、增删改
1.SQL增删改(ADO.NET)
//8.sql增删改
bool b = freesql.Ado.ExecuteNonQuery(@"DELETE FROM Student_2022 WHERE id = 6")>0;
2.框架增删改(普通)
//9.框架增删改
freesql.Insert(entity).ExecuteAffrows();
freesql.Update<T>(entity);
freesql.Update<T>()
.Set(a => a.Title, "新标题")
.Set(a => a.Time, DateTime.Now)
.Where(a => a.Id == 1)//过滤条件
.ExecuteAffrows();
freesql.Delete<T>(entity).ExecuteAffrows();
freesql.Delete<T>()
.Where(s => s.Id == 1)
.ExecuteAffrows();
3.框架保存逻辑
【判断依据】主键存在=>改,主键不存在=>增
//10.保存实体(增加或修改)
var entity = new Student2022 { name = "晓晓", id = 6 };
bool b2 = freesql.InsertOrUpdate<Student2022>()
.SetSource(entity)
.ExecuteAffrows()>0;
四、demo
1.Model
using FreeSql.DataAnnotations;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace FreesqlDemo
{
public class Model
{
}
[Table(Name = "Student_2022")]
public class Student2022
{
[Column(IsPrimary = true)]
public int id { get; set; }
public string name { get; set; }
public int? ParentId { get; set; } // 【导航关联字段】(数据库不需要设置外键,但数据库必须要有这个字段)
[Navigate(nameof(ParentId))] // 设置导航属性,指定【导航关联字段】
public Parent Parent { get; set; } // 关联的 Parent 实体
}
public class Parent
{
[Column(IsPrimary = true)]
public int pid { get; set; }
public string pname { get; set; }
[Navigate(nameof(Student2022.ParentId))] // 设置导航属性,指定【导航关联字段】
public Student2022 Student { get; set; } // 关联的 Student2022 实体
}
public class StuAndParent
{
public int id { get; set; }
public string name { get; set; }
public int pid { get; set; }
public string pname { get; set; }
}
//假如是按月分表:[Table(Name = "log_{yyyyMM}", AsTable = "createtime=2022-1-1(1 month)")]注意:①需包含log_202201这张表 ②递增规律是一个月一次,确保他们存在。 ③确保有字段createtime。
[Table(Name = "Teacher_{yyyy}", AsTable = "time=2023-1-1(1 year)")]
public class Teacher
{
[Column(IsPrimary = true)]
public int id { get; set; }
public DateTime time { get; set; }
}
}
2.Main
using FreeSql;
using System.Diagnostics;
using System.Net.WebSockets;
using System.Reflection.Metadata;
using static FreeSql.Internal.GlobalFilter;
namespace FreesqlDemo
{
public class Program
{
// 修正后的静态字段声明
private static IFreeSql freesql = new FreeSqlBuilder()
.UseMonitorCommand(cmd => Trace.WriteLine($"Sql:{cmd.CommandText}"))
.UseConnectionString(DataType.SqlServer, @"server = DESKTOP-FTH2P3S; Database = Test; Trusted_Connection = SSPI;")
.Build();
static void Main(string[] args)
{
List<Student2022> list1 = freesql.Select<Student2022>().ToList(); //1.简单查询
var sql1 = freesql.Select<Student2022>().ToSql();//【技巧】获取查询SQL
List<StuAndParent> list5 = freesql.Select<Student2022>().ToList<StuAndParent>();//2.查询后自动映射
//freesql.Select<Student2022>().ToList(a => new StuAndParent { xxx = a.ext }) //【技巧】纠正映射
//等于=、批量in、模糊like查询
//freesql.Ado.QuerySingle<T>("select * from t1 where id = @id", new { id = 1 });//同时支持字典查询
//freesql.Ado.Query<T>("select * from t1 where name like @name", new { name = "%" + searchText + "%" });//同时支持字典查询
//var ids = new int[] { 1, 2, 3 };
//List<T> list = freesql.Ado.Query<T>("select * from t1 where id in @ids", new { ids = ids });//仅支持 Array 和 IList 类型
List<StuAndParent> list2 = freesql.Ado.Query<StuAndParent>("SELECT * FROM Student_2022 A LEFT JOIN Parent B ON A.id=B.pid");//3.自定义SQL查询
List<StuAndParent> list3 = freesql.Select<Student2022, Parent>()//4.左外连接(框架,列出具体字段)
.LeftJoin(w => w.t1.id == w.t2.pid)
.ToList(w => new StuAndParent
{
id= w.t1.id,
name = w.t1.name,
pid = w.t2.pid,
pname = w.t2.pname
});
List<StuAndParent> list3_1 = freesql.Select<Student2022, Parent>()//5.左外连接(框架)
.LeftJoin(w => w.t1.id == w.t2.pid)
.ToList(x=>new StuAndParent());
List<StuAndParent> list4 = freesql.Select<Student2022>() //6.左外连接(导航属性)
.LeftJoin<Parent>((student, parent) => student.id == parent.pid)//直接设置关联条件
.ToList(x=>new StuAndParent());//转化为StuAndParent实体
//7.简单分表查询
var list6 = freesql.Select<Teacher>().ToList();
//8.sql增删改
bool b = freesql.Ado.ExecuteNonQuery(@"DELETE FROM Student_2022 WHERE id = 6")>0;
//9.框架增删改
//freesql.Insert(entity).ExecuteAffrows();
//freesql.Update<T>(entity);
//freesql.Update<T>()
// .Set(a => a.Title, "新标题")
// .Set(a => a.Time, DateTime.Now)
// .Where(a => a.Id == 1)//过滤条件
// .ExecuteAffrows();
//freesql.Delete<T>(entity).ExecuteAffrows();
//freesql.Delete<T>()
// .Where(s => s.Id == 1)
// .ExecuteAffrows();
//10.保存实体(增加或修改)
var entity = new Student2022 { name = "晓晓", id = 6 };
bool b2 = freesql.InsertOrUpdate<Student2022>()
.SetSource(entity)
.ExecuteAffrows()>0;
}
}
}