将C#方法映射到SQL函数的方法。

huangapple go评论64阅读模式
英文:

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

huangapple
  • 本文由 发表于 2023年5月21日 23:52:19
  • 转载请务必保留本文链接:https://go.coder-hub.com/76300731.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定