Entity Framework——使用Sequence

使用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();
            }
        }
    }
}