ShadowSql.net之静态查询示例

ShadowSql拼接sql除了性能突出外还是讲逻辑的

其一为了更好拼接正确的sql,提供代码可读性

其二是为了码农同学爽,拼写起来更简便

以下我就举几个例子给大家观摩一下

 

一、单表查询示例

为了静态检测，我们首先自定义一个表UserTable用来查询

    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
    }

1、用SqlQuery查询

        var users = new UserTable();
        var query = users.ToSqlQuery()
            .Where(users.Id.LessValue(100));

我们还可以用这样查询

        var query = new UserTable()
            .ToSqlQuery()
            .Where(user => user.Id.Less("LastId"));

sql: SELECT * FROM [Users]  WHERE [Id]<@LastId

2、用Query查询

        var users = new UserTable();
        var query = users.ToQuery()
            .And(users.Id.LessValue(100));

换种写法

        var query = new UserTable()
            .ToQuery()
            .And(user => user.Id.Less("LastId"));

 sql: SELECT * FROM [Users]  WHERE [Id]<@LastId

3、Query可以用&和|重载符查询

        var users = new UserTable();
        var query = users.ToQuery()
            .And(users.Id.LessValue(100) & users.Status.EqualValue(true));

sql: SELECT * FROM [Users] WHERE [Id]<100 AND [Status]=1

        var users = new UserTable();
        var query = users.ToQuery()
            .And(users.Id.LessValue(100) | users.Status.EqualValue(true));

sql: SELECT * FROM [Users] WHERE [Id]<100 OR [Status]=1

 

二、联表查询示例

为了静态检测，我们自定义两个别名表

    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;
    }

准备好了就方便联表查询了

        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"));

        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"));

sql: SELECT * FROM [Comments] AS c INNER JOIN [Posts] AS p ON c.[PostId]=p.[Id] where c.[Pick]=1 AND p.[Author]='jxj'

 

Query模式和SqlQuery模式各有优点,您喜欢哪种？

源码托管地址: https://github.com/donetsoftwork/Shadow。
如果大家喜欢请动动您发财的小手手帮忙点一下Star。