ShadowSql.net之静态查询示例

大规绣 發表於 2025-4-3 12:36:00

ShadowSql拼接sql除了性能突出外还是讲逻辑的
其一为了更好拼接正确的sql,提供代码可读性
其二是为了码农同学爽,拼写起来更简便
以下我就举几个例子给大家观摩一下
 
一、单表查询示例
为了静态检测，我们首先自定义一个表UserTable用来查询
<div class="cnblogs_code">
<pre> class UserTable : Table
{
 public UserTable()
 : base("Users")
 {
 Id = DefineColumn(nameof(Id));
 Status = DefineColumn(nameof(Status));
 }
 #region Columns
 public IColumn Id { get; private set; }
 public IColumn Status { get; private set; }
 #endregion
}</pre>
</div>
1、用SqlQuery查询
<div class="cnblogs_code">
<pre> var users = new UserTable();
 var query = users.ToSqlQuery()
 .Where(users.Id.LessValue(100));</pre>
</div>
我们还可以用这样查询
<div class="cnblogs_code">
<pre> var query = new UserTable()
 .ToSqlQuery()
 .Where(user => user.Id.Less("LastId"));</pre>
</div>
sql: SELECT * FROM   WHERE <@LastId
2、用Query查询
<div class="cnblogs_code">
<pre> var users = new UserTable();
 var query = users.ToQuery()
 .And(users.Id.LessValue(100));</pre>
</div>
换种写法
<div class="cnblogs_code">
<pre> var query = new UserTable()
 .ToQuery()
 .And(user => user.Id.Less("LastId"));</pre>
</div>
 sql: SELECT * FROM   WHERE <@LastId
3、Query可以用&和|重载符查询
<div class="cnblogs_code">
<pre> var users = new UserTable();
 var query = users.ToQuery()
 .And(users.Id.LessValue(100) & users.Status.EqualValue(true));</pre>
</div>
sql: SELECT * FROM  WHERE <100 AND =1
<div class="cnblogs_code">
<pre> var users = new UserTable();
 var query = users.ToQuery()
 .And(users.Id.LessValue(100) | users.Status.EqualValue(true));</pre>
</div>
sql: SELECT * FROM  WHERE <100 OR =1
 
二、联表查询示例
为了静态检测，我们自定义两个别名表
<div class="cnblogs_code">
<pre> class CommentTable : TableAlias<Table>
{
 public CommentTable(string tableAlias)
 : this(new Table("Comments"), tableAlias)
 {
 }
 private CommentTable(Table table, string tableAlias)
 : base(table, tableAlias)
 {
 Id = AddColumn(Column.Use(nameof(Id)));
 PostId = AddColumn(Column.Use(nameof(PostId)));
 Content = AddColumn(Column.Use(nameof(Content)));
 Pick = AddColumn(Column.Use(nameof(Pick)));
 }
 public readonly IPrefixColumn Id;
 public readonly IPrefixColumn PostId;
 public readonly IPrefixColumn Content;
 public readonly IPrefixColumn Pick;
}
class PostTable : TableAlias<Table>
{
 public PostTable(string tableAlias)
 : this(new Table("Posts"), tableAlias)
 {
 }
 private PostTable(Table table, string tableAlias)
 : base(table, tableAlias)
 {
 Id = AddColumn(Column.Use(nameof(Id)));
 Title = AddColumn(Column.Use(nameof(Title)));
 Author = AddColumn(Column.Use(nameof(Author)));
 }
 //Id, Title, Author
 public readonly IPrefixColumn Id;
 public readonly IPrefixColumn Title;
 public readonly IPrefixColumn Author;
}</pre>
</div>
准备好了就方便联表查询了
<div class="cnblogs_code">
<pre> CommentTable c = new("c");
 PostTable p = new("p");
 var joinOn = c.Join(p)
 .And(c.PostId.Equal(p.Id));
 var query = joinOn.Root
 .And(c.Pick.EqualValue(true))
 .And(p.Author.EqualValue("jxj"));</pre>
</div>
<div class="cnblogs_code">
<pre> CommentTable c = new("c");
 PostTable p = new("p");
 var joinOn = c.SqlJoin(p)
 .On(c.PostId.Equal(p.Id));
 var query = joinOn.Root
 .Where(c.Pick.EqualValue(true))
 .Where(p.Author.EqualValue("jxj"));</pre>
</div>
sql: SELECT * FROM AS c INNER JOIN AS p ON c.=p. where c.=1 AND p.='jxj'
 
Query模式和SqlQuery模式各有优点,您喜欢哪种？
源码托管地址: https://github.com/donetsoftwork/Shadow。 如果大家喜欢请动动您发财的小手手帮忙点一下Star。
  
来源：https://www.cnblogs.com/xiangji/p/18807534

頁: [1]

琼殿技术论坛's Archiver

ShadowSql.net之静态查询示例