使用Sequence
EntityFramework core 原生支持数据库的Sequence,但是其他Entity Framework 6.x并不支持数据库的Sequence。这里讲的就是如何在Entity Framework 6.x中使用Sequence。
步骤:
1. 创建一个自己的初始化类,并重写InitializeDatabase函数,在InitializeDatabase中通过Sql语句创建我们要用的Sequence。
2. 在数据模型类(class MyContex : DbContext)中提供一个GetSequeceNumber的函数,函数调用Database.SqlQuery<long>查询Sequence的值。
示例代码:
namespace Sequece
{
class TableItem
{
public int ID { get; set; }
public long SequenceNumber { get; set; }
}
class MyContex : DbContext
{
public const string connectstr = "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=EFSequece;Integrated Security=True;";
public const string SequenceName = "TestSequece";
public DbSet<TableItem> TableItems { get; set; }
public long GetTableSuequence()
{
return GetSequenceNumber(SequenceName);
}
public MyContex() :
base(connectstr)
{
}
static MyContex()
{
Database.SetInitializer(new SequenceDBInitializer());
}
private long GetSequenceNumber(string sequenceName)
{
return Database.SqlQuery<long>($"SELECT NEXT VALUE FOR [{sequenceName}] SIDd").FirstOrDefault();
}
class SequenceDBInitializer :CreateDatabaseIfNotExists<MyContex>
{
public override void InitializeDatabase(MyContex context)
{
base.InitializeDatabase(context);
CreateSequence(context, SequenceName);
}
private void CreateSequence(MyContex context, string SequenceName)
{
var query = context.Database.SqlQuery<int>($@"SELECT count(*) FROM sys.sequences WHERE name = '{SequenceName}'").FirstOrDefault();
if (query == 0)//数据库中如果不存在则创建Sequence。
{
context.Database.ExecuteSqlCommand($"CREATE SEQUENCE [dbo].[{SequenceName}] AS [bigint] START WITH 1 INCREMENT BY 1");
}
}
}
}
class Program
{
static void Main(string[] args)
{
using (var db = new MyContex())
{
for (int i = 0; i < 3; i++)
{
var s = db.GetTableSuequence();
Console.WriteLine($"{i} Sequence Number:{s}");
db.TableItems.Add(new TableItem() { SequenceNumber = s });
}
db.SaveChanges();
}
}
}
}