CSharp: donet create database view mapping with EF 6

/*

function mapping
https://learn.microsoft.com/en-us/ef/core/querying/user-defined-function-mapping


view mapping

public DbQuery<View_BookDetails> View_BookDetails { get; set; }

modelBuilder.Query<View_BookDetails>().ToView("View_BookTable");

https://learn.microsoft.com/en-us/ef/ef6/fundamentals/performance/pre-generated-views

https://www.michalbialecki.com/en/2020/09/09/working-with-views-in-entity-framework-core-5/
https://khalidabuhakmeh.com/how-to-add-a-view-to-an-entity-framework-core-dbcontext
https://www.c-sharpcorner.com/blogs/create-a-database-view-using-entity-framework-ef-code-first-approach

Stored Procedures mapping
https://www.c-sharpcorner.com/UploadFile/ff2f08/code-first-stored-procedure-entity-framework-6-0/

index mapping

*/

sql:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].DepartmentMaster') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE DepartmentMaster
GO
create table DepartmentMaster
(
    DepartmentId INT IDENTITY(1,1) PRIMARY KEY,
    Code nvarchar(50),
    [Name] nvarchar(50)
)
go
 
insert into DepartmentMaster(Code,[Name]) values('0001','IT')
go
insert into DepartmentMaster(Code,[Name]) values('0002','HR')
go
 
select * from DepartmentMaster
go
 
 
 
select * from EmployeeMasters
go
 
create view ViewEmployeeMasters
as
select a.*,b.Name as 'DepartmentName',b.Code as 'DepartmentCode' from EmployeeMasters as a,DepartmentMaster as b
where a.DepartmentId=b.DepartmentId
go

　　

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
namespace EntityFramework6.Entity
{
    /// <summary>
    ///
    /// </summary>
 
    public class DepartmentMaster
    {
 
        /// <summary>
        ///
        /// </summary>
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int DepartmentId { get; set; }
        /// <summary>
        ///
        /// </summary>
        public string Code { get; set; }
 
        /// <summary>
        ///
        /// </summary>
        public string Name { get; set; }
        /// <summary>
        ///
        /// </summary>
        public List<EmployeeMaster> Employees { get; set; }
    }
}
 
 
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
namespace EntityFramework6.Entity
{
 
 
    /// <summary>
    /// geovindu,Geovin Du
    /// </summary>
    public class EmployeeMaster
    {
 
        /// <summary>
        ///
        /// </summary>
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int EmployeeId { get; set; }
 
        /// <summary>
        ///
        /// </summary>
        [StringLength(50)]
        public string Code { get; set; }
        /// <summary>
        ///
        /// </summary>
        [StringLength(50)]
        public string Name { get; set; }
 
        /// <summary>
        ///
        /// </summary>
         
        public int DepartmentId { get; set; }
 
        /// <summary>
        ///
        /// </summary>
        public virtual DepartmentMaster Department { get; set; }
 
    }
}
 
 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.ComponentModel.DataAnnotations.Schema;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.ModelConfiguration;
 
 
namespace EntityFramework6.Entity
{
 
    /// <summary>
    /// 视图
    /// Create A Database View Using The Entity Framework (EF) Code First Approach
    /// </summary>
    public class ViewEmployeeMasters
    {
 
        public int EmployeeId { get; set; }
 
        /// <summary>
        ///
        /// </summary>
  
        public string Code { get; set; }
        /// <summary>
        ///
        /// </summary>
        
        public string Name { get; set; }
 
        /// <summary>
        ///
        /// </summary>
 
        public int DepartmentId { get; set; }
 
        /// <summary>
        ///
        /// </summary>
        public string DepartmentCode { get; set; }
 
        /// <summary>
        ///
        /// </summary>
        public string DepartmentName { get; set; }
        /// <summary>
 
 
 
    }
}

　　

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
using EntityFramework6.Enity;
using EntityFramework6.Entity;
using System;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.ModelConfiguration;
using System.Linq;
using System.Reflection;
 
namespace EntityFramework6
{
 
    /// <summary>
    ///
    /// </summary>
    [DbConfigurationType(typeof(EFConfiguration.EFConfiguration))]
    public class EfDbContext : DbContext
    {
 
        /// <summary>
        ///
        /// </summary>
        public EfDbContext() : base("name=ConnectionString")
        {
        }
 
        /// <summary>
        ///
        /// </summary>
        /// <param name="con"></param>
        public EfDbContext(DbConnection con) : base(con, contextOwnsConnection: false)
        {}
        /// <summary>
        ///
        /// </summary>
        public DbSet<Student> Students { get; set; }
 
        /// <summary>
        ///
        /// </summary>
        public DbSet<Customer> Customers { get; set; }
 
        /// <summary>
        ///
        /// </summary>
        public DbSet<Order> Orders { get; set; }
 
        /// <summary>
        ///
        /// </summary>
        public DbSet<Category> Categories { get; set; }
        /// <summary>
        ///
        /// </summary>
        public DbSet<Product> Products { get; set; }
        /// <summary>
        ///
        /// </summary>
        public DbSet<BillingDetail> BillingDetails { get; set; }
 
        /// <summary>
        ///
        /// </summary>
        public DbSet<DepartmentMaster> Departments { get; set; }
        /// <summary>
        ///
        /// </summary>
        public DbSet<EmployeeMaster> Employees { get; set; }
 
        /// <summary>
        /// 视图
        /// </summary>
        public DbQuery<ViewEmployeeMasters> ViewEmployeeMasters { get; set; }
 
        /// <summary>
        /// 视图
        /// </summary>
        /// public DbQuery<View_BookDetails> View_BookDetails { get; set; }
 
        /// <summary>
        ///
        /// </summary>
        /// <param name="modelBuilder"></param>
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
 
            // modelBuilder.Entity<ViewEmployeeMasters>();
 
            //视图
            // modelBuilder.Query<View_BookDetails>().ToView("View_BookTable");
 
            //ef 6 视图
            modelBuilder.Ignore<ViewEmployeeMasters>();
 
 
            //ef core  视图
            // modelBuilder.Entity<DepartmentMaster>().ToView("EmployeeView", schema: "dbo");
 
 
 
            //存储过程
            // modelBuilder.Entity<EmployeeMaster>().MapToStoredProcedures();
            //modelBuilder.Entity<Customer>()
            //modelBuilder.Entity<DepartmentMaster>().MapToStoredProcedures();
 
 
            //modelBuilder.Entity<EmployeeMaster>()
            //    .MapToStoredProcedures(s => s.Insert(u => u.HasName("InsertEmployee", "dbo"))
            //                                .Update(u => u.HasName("UpdateEmployee", "dbo"))
            //                                .Delete(u => u.HasName("DeleteEmployee", "dbo"))
            //);
 
            //https://learn.microsoft.com/zh-cn/ef/ef6/modeling/code-first/fluent/cud-stored-procedures
            modelBuilder
      .Entity<EmployeeMaster>()
      .MapToStoredProcedures(s =>
        s.Update(u => u.HasName("UpdateEmployee")
                       .Parameter(b => b.EmployeeId, "EmployeeId")
                       .Parameter(b => b.Code, "Code")
                       .Parameter(b => b.Name, "Name")
                       .Parameter(b => b.DepartmentId, "DepartmentId"))
         .Delete(d => d.HasName("DeleteEmployee")
                       .Parameter(b => b.EmployeeId, "EmployeeId"))
         .Insert(i => i.HasName("InsertEmployee")
                        .Parameter(b => b.Code, "Code")
                       .Parameter(b => b.Name, "Name")
                       .Parameter(b => b.DepartmentId, "DepartmentId")));
 
 
 
            var typesToRegister = Assembly.GetExecutingAssembly().GetTypes()
                    .Where(type => !string.IsNullOrEmpty(type.Namespace))
                    .Where(type => type.BaseType != null && type.BaseType.IsGenericType
                         && type.BaseType.GetGenericTypeDefinition() == typeof(EntityTypeConfiguration<>));
            foreach (var type in typesToRegister)
            {
                dynamic configurationInstance = Activator.CreateInstance(type);
                modelBuilder.Configurations.Add(configurationInstance);
            }
 
 
            base.OnModelCreating(modelBuilder);
 
 
 
        }
    }
}

　调用：

 

?
1
2
3
4
5
6
List<ViewEmployeeMasters> views=ctx.Database.SqlQuery<ViewEmployeeMasters>("select * from dbo.ViewEmployeeMasters").ToList();
 
foreach (ViewEmployeeMasters view in views)
    {
        Console.WriteLine(view.Name);
    }

　　

输出

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
select * from dbo.ViewEmployeeMasters
 
 
-- Executing at 2023/2/7 16:41:03 +08:00
 
-- Completed in 176 ms with result: SqlDataReader
 
 
 
姓名：Jignesh Trivedi
姓名：涂聚文
姓名：Geovin Du 涂聚文
SELECT TOP (3)
    [Extent1].[Id] AS [Id],
    [Extent1].[Name] AS [Name],
    [Extent1].[Email] AS [Email],
    [Extent1].[CreatedTime] AS [CreatedTime],
    [Extent1].[ModifiedTime] AS [ModifiedTime]
    FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent1].[Email] AS [Email], [Extent1].[CreatedTime] AS [CreatedTime], [Extent1].[ModifiedTime] AS [ModifiedTime], row_number() OVER (ORDER BY [Extent1].[Id] ASC) AS [row_number]
        FROM [dbo].[Customers] AS [Extent1]
    )  AS [Extent1]
    WHERE [Extent1].[row_number] > 4
    ORDER BY [Extent1].[Id] ASC

　　

 

posted @   ®Geovin Du Dream Park™  阅读(26)  评论(0)  编辑  收藏  举报
相关博文：
·  CSharp: donet 7 create database view mapping with EF core 7.02
·  CSharp: donet Stored procedure mapping with Entity Framework 6
·  第1章 Entity Framework Core 简介
·  MyBatis笔记
·  【EF Core】EF Core 6.0 连接Sqlite3
阅读排行：
· 25岁的心里话
· 闲置电脑爆改个人服务器（超详细） #公网映射 #Vmware虚拟网络编辑器
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· 零经验选手，Compose 一天开发一款小游戏！
· 一起来玩mcp_server_sqlite，让AI帮你做增删改查！！
历史上的今天：
2015-02-07 csharp:search and Compare string
2009-02-07 C#2.0 print winform 打印窗体数据试试
< 2025年3月 >
日 一 二 三 四 五 六
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5
点击右上角即可分享
微信分享提示