/*
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
标签：donet,set,get,System,mapping,using,Entity,public,view From： https://www.cnblogs.com/geovindu/p/17099009.html