2014年12月17日 星期三

Entity Framework DbContext 新增、修改、刪除、查詢

首先建立一個留言版的資料庫來當例子

DemoContext
namespace ConsoleApplication1.Models
{
    using System.Data.Entity;

    public class DemoContext : DbContext
    {
        public DbSet<Topic> Topic { get; set; }
        public DbSet<Reply> Reply { get; set; }
    }
}

留言版主表
namespace ConsoleApplication1.Models
{
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;

    [Table("Topic")]
    public class Topic
    {
        [Key]
        public int TopicId { get; set; }
        public string TopicTitle { get; set; }
        public virtual ICollection<Reply> Reply { get; set; }
    }
}

留言版明細表
namespace ConsoleApplication1.Models
{
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;

    [Table("Reply")]
    public class Reply
    {
        [Key]
        public int ReplyId { get; set; }
        [ForeignKey("Topic")]
        public int TopicId { get; set; }
        public string ReplyMessage { get; set; }
        public virtual Topic Topic { get; set; }
    }
}

新增、修改、刪除、查詢
namespace ConsoleApplication1
{
    using ConsoleApplication1.Models;
    using System.Collections.Generic;
    using System.Data.Entity;
    using System.Linq;

    class Program
    {
        static void Main(string[] args)
        {
            // 初始化資料庫
            Database.SetInitializer(new DropCreateDatabaseAlways<DemoContext>());
            using (DemoContext db = new DemoContext())
            {
                db.Database.Initialize(false);
            }

            // 新增
            using (DemoContext db = new DemoContext())
            {
                db.Topic.Add(new Topic
                {
                    TopicTitle = "a",
                    Reply = new List<Reply>
                    {
                        new Reply { ReplyMessage = "a1" },
                        new Reply { ReplyMessage = "a2" },
                        new Reply { ReplyMessage = "a3" },
                    }
                });

                db.SaveChanges();
            }

            // 修改
            using (DemoContext db = new DemoContext())
            {
                var model = db.Topic.FirstOrDefault();
                if (model != null)
                {
                    model.TopicTitle = "modify";
                    db.SaveChanges();
                }
            }

            // 查詢
            using (DemoContext db = new DemoContext())
            {
                var model1 = db.Topic.Find(1);
                var model2 = db.Topic.SingleOrDefault();
                var model3 = db.Topic.FirstOrDefault();
            }

            // 刪除
            using (DemoContext db = new DemoContext())
            {
                var model = new Topic { TopicId = 1 };
                db.Entry(model).State = EntityState.Deleted;
                db.SaveChanges();
            }
        }
    }
}

新增的SQL
exec sp_reset_connection
go
exec sp_executesql N'INSERT [dbo].[Topic]([TopicTitle])
VALUES (@0)
SELECT [TopicId]
FROM [dbo].[Topic]
WHERE @@ROWCOUNT > 0 AND [TopicId] = scope_identity()',N'@0 nvarchar(max) ',@0=N'a'
go
exec sp_executesql N'INSERT [dbo].[Reply]([TopicId], [ReplyMessage])
VALUES (@0, @1)
SELECT [ReplyId]
FROM [dbo].[Reply]
WHERE @@ROWCOUNT > 0 AND [ReplyId] = scope_identity()',N'@0 int,@1 nvarchar(max) ',@0=1,@1=N'a1'
go
exec sp_executesql N'INSERT [dbo].[Reply]([TopicId], [ReplyMessage])
VALUES (@0, @1)
SELECT [ReplyId]
FROM [dbo].[Reply]
WHERE @@ROWCOUNT > 0 AND [ReplyId] = scope_identity()',N'@0 int,@1 nvarchar(max) ',@0=1,@1=N'a2'
go
exec sp_executesql N'INSERT [dbo].[Reply]([TopicId], [ReplyMessage])
VALUES (@0, @1)
SELECT [ReplyId]
FROM [dbo].[Reply]
WHERE @@ROWCOUNT > 0 AND [ReplyId] = scope_identity()',N'@0 int,@1 nvarchar(max) ',@0=1,@1=N'a3'
go


修改的SQL
exec sp_reset_connection
go
SELECT TOP (1) 
    [c].[TopicId] AS [TopicId], 
    [c].[TopicTitle] AS [TopicTitle]
    FROM [dbo].[Topic] AS [c]
go
exec sp_reset_connection
go
exec sp_executesql N'UPDATE [dbo].[Topic]
SET [TopicTitle] = @0
WHERE ([TopicId] = @1)
',N'@0 nvarchar(max) ,@1 int',@0=N'modify',@1=1
go


查詢的SQL
exec sp_reset_connection
go
exec sp_executesql N'SELECT TOP (2) 
    [Extent1].[TopicId] AS [TopicId], 
    [Extent1].[TopicTitle] AS [TopicTitle]
    FROM [dbo].[Topic] AS [Extent1]
    WHERE [Extent1].[TopicId] = @p0',N'@p0 int',@p0=1
go
exec sp_reset_connection
go
SELECT TOP (2) 
    [c].[TopicId] AS [TopicId], 
    [c].[TopicTitle] AS [TopicTitle]
    FROM [dbo].[Topic] AS [c]
go
exec sp_reset_connection
go
SELECT TOP (1) 
    [c].[TopicId] AS [TopicId], 
    [c].[TopicTitle] AS [TopicTitle]
    FROM [dbo].[Topic] AS [c]
go


刪除的SQL
exec sp_reset_connection
go
exec sp_executesql N'DELETE [dbo].[Topic]
WHERE ([TopicId] = @0)',N'@0 int',@0=1
go