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:

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

　　

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>



    }
}

　　

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);



        }
    }
}

　调用：

 

            List<ViewEmployeeMasters> views=ctx.Database.SqlQuery<ViewEmployeeMasters>("select * from dbo.ViewEmployeeMasters").ToList();
       
            foreach (ViewEmployeeMasters view in views)
                {
                    Console.WriteLine(view.Name);
                }

　　

输出

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