ASP.NET EF 延迟加载，导航属性延迟加载

EF(EntityFramework)原理：属于ORM的一种实现

通过edmx文件来查看三部分：概念模型，数据模型，映射关系，上下文DbContext完成连接、状态跟踪管理，核心类是EntityClient完成映射

EF(EntityFramework)延迟加载：

>1：EF查询默认会延迟加载

>2：EF对于集合类型的导航属性会延迟加载

本质：IQueryable拥有3个成员，Expression，Type，Provider

IQueryable与IEnumberable对比区别：

IQueryable: 可以拼接一个完成的SQL语句，然后请求数据库，拿到需要的数据

IEnumberable:直接把第一个命令请求数据库，然后拿到数据，在内存当中对于后续条件进行筛选。

把IQueryable 转换为IEnumberable ：IQueryable.AsEnumberable();

EF非延迟加载：使用ToList()方法将结果立即拿到内存中(最好把命令全部拼接完之后使用ToList())

EF导航属性的非延迟加载：Include("") 可以使导航属性非延迟加载

EF延迟加载 优点：用时才加载数据，保证数据的有效性

EF延迟加载 缺点：每次访问都加载一次，加重了数据库服务器的负担

create database MyFirstEF
on primary
(
    name='MyFirstEF.mdf',
    --修改为自己电脑上SQL DB路径
    filename='E:ProgramMSSQLServerDBMyFirstEF.mdf',
    size=5mb,
    maxsize=100mb,
    filegrowth=10%
)
log on
(
    name='MyFirstEF_log.ldf',
    --修改为自己电脑上SQL DB路径
    filename='E:ProgramMSSQLServerDBMyFirstEF_log.ldf',
    size=2mb,
    maxsize=100mb,
    filegrowth=5mb
)
go

use MyFirstEF
go

create table CustomerInfo
(
    id int identity(1,1) primary key,
    customerName nvarchar(100) not null,
    customerDate datetime
)
go

create table OrderInfo
(
  id int identity(1,1) primary key,
  orderName nvarchar(100),
  customerId int
)
go

alter table OrderInfo
add constraint FK_OrderInfo_CustomerInfo foreign key(customerId) references CustomerInfo(id)
go

insert into CustomerInfo 
select 'aa',GETDATE() union all
select 'bb',GETDATE() union all
select 'cc',GETDATE() union all
select 'dd',GETDATE() 
go

insert into OrderInfo
select 'bike1',2 union all
select 'bike2',2 union all
select 'car1',3 union all
select 'car2',3 union all
select 'chezi1',4 union all
select 'chezi2',4 
go

select * from CustomerInfo
go

select * from OrderInfo
go

>1：EF查询默认会延迟加载

DbContext context = new MyFirstEFEntities();
//1：EF默认延迟加载，执行完下面的语句，数据库并没有SQL查询语句
var rows = context.Set<CustomerInfo>().Select(c => c);
//2：查询一次数据库
Console.WriteLine(rows.Count());
//3：第二次查询数据库
Console.WriteLine(rows.Count());

使用MS SQL Server Profiler(工具-->SQL Server Profiler),可以监测到上面代码执行了两次查询数据库操作

两次查询数据库SQL：

SELECT 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        COUNT(1) AS [A1]
        FROM [dbo].[CustomerInfo] AS [Extent1]
    )  AS [GroupBy1]

>EF非延迟加载：ToList()方法

DbContext context = new MyFirstEFEntities();
//1:直接根据拼接SQL　查询数据库
var rows = context.Set<CustomerInfo>().Select(c => c).ToList();

//2：在内存中统计Count() 不会重新查询数据库
Console.WriteLine(rows.Count());
//3：在内存中统计Count() 不会重新查询数据库
Console.WriteLine(rows.Count());

使用MS SQL Server Profiler(工具-->SQL Server Profiler),可以监测到上面代码在拼接SQL完成时，直接查询数据库

对应SQL：

SELECT 
    [Extent1].[id] AS [id], 
    [Extent1].[customerName] AS [customerName], 
    [Extent1].[customerDate] AS [customerDate]
    FROM [dbo].[CustomerInfo] AS [Extent1]

 >2：EF对于集合类型的导航属性会延迟加载

DbContext context = new MyFirstEFEntities();
//1：EF默认延迟加载，执行完下面的语句，数据库并没有SQL查询语句
var rows = context.Set<CustomerInfo>().Select(c => c);
//2:第一次查询数据库，查询CustomerInfo表
foreach (var row in rows)
{
    //foreach时  这个会执行多次  每次@EntityKeyValue1 等于 迭代到这次的 OrderInfoId
    Console.WriteLine(row.OrderInfoes.Count);
}

使用MS SQL Server Profiler(工具-->SQL Server Profiler),可以监测到执行到foreach时，执行CustomerInfo表格数据的查询(即为：EF查询默认会延迟加载)

此时对应SQL为：

SELECT 
    [Extent1].[id] AS [id], 
    [Extent1].[customerName] AS [customerName], 
    [Extent1].[customerDate] AS [customerDate]
    FROM [dbo].[CustomerInfo] AS [Extent1]

foreach循环时  这个会执行多次  每次@EntityKeyValue1 等于 迭代到这次的 OrderInfoId，对应SQL为：

exec sp_executesql N'SELECT 
    [Extent1].[id] AS [id], 
    [Extent1].[orderName] AS [orderName], 
    [Extent1].[customerId] AS [customerId]
    FROM [dbo].[OrderInfo] AS [Extent1]
    WHERE [Extent1].[customerId] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1

用MS SQL Server Profiler(工具-->SQL Server Profiler),可以监测到执行到foreach时 执行了4次数据库查询(rows有4条CustomerInfo数据)

//也就是说 我们有多少条OrderInfo 就要执行多少次上面的查询SQL   当然 这里使用的是exec sp_executesql   利用sp_executesql，能够重用执行计划，这就大大提供了执行性能

 >EF导航属性的非延迟加载: Include("")方法

DbContext context = new MyFirstEFEntities();
//1：EF默认延迟加载，执行完下面的语句，数据库并没有SQL查询语句
var rows = context.Set<CustomerInfo>().Include(c=>c.OrderInfoes).Select(c => c);
//2:第一次查询数据库，查询CustomerInfo表,以及CustomerInfo对应的所有OrderInfo数据
foreach (var row in rows)
{
    //foreach时，不会执行数据库查询操作
    Console.WriteLine(row.OrderInfoes.Count);
}

使用MS SQL Server Profiler(工具-->SQL Server Profiler),可以监测到执行foreach时，执行CustomerInfo表格数据的查询(EF查询默认会延迟加载)，以及CustomerInfo对应的所有OrderInfo数据(EF导航属性的非延迟加载)

对应SQL为：

SELECT 
    [Project1].[id] AS [id], 
    [Project1].[customerName] AS [customerName], 
    [Project1].[customerDate] AS [customerDate], 
    [Project1].[C1] AS [C1], 
    [Project1].[id1] AS [id1], 
    [Project1].[orderName] AS [orderName], 
    [Project1].[customerId] AS [customerId]
    FROM ( SELECT 
        [Extent1].[id] AS [id], 
        [Extent1].[customerName] AS [customerName], 
        [Extent1].[customerDate] AS [customerDate], 
        [Extent2].[id] AS [id1], 
        [Extent2].[orderName] AS [orderName], 
        [Extent2].[customerId] AS [customerId], 
        CASE WHEN ([Extent2].[id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM  [dbo].[CustomerInfo] AS [Extent1]
        LEFT OUTER JOIN [dbo].[OrderInfo] AS [Extent2] ON [Extent1].[id] = [Extent2].[customerId]
    )  AS [Project1]
    ORDER BY [Project1].[id] ASC, [Project1].[C1] ASC

关闭延迟加载的方式：

1.去掉属性里的virtual

2.context.Configuration.LazyLoadingEnabled = false;