SqlSugar跨库查询/多库查询
一、跨库方式1：跨库导航 （5.1.3.24）
优点1：支持跨服务器，支持跨数据库品种, 支持任何类型数据库
优点2: 超级强大的性能，能达到本库联表性能
缺点：不支持子表过滤主表 (方案有ToList后在内存过滤， 如果分页可以查前1000条主表在内存分页 前端只显示前10页)
[Tenant("db2")] //实体标为db2public class OrderItem { [SqlSugar.SugarColumn(IsPrimaryKey = true, IsIdentity = true)] public int ItemId { get; set; } public int OrderId { get; set; } public decimal? Price { get; set; } [SqlSugar.SugarColumn(IsNullable = true)] public DateTime? CreateTime { get; set; } [Navigate(NavigateType.OneToOne,nameof(OrderId))] //设置关系 对应Order表主键 public Order Order { get; set; } }[Tenant("db1")] //实体标为db1public class Order { [SugarColumn(IsPrimaryKey = true, IsIdentity = true)] public int Id { get; set; } public string Name { get; set; } public decimal Price { get; set; } [SugarColumn(IsNullable = true)] public DateTime CreateTime { get; set; } [SugarColumn(IsNullable = true)] public int CustomId { get; set; } [Navigate(NavigateType.OneToMany, nameof(OrderItem.OrderId))]// public List<OrderItem> Items { get; set; } } //通过ConfigId进行区分是哪个库var db = new SqlSugarClient(new List<ConnectionConfig>(){ new ConnectionConfig(){ConfigId="db1",DbType=DbType.Sqlite, ConnectionString="DataSource=/Db_OrderDb.sqlite",IsAutoCloseConnection=true}, new ConnectionConfig(){ConfigId="db2",DbType=DbType.Sqlite, ConnectionString="DataSource=/Db_OrderItemDb.sqlite",IsAutoCloseConnection=true }});//注意：如果是接口需要//db.AsTenant().QueryableWithAttr<OrderItem>() //通过实体类特性Tenant自动映射不同数据库进行查询var list=db.QueryableWithAttr<OrderItem>().Includes(z => z.Order).ToList(); //1行代码就搞定了2个库联表查询//不通过特性实现跨库导航var list =db.GetConnection("db1").Queryable<OrderItem>()//Orderitem是db1 .CrossQuery(typeof(Order), "db2")//Order是db2 .Includes(z => z.Order) .ToList(); |
详细教程 (5.1.3.25)：
https://www.cnblogs.com/sunkaixuan/p/16767798.html
二、手动跨库查询
优点：联表语法100%可以用
缺点：只支持个别数据库，并且跨服务器比较麻烦需要配置dblink
大多数的数据库支持下面的写法，我们可以通过As指定是哪个库, 查询的时候用别名
注意：不同数据库写法有区别 下面是SqlServer例子 ： 库名.dbo.表名
//单表跨库var list=db.Queryable<Order>().AS("xx.dbo.Order2019").ToList();//SqlServer (跨库用dblink)var list1 = db.Queryable<Order>() //主表用当前db就行了 .LeftJoin<OrderItem>((o,i)=> o.Id == i.OrderId,"yy.dbo.OrderItem") .LeftJoin<Custom>((o, i, c) => c.Id == o.CustomId,"zz.dbo.Custom") .Where((o,i,c)=> o.TypeId==1) .Select((o,i,c)=>new classA() { oid=o.Id , iname=i.Name }) .ToList(); //MySql (只能同服务器) var list1 = db.Queryable<Order>(). //如果跨服务器需要配置dblink，语法上每种数据库有点小差异 .LeftJoin<OrderItem>((o,i)=> o.Id == i.OrderId,"yy.OrderItem") .LeftJoin<Custom>((o, i, c) => c.Id == o.CustomId,"zz.Custom") .Where((o,i,c)=> o.TypeId==1) .Select((o,i,c)=>new classA() { oid=o.Id , iname=i.Name }) .ToList(); //Oracle var list1 = db.Queryable<Order>() .LeftJoin<OrderItem>((o,i)=> o.Id == i.OrderId,"\"ORDERITEM\"@dblink_name") .LeftJoin<Custom>((o, i, c) => c.Id == o.CustomId,"\"CUSTEM\"@dblink_name") .Where((o,i,c)=> o.TypeId==1) .Select((o,i,c)=>new classA() { oid=o.Id , iname=i.Name }) .ToList(); //其他库同理//老版本：多表跨库var list1 = db.Queryable<Order>().AS("xx.dbo.order") // AS("") .LeftJoin<OrderItem>((o,i)=> o.Id == i.OrderId).AS<OrderItem>("yy.dbo.OrderItem") //AS<T> .LeftJoin<Custom>((o, i, c) => c.Id == o.CustomId) .AS<Custom>("zz.dbo.Custom")//AS<T> .Select((o,i,c)=>new classA() { oid=o.Id , iname=i.Name }) .ToList(); //获取表名可以减少字符串操作 var tableName=db.EntityMaintenance.GetTableName<T>() //如果有2个AS<T>是相同我们可以这么写 .LeftJoin(db.Queryable<T>().AS("xx.dbo.zzzz"),(o,i)=>o.id==i.id) |
请升级到5.1.4.86-preview01+
三、同服务器：自动查询跨库查询
3.1 Mysql和SqlServer自动
注意：
1.只支持MySql和SqlServer同服务器并且是同一种类型的库（你有办法支持其他库可以反馈给我）
2.查询用的是QueryableWithAttr不是Queryable
//声名SqlSugar对象var db = new SqlSugarClient(new List<ConnectionConfig>(){ new ConnectionConfig(){ConfigId="A",DbType=DbType.SqlServer,ConnectionString=..,IsAutoCloseConnection=true}, new ConnectionConfig(){ConfigId="B",DbType=DbType.MySql,ConnectionString=..,IsAutoCloseConnection=true }, new ConnectionConfig(){ConfigId="C",DbType=DbType.MySql,ConnectionString=..,IsAutoCloseConnection=true }});//实体配置是哪个库[Tenant("A")] //实体标为A表示是A库public class OrderItem[Tenant("B")] //实体标为B表示是B库public class Order //联表查询要5.1.4.66+才支持var list = db.QueryableWithAttr<Order>() //根根据ConfigId自动实现跨库 .LeftJoin<OrderItem>((o,i)=> o.Id == i.OrderId) .LeftJoin<Custom>((o, i, c) => c.Id == o.CustomId) .Where((o,i,c)=> o.TypeId==1) .Select((o,i,c)=>new classA() { oid=o.Id , iname=i.Name }) .ToList(); //ISqlSugarClient需要转一下租户接口，才能用租户方法var list =db.AsTenant().QueryableWithAttr<Order>().... |
3.2 自动: PgSql跨Scheme查询
var db = new SqlSugarClient(new List<ConnectionConfig>(){ new ConnectionConfig(){ConfigId="A",DbType=DbType.PostgreSQL, ConnectionString="..",IsAutoCloseConnection=true}, new ConnectionConfig(){ConfigId="B",DbType=DbType.PostgreSQL, DbLinkName="public",//重点 ConnectionString=".....;searchpath=public"//重点 ,IsAutoCloseConnection=true }, });var x3 = db.QueryableWithAttr<OptRole>() .LeftJoin<Role>((x1, y1) => x1.roleId == y1.id)//Left Join时会加上public. .ToList(); //ISqlSugarClient需要转一下租户接口，才能用租户方法var list =db.AsTenant().QueryableWithAttr<Order>().... |
3.3 其他库同服务器
找库规则3种：
1.SqlServer: 库名.dbo.表名
2.PgSql或者同类库:有配置 DbLinkName和 searchpath : DbLinkName.表名
3.其他库: 库名.表名
用法就上面3种用法，如果发现哪个数据库不支持可以和我反馈
四、跨服务器：自动跨库查询
除了sqlite，其他的应该都支持dblink配置，只要能配置dblink就能跨库查询，也支持同服务器跨库
4.1 配置SqlServer dblink
请升级5.1.4.72-preview02+
var db = new SqlSugarClient(new List<ConnectionConfig>(){ new ConnectionConfig(){ConfigId="A",DbType=DbType.SqlServer,ConnectionString=..,IsAutoCloseConnection=true }, new ConnectionConfig(){ ConfigId="B", DbType=DbType.SqlServer, DbLinkName="db1.ecology2013_SHQC2.dbo",//配置跨库可以用的表名 ConnectionString="..",IsAutoCloseConnection=true }});//实体配置是哪个库[Tenant("B")] //实体标为A表示是A库public class OrderItem //联表查询要5.1.4.66+才支持var list1 = db.QueryableWithAttr<Order>() //根根据ConfigId自动实现跨库 .LeftJoin<OrderItem>((o,i)=> o.Id == i.OrderId) .ToList();//生成Sql //Left join db1.ecology2013_SHQC2.dbo.OrderItem |
下面是SqlServer的例子
可以企业管理器里添加linkserver实现。
使用sp_addlinkedserver创建一个链接的服务器，使其允许对分布式的、针对 OLEDB 数据源的异类查询进行访问。
在使用 sp_addlinkedserver 创建链接的服务器之后，此服务器就可以执行分布式查询。
步骤：
1. 创建linkserver
EXEC sp_addlinkedserver
@server='DB1',--被访问的服务器别名
@srvproduct='', --sqlserver不需要指定
@provider='SQLOLEDB',
5@datasrc='192.168.1.102' --要访问的服务器
2. 登录链接服务器
EXEC sp_addlinkedsrvlogin
'DB1', --被访问的服务器别名
'false', --useself
NULL, --locallogin
'sa', --帐号
'123456' --密码
4.2 配置 Oracle dblink
请升级到5.1.4.86-preview02+
var db = new SqlSugarClient(new List<ConnectionConfig>(){ new ConnectionConfig(){ConfigId="A",DbType=DbType.SqlServer,ConnectionString=..,IsAutoCloseConnection=true }, new ConnectionConfig(){ ConfigId="B", DbType=DbType.SqlServer, DbLinkName="@dblink_name",//配置dblink名字要@开头 ConnectionString=..,IsAutoCloseConnection=true }});//实体配置是哪个库[Tenant("B")] //实体标为A表示是A库public class OrderItem //联表查询要5.1.4.66+才支持var list1 = db.QueryableWithAttr<Order>() //根根据ConfigId自动实现跨库 .LeftJoin<OrderItem>((o,i)=> o.Id == i.OrderId) .ToList();//生成Sql //Left join OrderItem@dblink_name |
下面是Oracle的例子
CREATE DATABASE LINK dblink_nameCONNECT TO target_username IDENTIFIED BY target_passwordUSING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=target_host) (PORT=target_port))(CONNECT_DATA=(SERVICE_NAME=target_service_name)))'; |
4.3 配置 PgSql Pwd外部表
请升级到5.1.4.86-preview02+
var db = new SqlSugarClient(new List<ConnectionConfig>(){ new ConnectionConfig(){ConfigId="A",DbType=DbType.SqlServer,ConnectionString=..,IsAutoCloseConnection=true }, new ConnectionConfig(){ ConfigId="B", DbType=DbType.SqlServer, DbLinkName="remote_",//命名要以_结尾 ConnectionString=..,IsAutoCloseConnection=true }});//实体配置是哪个库[Tenant("B")] //实体标为A表示是A库public class OrderItem //联表查询要5.1.4.66+才支持var list1 = db.QueryableWithAttr<Order>() //根根据ConfigId自动实现跨库 .LeftJoin<OrderItem>((o,i)=> o.Id == i.OrderId) .ToList();//生成Sql //Left join remote_OrderItem |
下面是PgSql的例子
首先，在源数据库中创建 postgres_fdw 扩展：
CREATE EXTENSION postgres_fdw; |
在源数据库中创建外部服务器对象，指定目标数据库的连接信息：
CREATE SERVER target_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'targetdb', host 'targethost', port 'targetport'); |
在上面的示例中，target_server 是外部服务器的名称，你需要提供目标数据库的连接信息，如目标数据库的名称、主机和端口。
创建用户映射，将源数据库的用户映射到目标服务器的用户：
CREATE USER MAPPING FOR current_user SERVER target_server OPTIONS (user 'targetuser', password 'targetpassword'); |
确保提供正确的目标服务器的用户名和密码。
创建外部表，在源数据库中创建一个外部表，该表将映射到目标数据库中的表：
CREATE FOREIGN TABLE IF NOT EXISTS remote_OrderItem ( column1 INT, column2 TEXT, ...) SERVER target_server; |
字段结构要一样才能同步
现在，你可以在源数据库中查询外部表，就像查询本地表一样：
SELECT * FROM remote_table; |
请升级到5.1.4.86-preview02+
4.4 其他跨服务器联表
只要配置好dblinkName找表规则：
1.默认: dblinkName.[表名]
2.dblinkName以_结尾: dblinkName表名 (这个表名是没有转释符号的，像`` 、[]、 "" 我们称为转释符号)
3.dblinkName以@开头： [表名]@dblinkName
var list1 = db.QueryableWithAttr<Order>() //根根据ConfigId自动实现跨库 .LeftJoin<OrderItem>((o,i)=> o.Id == i.OrderId) .ToList();//生成Sql //Left join 规则生成的表名 |
请升级到5.1.4.86-preview02+
五、跨库子查询
5.1 用ThenMapper实现
支持跨库的类型，只能用在Select对象填充
//跨库 var mydb=db.GetConnection(1);mydb.ThenMapper(root,item=>{...}); |
5.2 Subqueryable
请升级:5.1.4.107-preview11+
db.Queryable<Order>() .Select(it => new { x= SqlFunc.Subqueryable<OrderItem>().AsWithAttr().Select(s => s.OrderId)//生成表名规则看：4.4和3.3 }) .ToList(); //也可以用AS("表名") SqlFunc.Subqueryable<Order>().AS("yyy.Order01") .InnerJoin<OrderItem>((x,y)=>x.Id==y.OrderId, "yyy.OrderItem01") .Select(x=>x.Id) |
