.NET ORM操作 PostgreSQL PGSQL

字符串格式

  
PORT=5432;DATABASE=SqlSugar4xTest;HOST=localhost;PASSWORD=haosql;USER ID=postgres

//最大连接池设置和其他库不一样，如下
//MaxPoolSize=512

//非public看标题6

1、不规范表和规范表

2种用法小有区别

1.1 规范（小写表） 

推荐： 写Sql无需考虑大小写

 //无需配置任何东西
 SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
 {
       DbType = DbType.PostgreSQL,
       ConnectionString = Config.ConnectionString3,
       IsAutoCloseConnection = true
 });
 
 
//技巧：如果想全自动 StudentName变成 student_name
SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
  {
       DbType = DbType.PostgreSQL,
       ConnectionString = Config.ConnectionString3,
       IsAutoCloseConnection = true,
       ConfigureExternalServices=new ConfigureExternalServices() 
       {
           EntityService = (x,p) => //处理列名
           {
             //要排除DTO类，不然MergeTable会有问题
             p.DbColumnName = UtilMethods.ToUnderLine(p.DbColumnName);//ToUnderLine驼峰转下划线方法
           },
           EntityNameService = (x, p) => //处理表名
           {
              //最好排除DTO类
              p.DbTableName=UtilMethods.ToUnderLine(p.DbTableName);//ToUnderLine驼峰转下划线方法
           }
        }
   });

1.2 不规范表（不自动转小写）

类是什么样，表就什么样，大小写和数据库一模一样

缺点：写SQL难受 ,需要大小写和实体一样，并且加上转译符号

var db= new SqlSugarClient(new ConnectionConfig(){
           DbType = SqlSugar.DbType.PostgreSQL,
           ConnectionString = Config.ConnectionString,
           IsAutoCloseConnection = true,
           MoreSettings= new ConnMoreSettings()
           {
             PgSqlIsAutoToLower = false,//增删查改支持驼峰表
             PgSqlIsAutoToLowerCodeFirst = false, // 建表建驼峰表。5.1.3.30 
           }})
//注意 是false不要看错了

2、JSON类型

PgSql 的Json类型操作实体需要配置

//可以是JSON对应的对象  
[SugarColumn(IsJson = true )]//低版本要加 ColumnDataType ="json"
Public List<Order> JsonText{get;set;} //只要能序列化成json的所有对象都可以

Json函数：https://www.donet5.com/Home/Doc?typeId=1232

3、数组类型

 3.1 普通数组

[SugarColumn(ColumnDataType = "text []", IsArray = true)]  
public string [] MenuIds { get; set; }


//数组函数：SqlSugarCore 5.1.4.158-preview15+
var x=Db.Queryable<UnitArrayLongtest1>()
          .Where(it => SqlFunc.PgsqlArrayContains(it.ids , 1))//数组包含
          .ToList();

 3.2 vector类型

https://www.donet5.com/ask/9/23319  用户1解决

https://www.donet5.com/Ask/9/26619  用户2解决

4、geometry / postgis

Nuget安装 Npgsql.NetTopologySuite7.0以下版本和SqlSugarCore

//*************  用法1 ******************
 SqlSugarClient Db= new SqlSugarClient(new ConnectionConfig(){
           ConnectionString = "连接符字串", 
           DbType = DbType.SqlServer,
           IsAutoCloseConnection = true},
           db=>{
                  //配置使用这个类型
                  NpgsqlConnection.GlobalTypeMapper.UseNetTopologySuite();
            
           });
 
//实体
[SugarColumn(ColumnName="geom")]
public Geometry geom { get; set; }


var list=db.Queryable<xx>().ToList(); 



//*************用法2******************
//实体
[SugarColumn(ColumnName="geom")]
public Geometry geom { get; set; }

//代码    
          //使用长链接 （如果不使用OpenAways需要将自动释放关闭手动Open Close）
          using (db.Ado.OpenAlways()) {
   

             (db.Ado.Connection as  NpgsqlConnection).TypeMapper.UseNetTopologySuite(
              new DotSpatialAffineCoordinateSequenceFactory(Ordinates.XY),
              handleOrdinates: Ordinates.XY); //配置相关代码


             var list=db.Queryable<xx>().ToList();//查询插入就可以用了
            
             
            }
//注意：Npgsql.NetTopologySuite7.0以下版本，不要高版本，高版本写法有变你们可以研究。

高版本用法：https://www.donet5.com/Ask/9/19286

5、时间异常

Cannot write DateTime with Kind=Unspecified to PostgreSQL type 'timestamp with time zone', 

only UTC is supported. Note that it's not possible to mix DateTimes with different 

Kinds in an array/range. See the Npgsql.EnableLegacyTimestampBehavior

 AppContext switch to enable legacy behavior.

ORM默认配置如下

  //ORM源码：
  if (StaticConfig.AppContext_ConvertInfinityDateTime == false)
  {
      AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);
      AppContext.SetSwitch("Npgsql.DisableDateTimeInfinityConversions", true);
  }
  //如果不想用默认的可以把
  StaticConfig.AppContext_ConvertInfinityDateTime=true;//这样就不走ORM默认

https://github.com/npgsql/doc/blob/main/conceptual/Npgsql/types/datetime.md/

6、架构的支持 schema

连接字符串上加上 searchpath=架构名  ，可以支持多架构  （5.0.9.1版本支持）

7、自增配置

如果不使用CodeFirst, 建表实现自增应该下面这样写

DROP TABLE IF EXISTS "public"."order";
CREATE TABLE "public"."order" (
  "id" int4 NOT NULL DEFAULT nextval('order_id_seq'::regclass),
  "name" varchar(200)   NOT NULL,
  "price" numeric NOT NULL,
  "createtime" timestamp(6),
  "customid" int4
)

8、自定义修改列方法

像一些复杂的需要用using来处理类型转换

ALTER TABLE "app_1714835140380856320" ALTER COLUMN "A" TYPE INT4
USING "A"::INT4;//加上using就能强转

ALTER COLUMN "ABC" TYPE int8
USING CASE 
       WHEN pg_typeof("ABC")::text = 'text' THEN 2
       WHEN pg_typeof("ABC")::text = 'json' THEN 1
       ELSE 0  -- 或者其他默认值
       END;

AOP修改SQL示例

   db.Aop.OnExecutingChangeSql = (s, p) =>
   {
       if (s.StartsWith("alter table ") && s.Contains("  type ") && !s.Contains(" USING "))
       {
          var type= s.Split("  type ").Last();
          var columnName = s.Split(" ALTER COLUMN ").Last().Split(" ").First();
          s = $"{s} USING {columnName}::{type}";//这儿的逻辑自已处理
       }
       return new KeyValuePair<string, SugarParameter[]>(s, p);
   };

9、ILike 不区分大小写

like是区分大小写的，想不区分大小写要启用

IsAutoCloseConnection = true,
DbType = DbType.PostgreSQL,
ConnectionString = Connection,
MoreSettings=new ConnMoreSettings() 
{
     EnableILike=true
}