EF Core 5.0原生sql语句执行

最新推荐文章于 2024-09-02 17:52:55 发布

王焜棟琦

最新推荐文章于 2024-09-02 17:52:55 发布

阅读量3k

点赞数

分类专栏： EF Core 文章标签： EF Core 5.0

本文链接：https://blog.csdn.net/u011511086/article/details/115198693

版权

EF Core 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

微软官网文档：
https://docs.microsoft.com/zh-cn/ef/core/querying/raw-sql

原生sql执行

    public async Task<(int, int, int)> GetImgViewCount()
    {
            const string sql = @" select
  --图片总数（可查看）
   (select count(0) from[img_info] where [i_status] = 1) as img_view_count
   --领导图片数（可查看）
   ,(select count(0) from[img_info] where [i_status] = 1 and [exist_leader] = 1) as leader_view_count
   --视频数（可查看）
   ,(select count(0) from[video] where [video_status] = 1) as video_view_count";

            (int, int, int) result = (0, 0, 0);
            MyDbContext myDbContext = null;
            DbConnection connection = null;
            try
            {
                myDbContext = new MyDbContext();
                connection = myDbContext.Database.GetDbConnection();
                await connection.OpenAsync();
                DbCommand dbCommand = connection.CreateCommand();
                dbCommand.CommandText = sql;
                DbDataReader reader = await dbCommand.ExecuteReaderAsync();
                while (await reader.ReadAsync())
                {
                    //图片总数（可查看）
                    result.Item1 = Convert.ToInt32(reader["img_view_count"]);
                    //领导图片数（可查看）
                    result.Item2 = Convert.ToInt32(reader["leader_view_count"]);
                    //视频数（可查看）
                    result.Item3 = Convert.ToInt32(reader["video_view_count"]);
                    break;
                }
                await reader.CloseAsync();
                await reader.DisposeAsync();
                await dbCommand.DisposeAsync();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (connection != null)
                {
                    await connection.CloseAsync();
                    await connection.DisposeAsync();
                }
                if (myDbContext != null)
                {
                    await myDbContext.DisposeAsync();
                }
            }
            return result;
    }

MyDbContext

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Text;
using WebNetCore5_Img_Storage.Model;
using WebNetCore5_Img_Storage.Model.Tool;
using Microsoft.Extensions.Logging;
using Microsoft.EntityFrameworkCore.Diagnostics;

namespace WebNetCore5_Img_Storage.DAL
{
    public class MyDbContext : DbContext
    {
        //public MyDbContext()
        //{
        //    //设置执行超时限制
        //    //this.Database.SetCommandTimeout(60);
        //}

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            //连接字符串配置
            string connectionString = MyConfigReader.GetConfigValue("connctionDb");
            optionsBuilder.UseSqlServer(connectionString);

            //设置不跟踪所有查询  
            optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);

            //启用敏感数据日志记录
            optionsBuilder.EnableSensitiveDataLogging();

#if DEBUG
            //记录日志          
            optionsBuilder.LogTo(msg =>
            {
                //调试-窗口消息
                System.Diagnostics.Debug.WriteLine(msg);
                //输出-窗口消息
                Console.WriteLine(msg);
            });
#endif

        }

 
        public virtual DbSet<Img_info> Img_info { get; set; }         
        public virtual DbSet<Video> Video { get; set; }
        
    }
}