英文:
How to make a mapping from c# method to a SQL function
问题
Goal: 从数据库函数 TestBool
中获取值。
Problem: 我没有足够的信息来了解如何使 DbFunction TestBool (C# 代码) 正常工作。
我缺少代码的哪部分。
我需要一个简单而教育性的解释,来实现这个目标。
谢谢!
英文:
Goal: get value from db function TestBool
.
Problem: I don't get enough information about how to make the DbFunction TestBool (C# code) to be working.
What part of the code am I missing.
I need a simple and pedagogical explanation about how to achieve the goal.
Thank you!
SQL:
CREATE TABLE [dbo].[TTest2]
(
[id] [int] IDENTITY(1,1) NOT NULL,
[isbn] [varchar](50) NOT NULL,
[date] [datetime] NOT NULL,
[score] [int] NOT NULL,
PRIMARY KEY CLUSTERED
([id] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TTest1]
(
[id] [int] IDENTITY(1,1) NOT NULL,
[isbn] [varchar](50) NOT NULL,
[date] [datetime] NOT NULL,
[score] [int] NOT NULL,
PRIMARY KEY CLUSTERED
([id] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TTest3]
(
[TTest3Id] [int] IDENTITY(1,1) NOT NULL,
[isbn] [varchar](50) NOT NULL,
[date] [datetime] NOT NULL,
[score] [int] NOT NULL,
PRIMARY KEY CLUSTERED
([TTest3Id] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE FUNCTION TestBool
(@test BIT)
RETURNS BIT
AS
BEGIN
RETURN @test
END;
C# code:
using System;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
namespace CreateFunctionApp.Entities
{
public partial class JanaruContext : DbContext
{
public JanaruContext()
{
}
public JanaruContext(DbContextOptions<JanaruContext> options)
: base(options)
{
}
public virtual DbSet<Ttest1> Ttest1s { get; set; } = null!;
public virtual DbSet<Ttest2> Ttest2s { get; set; } = null!;
public virtual DbSet<Ttest3> Ttest3s { get; set; } = null!;
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Ttest1>(entity =>
{
entity.ToTable("TTest1");
entity.Property(e => e.Id).HasColumnName("id");
entity.Property(e => e.Date)
.HasColumnType("datetime")
.HasColumnName("date");
entity.Property(e => e.Isbn)
.HasMaxLength(50)
.IsUnicode(false)
.HasColumnName("isbn");
entity.Property(e => e.Score).HasColumnName("score");
});
modelBuilder.Entity<Ttest2>(entity =>
{
entity.ToTable("TTest2");
entity.Property(e => e.Id).HasColumnName("id");
entity.Property(e => e.Date)
.HasColumnType("datetime")
.HasColumnName("date");
entity.Property(e => e.Isbn)
.HasMaxLength(50)
.IsUnicode(false)
.HasColumnName("isbn");
entity.Property(e => e.Score).HasColumnName("score");
});
modelBuilder.Entity<Ttest3>(entity =>
{
entity.ToTable("TTest3");
entity.Property(e => e.Ttest3Id).HasColumnName("TTest3Id");
entity.Property(e => e.Date)
.HasColumnType("datetime")
.HasColumnName("date");
entity.Property(e => e.Isbn)
.HasMaxLength(50)
.IsUnicode(false)
.HasColumnName("isbn");
entity.Property(e => e.Score).HasColumnName("score");
});
OnModelCreatingPartial(modelBuilder);
}
[DbFunction]
public static int TestBool(bool test)
{
throw new NotImplementedException();
}
partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}
}
---------
using CreateFunctionApp.Entities;
using Microsoft.AspNetCore.Mvc;
namespace CreateFunctionApp.Controllers
{
[ApiController]
[Route("[controller]")]
public class WeatherForecastController : ControllerBase
{
public WeatherForecastController(ILogger<WeatherForecastController> logger, JanaruContext db)
{
this._logger = logger;
this._db = db;
}
private readonly ILogger<WeatherForecastController> _logger;
private JanaruContext _db;
[HttpGet(Name = "GetWeatherForecast")]
public string Get()
{
var ttest1s = _db.Ttest1s.ToList();
var ttest2s = _db.Ttest2s.ToList();
var ttest3s = _db.Ttest3s.ToList();
var dddf = JanaruContext.TestBool(true);
return "Test";
}
}
}
答案1
得分: 1
The EF 用户定义函数映射 旨在用于 LINQ 查询,如
from t in db.Test1 where db.TestBool(t.Status)==true
如果你想直接从 .NET 代码调用数据库函数,你需要实现函数体以调用 UDF,就像这样:
[DbFunction]
public bool TestBool(bool test)
{
var pTest = new SqlParameter("@test", System.Data.SqlDbType.Bit);
pTest.Value = test;
var pRv = new SqlParameter("@rv", System.Data.SqlDbType.Bit);
pRv.Direction = System.Data.ParameterDirection.Output;
this.Database.ExecuteSqlRaw("set @rv = dbo.TestBool(@test)", pRv, pTest);
return (bool)pTest.Value;
}
英文:
The EF User-Defined Function Mapping is intended to be used in LINQ queries like
from t in db.Test1 where db.TestBool(t.Status)==true
If you want to call the database function directly from .NET code you have to implement the function body to call the UDF like this:
[DbFunction]
public bool TestBool(bool test)
{
var pTest = new SqlParameter("@test", System.Data.SqlDbType.Bit);
pTest.Value = test;
var pRv = new SqlParameter("@rv", System.Data.SqlDbType.Bit);
pRv.Direction = System.Data.ParameterDirection.Output;
this.Database.ExecuteSqlRaw("set @rv = dbo.TestBool(@test)", pRv, pTest);
return (bool)pTest.Value;
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论