ShadowSql.net之极简模式查询示例

ShadowSql拼接sql可以很简单,非必须的事情都可以不做,非必须的对象也都不构造。

可以不用提前配置,开箱即用。

这里我们示例一下极简模式。

 

一、极简单表查询

1. SqlQuery极简查询

        var users = EmptyTable.Use("Users");
        var query = users.ToSqlQuery()
            .Where(users.Field("Id").LessValue(100));

我们还可以用这样查询

        var query = EmptyTable.Use("Users")
            .ToSqlQuery()
            .Where(users => users.Field("Id").LessValue(100));

sql: SELECT * FROM [Users]  WHERE [Id]<100

这里用到Filed方法获取虚拟字段,这样被查询的表就不需要定义列了

通过EmptyTable.Use静态方法获取的表是EmptyTable的实例,只有表名，没有字段成员,节省内存。

EmptyTable.Use会缓存相同表名的实例,避免对象重复创建和回收的消耗

 

2、Query同样支持极简查询

        var users = EmptyTable.Use("Users");
        var query = users.ToQuery()
            .And(users.Field("Id").LessValue(100));

        var query = EmptyTable.Use("Users")
            .ToQuery()
            .And(users => users.Field("Id").LessValue(100));

sql: SELECT * FROM [Users]  WHERE [Id]<100

 

二、极简联表查询

1、SqlQuery极简联表

        var joinOn = EmptyTable.Use("Employees").SqlJoin(EmptyTable.Use("Departments"))
            .On(static (t1, t2) => t1.Field("DepartmentId").Equal(t2.Field("Id")))
            .WhereLeft(t1 => t1.Field("Age").GreaterValue(30))
            .WhereRight(t2 => t2.Field("Manager").EqualValue("CEO"));

        var joinOn = EmptyTable.Use("Employees").SqlJoin(EmptyTable.Use("Departments"));
        var (t1, t2) = (joinOn.Left, joinOn.Source);
        joinOn.On(t1.Field("DepartmentId").Equal(t2.Field("Id")));
        var joinTable = joinOn.Root
            .Where(t1.Field("Age").GreaterValue(30))
            .Where(t2.Field("Manager").EqualValue("CEO"));

 

sql: SELECT * FROM [Employees] AS e INNER JOIN [Departments] AS d ON e.[DepartmentId]=d.[Id] WHERE e.[Age]>30 AND d.[Manager]='CEO'

        var e = EmptyTable.Use("Employees").As("e");
        var d = EmptyTable.Use("Departments").As("d");
        var joinOn = e.SqlJoin(d)
            .On(e.Field("DepartmentId").Equal(d.Field("Id")));
        var joinTable = joinOn.Root
            .Where(e.Field("Age").GreaterValue(30))
            .Where(d.Field("Manager").EqualValue("CEO"));

sql: SELECT * FROM [Employees] AS e INNER JOIN [Departments] AS d ON e.[DepartmentId]=d.[Id] WHERE e.[Age]>30 AND d.[Manager]='CEO'

可以设置别名

        var e = EmptyTable.Use("Employees").As("e");
        var d = EmptyTable.Use("Departments").As("d");
        var joinOn = e.SqlJoin(d);
        joinOn.On(e.Field("DepartmentId").Equal(d.Field("Id")));
        var joinTable = joinOn.Root
            .Where(e.Field("Age").GreaterValue(30))
            .Where(d.Field("Manager").EqualValue("CEO"));

 

2、Query极简联表 

        var joinOn = EmptyTable.Use("Employees").Join(EmptyTable.Use("Departments"));
        var (t1, t2) = (joinOn.Left, joinOn.Source);
        joinOn.And(t1.Field("DepartmentId").Equal(t2.Field("Id")));
        var joinTable = joinOn.Root
            .And(t1.Field("Age").GreaterValue(30))
            .And(t2.Field("Manager").EqualValue("CEO"));

sql: SELECT * FROM [Employees] AS t1 INNER JOIN [Departments] AS t2 ON t1.[DepartmentId]=t2.[Id] WHERE t1.[Age]>30 AND t2.[Manager]='CEO'

Query也可以设置别名

        var e = EmptyTable.Use("Employees").As("e");
        var d = EmptyTable.Use("Departments").As("d");
        var joinOn = e.Join(d)
            .And(e.Field("DepartmentId").Equal(d.Field("Id")));
        var joinTable = joinOn.Root
            .And(e.Field("Age").GreaterValue(30))
            .And(d.Field("Manager").EqualValue("CEO"));

 sql: SELECT * FROM [Employees] AS e INNER JOIN [Departments] AS d ON e.[DepartmentId]=d.[Id] WHERE e.[Age]>30 AND d.[Manager]='CEO'

通过Field查询,既不需要自定义表类型,也不需要给表定义列,就可以畅通无阻的进行sql查询，这也一种不错的选项