CREATE table Category
(
  CategoryId int primary key not null identity,
  CategoryName varchar(64)
)

CREATE TABLE [dbo].[ProductDetail](
    [ProductId] [int] IDENTITY(1,1) NOT NULL,
    [ProductName] [varchar](32) NULL,
    [Price] [decimal](9, 2) NULL,
    [CategoryId] [int] NULL,
PRIMARY KEY CLUSTERED
(
    [ProductId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[ProductDetail]  WITH CHECK ADD  CONSTRAINT [FK_Category] FOREIGN KEY([CategoryId])
REFERENCES [dbo].[Category] ([CategoryId])
GO

ALTER TABLE [dbo].[ProductDetail] CHECK CONSTRAINT [FK_Category]
GO

--Category表插入數(shù)據(jù)
INSERT INTO Category (CategoryName)
select '電子產(chǎn)品' union
SELECT '家用電器' UNION
SELECT '圖書(shū)'

--ProductDetail表插入數(shù)據(jù)
INSERT INTO ProductDetail (ProductName,Price,CategoryId)
SELECT '蘋(píng)果6s手機(jī)',5633,1 UNION
SELECT 'Dell電腦',6998,1 UNION
SELECT '佳能相機(jī)',5633,1 UNION
SELECT '海爾洗衣機(jī)',1234,2 UNION
SELECT '格力空調(diào)',2344,2 UNION
SELECT '美的冰箱',3218,2 UNION
SELECT '白鹿原',342,3 UNION
SELECT 'C#高級(jí)編程(第十版)',145,3 UNION
SELECT '平凡的世界',231,3

//------------------------------------------------------------------------------
// <auto-generated>
//     此代碼已從模板生成。
//
//     手動(dòng)更改此文件可能導(dǎo)致應(yīng)用程序出現(xiàn)意外的行為。
//     如果重新生成代碼，將覆蓋對(duì)此文件的手動(dòng)更改。
// </auto-generated>
//------------------------------------------------------------------------------

namespace EFNavigateDemo
{
    using System;
    using System.Collections.Generic;

    public partial class Category
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public Category()
        {
            this.ProductDetails = new HashSet<ProductDetail>();
        }

        public int CategoryId { get; set; }
        public string CategoryName { get; set; }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<ProductDetail> ProductDetails { get; set; }
    }
}

//------------------------------------------------------------------------------
// <auto-generated>
//     此代碼已從模板生成。
//
//     手動(dòng)更改此文件可能導(dǎo)致應(yīng)用程序出現(xiàn)意外的行為。
//     如果重新生成代碼，將覆蓋對(duì)此文件的手動(dòng)更改。
// </auto-generated>
//------------------------------------------------------------------------------

namespace EFNavigateDemo
{
    using System;
    using System.Collections.Generic;

    public partial class ProductDetail
    {
        public int ProductId { get; set; }
        public string ProductName { get; set; }
        public Nullable<decimal> Price { get; set; }
        public Nullable<int> CategoryId { get; set; }

        public virtual Category Category { get; set; }
    }
}

using (var dbContext = new CategoryEntities())
{
        dbContext.Configuration.LazyLoadingEnabled = true; // 默認(rèn)是true，針對(duì)導(dǎo)航屬性
         var categoryList = dbContext.Set<Category>().Where(p => p.CategoryId == 3);
         // 只會(huì)在數(shù)據(jù)庫(kù)里面查詢Category表，不會(huì)查詢ProductDetail表
         foreach(var category in categoryList)
         {
              Console.WriteLine("CategoryId:"+category.CategoryId+ ",CategoryName:"+category.CategoryName);
              // 這時(shí)才會(huì)去數(shù)據(jù)庫(kù)查詢ProductDetail表
              foreach (var product in category.ProductDetails)
              {
                  Console.WriteLine("ProductName:"+product.ProductName);
              }
          }
}

using (var dbContext = new CategoryEntities())
{
      dbContext.Configuration.LazyLoadingEnabled = false; // 不延遲加載,不會(huì)再次查詢了
      var categoryList = dbContext.Set<Category>().Where(p => p.CategoryId == 3);
       // 只會(huì)在數(shù)據(jù)庫(kù)里面查詢Category表，不會(huì)查詢ProductDetail表
       foreach (var category in categoryList)
       {
            Console.WriteLine("CategoryId:" + category.CategoryId + ",CategoryName:" + category.CategoryName);
            // 這時(shí)不會(huì)去數(shù)據(jù)庫(kù)查詢了，所以用戶全是空的
            foreach (var product in category.ProductDetails)
            {
               Console.WriteLine("ProductName:" + product.ProductName);
            }
       }
}

// 顯示加載
using (var dbContext = new CategoryEntities())
{
       // 不延遲加載，指定Include，一次性加載主表和從表的所有數(shù)據(jù)
       var categoryList = dbContext.Set<Category>().Include("ProductDetails").Where(p => p.CategoryId == 3);
       foreach (var category in categoryList)
       {
            Console.WriteLine("CategoryId:" + category.CategoryId + ",CategoryName:" + category.CategoryName);
            // 不會(huì)再查詢
            foreach (var product in category.ProductDetails)
            {
               Console.WriteLine("ProductName:" + product.ProductName);
            }
       }
}

//LoadProperty 手動(dòng)加載
using (var dbContext = new CategoryEntities())
{
      dbContext.Configuration.LazyLoadingEnabled = false; // 不延遲加載,不會(huì)再次查詢了
      var categoryList = dbContext.Set<Category>().Where(p => p.CategoryId == 3);
      foreach (var category in categoryList)
      {
            Console.WriteLine("CategoryId:" + category.CategoryId + ",CategoryName:" + category.CategoryName);
            dbContext.Entry<Category>(category).Collection(p => p.ProductDetails).Load();// 集合顯示加載
             foreach (var product in category.ProductDetails)
             {
                 Console.WriteLine("ProductName:" + product.ProductName);
             }
       }
}

using (var dbContext = new CategoryEntities())
{
      using (TransactionScope trans = new TransactionScope())
      {
           Category category = new Category()
           {
                CategoryName = "自行車"
           };
           dbContext.Categories.Add(category);
           dbContext.SaveChanges();//category.CategoryId賦值了
           ProductDetail product = new ProductDetail()
           {
                 ProductName = "美利達(dá)",
                 Price = 2312,
                 CategoryId = category.CategoryId
            };

            dbContext.ProductDetails.Add(product);
            dbContext.SaveChanges();
            trans.Complete();//提交事務(wù)
      }
}

using (var dbContext = new CategoryEntities())
{
      using (TransactionScope trans = new TransactionScope())
      {
           Category category = new Category()
           {
                CategoryName = "汽車"
           };

           ProductDetail product = new ProductDetail()
           {
                 ProductName = "上海大眾",
                 Price = 190090,
                 CategoryId = category.CategoryId
            };

            category.ProductDetails = new List<ProductDetail>() { product};
            dbContext.Categories.Add(category);
            dbContext.SaveChanges();
            trans.Complete();//提交事務(wù)
       }
}