(三)EFCore(以FileStream写入内容到指定文件生成实体，呢种方式比CodeFirst，DbFirst更靠谱，更可控),类似于代码生成器呢种

实现思路:

1：先把所有的表查询出来,以列表展示到系统里面

2：选中某一个或者多个表,然后生成对应的实体

3：查询所有表跟视图的sql

select * from sysobjects obj where obj.xtype IN ('U','V')

4：建立存放一张表所有列的属性的实体(比如列的类型,长度,是否允许为空)

public class EntityColumn
{
// 表名
public string TableName { get; set; }
// Sort
public int Sort { get; set; }
//列名
public string ColName { get; set; }
//列说明
public string Experion { get; set; }
//数据类型
public string Coltype { get; set; }
//列长度
public int ColLenth { get; set; }
//小数位数
public int Xiaoshuweishu { get; set; }
//是否标识(自增列)
public string Biaoshi { get; set; }
//是否主键
public string Iskey { get; set; }
//是否允许为空
public string ColIsnull { get; set; }
//默认值
public string Defaultvalue { get; set; }
}

5：根据表名称查询该表的所有列，以及列对应的所有属性存到EntityColumn集合里面(比如列的类型,长度,是否允许为空)

public List<EntityColumn> GetColumn(string tableName)
{
string sql = @"SELECT CASE WHEN col.colorder = 1 THEN obj.name
ELSE ''
END AS TableName,--表明
col.colorder AS Sort ,--序号
col.name AS ColName ,--列名
ISNULL(ep.[value], '') AS Experion, --列说明
t.name AS Coltype ,--数据类型
col.length AS ColLenth ,--列长度
ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS Xiaoshuweishu ,--小数位数
CASE WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN 'true'
ELSE ''
END AS Biaoshi ,--标识(自增列)
CASE WHEN EXISTS ( SELECT 1
FROM dbo.sysindexes si
INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
AND si.indid = sik.indid
INNER JOIN dbo.syscolumns sc ON sc.id = sik.id
AND sc.colid = sik.colid
INNER JOIN dbo.sysobjects so ON so.name = si.name
AND so.xtype = 'PK'
WHERE sc.id = col.id
AND sc.colid = col.colid ) THEN 'true'
ELSE ''
END AS Iskey ,--是否主键
CASE WHEN col.isnullable = 1 THEN 'true'
ELSE ''
END AS ColIsnull ,--是否允许为空
ISNULL(comm.text, '') AS Defaultvalue --默认值
FROM dbo.syscolumns col
LEFT JOIN dbo.systypes t ON col.xtype = t.xusertype
inner JOIN dbo.sysobjects obj ON col.id = obj.id
AND obj.xtype IN ('U','V') --AND obj.xtype = 'U' 表或者视图都要进行查询
AND obj.status >= 0
LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.id
LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id
AND col.colid = ep.minor_id
AND ep.name = 'MS_Description'
LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id
AND epTwo.minor_id = 0
AND epTwo.name = 'MS_Description'
WHERE obj.name = @TableName--表名
ORDER BY col.colorder ;";

List<EntityColumn> list = _sqlsugarContext.DB.SqlQueryable<EntityColumn>(sql).AddParameters(new { TableName = tableName }).ToList();
return list;
}

6：通过文件流创建文件,并且通过写的文件流,把上面查询的结果写入.cs后缀的文件,生成实体

public void CreateFile(string tableName)
{

string s = System.Environment.CurrentDirectory;
string solutionUrl = new DirectoryInfo(s).Parent.ToString();
string file = tableName + ".cs";
string createEntityUrl = Path.Combine(solutionUrl, "Entitys\\program\\" + file);
StringBuilder entityContent = new StringBuilder();
entityContent.AppendLine("using System;");
entityContent.AppendLine("using System.Collections.Generic; ");
entityContent.AppendLine("using System.Text; ");
entityContent.AppendLine("using System.ComponentModel.DataAnnotations.Schema;");
entityContent.AppendLine("using System.ComponentModel.DataAnnotations;");
entityContent.AppendLine("using Entitys.program;");
entityContent.AppendLine("namespace Entity");
entityContent.AppendLine(" { ");

entityContent.AppendLine(" [Table(\"" + file.Replace(".cs", "") + "\" )]");
entityContent.AppendLine(" public class " + file.Replace(".cs", "") + ": BaseEntity");
entityContent.AppendLine(" {");

#region 动态加载内容
List<EntityColumn> cols = GetColumn(tableName);
foreach (EntityColumn col in cols)
{
entityContent.AppendLine(" /// <summary>");
entityContent.AppendLine(" /// " + col.Experion + "");//备注
entityContent.AppendLine(" /// </summary>");
//是否是主键
if (col.Iskey == "true")
{
entityContent.AppendLine(" [Key]");
}
entityContent.AppendLine(" [Display(Name = \"" + col.Experion + "\")]");
string isnull = "";
string formatAttr = "string ";
string len = "";//nvarchar需要列长度
switch (col.Coltype)
{
case "nvarchar":
case "char":
case "nchar":
case "varchar":
if (col.ColLenth != -1)
{
entityContent.AppendLine(" [MaxLength(" + col.ColLenth + ")]");
}
if (col.Coltype == "nvarchar" || col.Coltype == "nchar")//nvarchar查询出来的长度会是数据库字段的两倍
{
len = (col.ColLenth == -1 ? "(max)" : "(" + col.ColLenth / 2 + ")");
}
else
{
len = (col.ColLenth == -1 ? "(max)" : "(" + col.ColLenth + ")");
}
break;
case "decimal":
if (col.ColIsnull == "true")
{
isnull = "? ";
}
formatAttr = "decimal ";
entityContent.AppendLine(" [DisplayFormat(DataFormatString = \"" + col.ColLenth * 2 + ", " + col.Xiaoshuweishu + "\")]");
break;
case "datetime":
case "smalldatetime":
case "date":
if (col.ColIsnull == "true")
{
isnull = "? ";
}
formatAttr = "DateTime ";
break;
case "int":
case "bigint":
case "smallint":
case "tinyint":
if (col.ColIsnull == "true")
{
isnull = "? ";
}
formatAttr = "int ";
break;
case "bit":
if (col.ColIsnull == "true")
{
isnull = "? ";
}
formatAttr = "bool ";
break;
case "uniqueidentifier":
if (col.ColIsnull == "true")
{
isnull = "? ";
}
formatAttr = "Guid ";
break;
case "money":
if (col.ColIsnull == "true")
{
isnull = "? ";
}
formatAttr = "decimal ";
entityContent.AppendLine(" [DisplayFormat(DataFormatString = \"15, 4\")]");
break;
case "text":
formatAttr = "string ";
break;
case "image":
formatAttr = "byte[] ";
break;
case "float":
formatAttr = "float ";
break;

}

//数据类型
entityContent.AppendLine(" [Column(TypeName = \"" + col.Coltype + len + "\")]");
if (col.Biaoshi == "true")
{
entityContent.AppendLine(" [DatabaseGenerated(DatabaseGeneratedOption.Identity)]");
}
if (col.ColIsnull != "true")
{
entityContent.AppendLine(" [Required(AllowEmptyStrings = false)]");
}

//生成属性
entityContent.AppendLine(" public " + formatAttr + isnull + ToFirstLower(col.ColName) + " { get; set; }");

 

}

#endregion
entityContent.AppendLine(" }");

entityContent.AppendLine(" }");

//创建文件
FileStream fs = new FileStream(createEntityUrl, FileMode.Create, FileAccess.ReadWrite); //可以指定盘符，也可以指定任意文件名，还可以为word等文件 OpenOrCreate
StreamWriter sw = new StreamWriter(fs); // 创建写入流
sw.WriteLine(entityContent);
sw.Close(); //关闭文件
}

7：将表里面首写字母转换成大写

public string ToFirstLower(string fildName)
{
string first = fildName.Substring(0, 1).ToUpper();
string rest = fildName.Substring(1, fildName.Length - 1);
string newStr = new StringBuilder(first).Append(rest).ToString();
return newStr;
}

//上面就是以写文件流将查询出来的表架构动态生成实体的全过程