EntityFramework Core筆記：儲存資料（4）

libingql發表於2018-05-31
Framework筆記

1. 基本儲存

　　每個DBContext例項都有一個ChangeTracker，負責跟蹤需要寫入資料庫的更改。當例項發生更改時，更改會被記錄在ChangeTracker中，在呼叫 SaveChanges 時被寫入資料庫。

1.1 新增資料

　　使用 DbSet.Add()新增實體類的新例項。 呼叫 SaveChanges() 時，資料將插入到資料庫中。

using (var context = new LibingContext())
{
    var role = new Role
    {
        RoleName = "教師"
    };
    context.Roles.Add(role);

    context.SaveChanges();
}

1.2 更新資料

　　Entity Framwork Core將自動檢測對由DbContext跟蹤的實體所做的更改。

　　更新資料：修改屬性值，呼叫 SaveChanges()。

using (var context = new LibingContext())
{
    var role = context.Roles.Find(1);
    role.RoleName = "教師";

    context.SaveChanges();
}

1.3 刪除資料

　　使用 DbSet.Remove() 刪除實體類的例項。

　　如果實體已存在於資料庫中，則 SaveChanges() 將刪除該實體。 如果實體尚未儲存到資料庫（即跟蹤為“已新增”），則 SaveChanges() 時，該實體會從上下文中刪除且不再插入。

using (var context = new LibingContext())
{
    var role = context.Roles.Find(1);
    context.Roles.Remove(role);

    context.SaveChanges();
}

1.4 一個SaveChanges 中的多個操作

　　可以將多個新增/更新/刪除操作合併到對“SaveChanges”的單個呼叫。

　　對於大多數資料庫提供程式，“SaveChanges”是事務性的。

using (var context = new LibingContext())
{
    // 新增
    context.Roles.Add(new Role { RoleName = "管理員" });
    context.Roles.Add(new Role { RoleName = "學生" });

    // 修改
    var modifyRole = context.Roles.Find(1);
    modifyRole.RoleName = "教師";

    // 刪除
    var deleteRole = context.Roles.Where(t => t.RoleID == 2).FirstOrDefault();
    context.Roles.Remove(deleteRole);

    context.SaveChanges();
}

2. 關聯資料

2.1 新增新實體的關係圖

using (var context = new LibingContext())
{                
    var category = new Category
    {
        CategoryName = "手機",
        Products = new List<Product> {
            new Product { ProductName = "華為", UnitPrice = 2000 },
            new Product { ProductName = "小米", UnitPrice = 1000 }
        }
    };
    context.Categories.Add(category);

    context.SaveChanges();
}
exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [Category] ([CategoryName])
VALUES (@p0);
SELECT [CategoryID]
FROM [Category]
WHERE @@ROWCOUNT = 1 AND [CategoryID] = scope_identity();

',N'@p0 nvarchar(4000)',@p0=N'手機'
exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [Product] ([CategoryID], [ProductName], [UnitPrice])
VALUES (@p0, @p1, @p2);
SELECT [ProductID]
FROM [Product]
WHERE @@ROWCOUNT = 1 AND [ProductID] = scope_identity();

',N'@p0 int,@p1 nvarchar(4000),@p2 decimal(4,0)',@p0=1,@p1=N'華為',@p2=2000
exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [Product] ([CategoryID], [ProductName], [UnitPrice])
VALUES (@p0, @p1, @p2);
SELECT [ProductID]
FROM [Product]
WHERE @@ROWCOUNT = 1 AND [ProductID] = scope_identity();

',N'@p0 int,@p1 nvarchar(4000),@p2 decimal(4,0)',@p0=1,@p1=N'小米',@p2=1000

2.2 新增關聯實體

　　如果從已由DbContext跟蹤的實體的導航屬性中引用新實體，則該實體將插入到資料庫中。

using Microsoft.EntityFrameworkCore;
using (var context = new LibingContext())
{
    var category = context.Categories
        .Include(t => t.Products)
        .Where(t => t.CategoryID == 1)
        .FirstOrDefault();
    category.Products.Add(new Product
    {
        ProductName = "VIVO",
        UnitPrice = 1500
    });

    context.SaveChanges();
}
SELECT TOP(1) [t].[CategoryID], [t].[CategoryName]
FROM [Category] AS [t]
WHERE [t].[CategoryID] = 1
ORDER BY [t].[CategoryID]
SELECT [t.Products].[ProductID], [t.Products].[CategoryID], [t.Products].[ProductName], [t.Products].[UnitPrice]
FROM [Product] AS [t.Products]
INNER JOIN (
    SELECT TOP(1) [t0].[CategoryID]
    FROM [Category] AS [t0]
    WHERE [t0].[CategoryID] = 1
    ORDER BY [t0].[CategoryID]
) AS [t1] ON [t.Products].[CategoryID] = [t1].[CategoryID]
ORDER BY [t1].[CategoryID]
exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [Product] ([CategoryID], [ProductName], [UnitPrice])
VALUES (@p0, @p1, @p2);
SELECT [ProductID]
FROM [Product]
WHERE @@ROWCOUNT = 1 AND [ProductID] = scope_identity();

',N'@p0 int,@p1 nvarchar(4000),@p2 decimal(4,0)',@p0=1,@p1=N'VIVO',@p2=1500

2.3 更改關係

　　如果更改實體的導航屬性，則將對資料庫中的外來鍵列進行相應的更改。

using (var context = new LibingContext())
{
    var category = context.Categories.Find(2);
    var product = context.Products.Find(1);
    product.Category = category;

    context.SaveChanges();
}
exec sp_executesql N'SELECT TOP(1) [e].[CategoryID], [e].[CategoryName]
FROM [Category] AS [e]
WHERE [e].[CategoryID] = @__get_Item_0',N'@__get_Item_0 int',@__get_Item_0=2
exec sp_executesql N'SELECT TOP(1) [e].[ProductID], [e].[CategoryID], [e].[ProductName], [e].[UnitPrice]
FROM [Product] AS [e]
WHERE [e].[ProductID] = @__get_Item_0',N'@__get_Item_0 int',@__get_Item_0=1
exec sp_executesql N'SET NOCOUNT ON;
UPDATE [Product] SET [CategoryID] = @p0
WHERE [ProductID] = @p1;
SELECT @@ROWCOUNT;

',N'@p1 int,@p0 int',@p1=1,@p0=2

2.4 刪除關係

　　可以通過將引用導航設定為 null 或從集合導航中刪除相關實體來刪除關係。

　　預設情況下，對於必選關係，將配置級聯刪除行為，並將從資料庫中刪除子實體/依賴實體。 對於可選關係，預設情況下不會配置級聯刪除，但會將外來鍵屬性設定為 null。

using (var context = new LibingContext())
{
    var category = context.Categories
        .Include(t => t.Products)
        .Where(t => t.CategoryID == 1)
        .FirstOrDefault();

    //category.Products.Remove(category.Products.FirstOrDefault());
    category.Products = null;

    context.SaveChanges();
}
SELECT TOP(1) [t].[CategoryID], [t].[CategoryName]
FROM [Category] AS [t]
WHERE [t].[CategoryID] = 1
ORDER BY [t].[CategoryID]
SELECT [t.Products].[ProductID], [t.Products].[CategoryID], [t.Products].[ProductName], [t.Products].[UnitPrice]
FROM [Product] AS [t.Products]
INNER JOIN (
    SELECT TOP(1) [t0].[CategoryID]
    FROM [Category] AS [t0]
    WHERE [t0].[CategoryID] = 1
    ORDER BY [t0].[CategoryID]
) AS [t1] ON [t.Products].[CategoryID] = [t1].[CategoryID]
ORDER BY [t1].[CategoryID]
exec sp_executesql N'SET NOCOUNT ON;
DELETE FROM [Product]
WHERE [ProductID] = @p0;
SELECT @@ROWCOUNT;

',N'@p0 int',@p0=2
exec sp_executesql N'SET NOCOUNT ON;
DELETE FROM [Product]
WHERE [ProductID] = @p0;
SELECT @@ROWCOUNT;

',N'@p0 int',@p0=3

3. 級聯刪除

相關文章