时间戳

首页 > 解决方案 > 如何使用 Azure Functions（计时器触发器）和 azure sql 配置 Entity Famework Core？

entity-framework-core - 如何使用 Azure Functions（计时器触发器）和 azure sql 配置 Entity Famework Core？

问题描述

我实际上在 Azure Sql 中有一个数据库，我在另一个 Azure 帐户（与数据库不同）中有一个 Azure 函数（计时器触发器）我已经使用 Core Entity 框架来映射数据库但是当我想要进行查询时它没有工作，所以配置有问题。我尝试了很多并尝试了一些解决方案，但我仍然无法解决这个问题。我正在上传代码希望你能帮助我。提前致谢。

函数1.cs：

using System;
using System.Data.SqlClient;
using System.Threading.Tasks;
using System.Collections.Generic;
using System.Text;
using Microsoft.Azure.WebJobs;
using Microsoft.Extensions.Logging;
using Microsoft.Azure.Functions.Extensions;
using Microsoft.Net.Http;
using Microsoft.EntityFrameworkCore.SqlServer.Design;
using Microsoft.EntityFrameworkCore.SqlServer;
using Microsoft.EntityFrameworkCore;
using System.Linq;
using Newtonsoft.Json;





namespace azuretosharepoint
{

    public static class Function1
    {
        [FunctionName("Function1")]
        public static async Task RunAsync([TimerTrigger("0 */1 * * * *")] TimerInfo myTimer, ILogger log)
        {
            var azsh = new ProgettokpidatabaseContext();
            var listsharepoint = azsh.KpiTables.ToList();
            Console.WriteLine(listsharepoint);
                            
            
         }
    }
}

progettokpidatabaseContext.cs：

using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Design;
using System.Collections.Generic;
using System.Text;
using Newtonsoft.Json;

#nullable disable

namespace azuretosharepoint
{
    public partial class ProgettokpidatabaseContext : DbContext
    {
        public ProgettokpidatabaseContext()
        {
        }

        public ProgettokpidatabaseContext(DbContextOptions<ProgettokpidatabaseContext> options)
            : base(options)
        {
        }

        public virtual DbSet<KpiTable> KpiTables { get; set; }
        public virtual DbSet<KptKpiTarget> KptKpiTargets { get; set; }
        public virtual DbSet<KpvKpiValue> KpvKpiValues { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer(Environment.GetEnvironmentVariable("sqlconnectionstring"));
               //#warning To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.
                //optionsBuilder.UseSqlServer("Server=tcp:progetto-kpi.database.windows.net,1433;Initial Catalog=progetto-kpi-database;Persist Security Info=False;User ID=progetto-kpi;Password=**********;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;");
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.HasAnnotation("Relational:Collation", "SQL_Latin1_General_CP1_CI_AS");

            modelBuilder.Entity<KpiTable>(entity =>
            {
                entity.HasKey(e => e.KpiId);

                entity.ToTable("KPI_table");

                entity.Property(e => e.KpiId)
                    .ValueGeneratedNever()
                    .HasColumnName("KPI_ID");

                entity.Property(e => e.KpiBusinessArea)
                    .IsRequired()
                    .HasMaxLength(50)
                    .HasColumnName("KPI_Business_Area");

                entity.Property(e => e.KpiDescription)
                    .HasMaxLength(250)
                    .HasColumnName("KPI_Description");

                entity.Property(e => e.KpiDoi)
                    .HasColumnType("datetime")
                    .HasColumnName("KPI_DOI")
                    .HasDefaultValueSql("(getdate())");

                entity.Property(e => e.KpiDou)
                    .HasColumnType("datetime")
                    .HasColumnName("KPI_DOU")
                    .HasDefaultValueSql("(getdate())");

                entity.Property(e => e.KpiFrequency)
                    .HasMaxLength(10)
                    .HasColumnName("KPI_Frequency");

                entity.Property(e => e.KpiManual).HasColumnName("KPI_Manual");

                entity.Property(e => e.KpiName)
                    .IsRequired()
                    .HasMaxLength(50)
                    .HasColumnName("KPI_Name");
            });

            modelBuilder.Entity<KptKpiTarget>(entity =>
            {
                entity.HasKey(e => e.KptId)
                    .HasName("PK_KPT_table");

                entity.ToTable("KPT_KPI_Targets");

                entity.Property(e => e.KptId)
                    .ValueGeneratedNever()
                    .HasColumnName("KPT_ID");

                entity.Property(e => e.KptDate)
                    .HasColumnType("date")
                    .HasColumnName("KPT_Date");

                entity.Property(e => e.KptDoi)
                    .HasColumnType("datetime")
                    .HasColumnName("KPT_DOI")
                    .HasDefaultValueSql("(getdate())");

                entity.Property(e => e.KptDou)
                    .HasColumnType("datetime")
                    .HasColumnName("KPT_DOU")
                    .HasDefaultValueSql("(getdate())");

                entity.Property(e => e.KptKpiId).HasColumnName("KPT_KPI_ID");

                entity.Property(e => e.KptTarget)
                    .HasColumnType("decimal(18, 2)")
                    .HasColumnName("KPT_Target");

                entity.HasOne(d => d.KptKpi)
                    .WithMany(p => p.KptKpiTargets)
                    .HasForeignKey(d => d.KptKpiId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK__KPT_KPI_T__KPT_K__2FCF1A8A");
            });

            modelBuilder.Entity<KpvKpiValue>(entity =>
            {
                entity.HasKey(e => e.KpvId)
                    .HasName("PK_KPV_table");

                entity.ToTable("KPV_KPI_Values");

                entity.Property(e => e.KpvId)
                    .ValueGeneratedNever()
                    .HasColumnName("KPV_ID");

                entity.Property(e => e.KpvDate)
                    .HasColumnType("date")
                    .HasColumnName("KPV_Date");

                entity.Property(e => e.KpvDoi)
                    .HasColumnType("datetime")
                    .HasColumnName("KPV_DOI")
                    .HasDefaultValueSql("(getdate())");

                entity.Property(e => e.KpvDou)
                    .HasColumnType("datetime")
                    .HasColumnName("KPV_DOU")
                    .HasDefaultValueSql("(getdate())");

                entity.Property(e => e.KpvKpiId).HasColumnName("KPV_KPI_ID");

                entity.Property(e => e.KpvValue)
                    .HasColumnType("decimal(18, 2)")
                    .HasColumnName("KPV_Value");

                entity.HasOne(d => d.KpvKpi)
                    .WithMany(p => p.KpvKpiValues)
                    .HasForeignKey(d => d.KpvKpiId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK__KPV_KPI_V__KPV_K__3587F3E0");
            });

            OnModelCreatingPartial(modelBuilder);
        }

        partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
    }

}

local.settings.json ：

{
  "IsEncrypted": false,
  "AzureWebJobsStorage": "UseDevelopmentStorage=true",
  "FUNCTIONS_WORKER_RUNTIME": "dotnet",
  "Values": {
    "sqlconnectionstring": "Server=tcp:progetto-kpi.database.windows.net,1433;Initial Catalog=progetto-kpi-database;Persist Security Info=False;User ID=progetto-kpi;Password=*********;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;",
    "ProviderName": "System.Data.SqlClient"


  }
}

问题 ：

Azure Functions Core Tools
Core Tools Version:       3.0.3568 Commit hash: e30a0ede85fd498199c28ad699ab2548593f759b  (64-bit)
Function Runtime Version: 3.0.15828.0

Can't determine project language from files. Please use one of [--csharp, --javascript, --typescript, --java, --python, --powershell, --custom]
Missing value for AzureWebJobsStorage in local.settings.json. This is required for all triggers other than httptrigger, kafkatrigger, rabbitmqtrigger, orchestrationTrigger, activityTrigger, entityTrigger. You can run 'func azure functionapp fetch-app-settings <functionAppName>' or specify a connection string in local.settings.json.

azuretosharepoint.csproj：

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>netcoreapp3.1</TargetFramework>
    <AzureFunctionsVersion>v3</AzureFunctionsVersion>
    <UserSecretsId>4e8db286-9c45-4574-b556-eb0a04ac345e</UserSecretsId>
    <OutputType>Library</OutputType>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Dapper" Version="2.0.90" />
    <PackageReference Include="Dapper.Contrib" Version="2.0.78" />
    <PackageReference Include="Dapper.FluentMap" Version="2.0.0" />
    <PackageReference Include="Dapper.SimpleCRUD" Version="2.3.0" />
    <PackageReference Include="EntityFramework" Version="6.4.4" />
    <PackageReference Include="Microsoft.ApplicationInsights.WorkerService" Version="2.18.0-beta1" />
    <PackageReference Include="Microsoft.AspNetCore.AzureKeyVault.HostingStartup" Version="2.0.4" />
    <PackageReference Include="Microsoft.Azure.Functions.Extensions" Version="1.1.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="5.0.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="5.0.1">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="5.0.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="5.0.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer.Design" Version="2.0.0-preview1-final" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="5.0.1">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.Extensions.Configuration.UserSecrets" Version="3.1.10" />
    <PackageReference Include="Microsoft.NET.Sdk.Functions" Version="3.0.12" />
    <PackageReference Include="System.Configuration.ConfigurationManager" Version="6.0.0-preview.4.21253.7" />
    <PackageReference Include="System.Data.Common" Version="4.3.0" />
    <PackageReference Include="System.Data.SqlClient" Version="4.8.2" />
    <PackageReference Include="System.IO" Version="4.3.0" />
    <PackageReference Include="System.Linq" Version="4.3.0" />
    <PackageReference Include="System.Runtime" Version="4.3.1" />
  </ItemGroup>
  <ItemGroup>
    <None Update="host.json">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
    <None Update="local.settings.json">
      <CopyToOutputDirectory>Always</CopyToOutputDirectory>
      <CopyToPublishDirectory>Never</CopyToPublishDirectory>
    </None>
  </ItemGroup>
  <ItemGroup>
    <Service Include="{508349b6-6b84-4df5-91f0-309beebad82d}" />
  </ItemGroup>
</Project>

标签： entity-framework-coreazure-functionsazure-sql-database

解决方案

您的 local.settings.json 似乎格式不正确。尝试：

{
    "IsEncrypted": false,
    "Values": {
        "AzureWebJobsStorage": "UseDevelopmentStorage=true",
        "FUNCTIONS_WORKER_RUNTIME": "dotnet",
        "sqlconnectionstring": "YOURCONNECTIONSTRING",
        "ProviderName": "IDONTUSETHISVALUE-IN-MY-CONFIGS"
    }
}

此外，您的连接字符串已将密码设为空白。如果您从 Azure 门户的某些部分获取它，您将得到它。您可能是故意这样做的，但如果您没有这样做 - 请删除星号 ^

一种快速测试它是本地功能配置问题还是代码问题的方法：

  1. 打开新的 GitBash 提示符/其他终端
func init TestMe --dotnet
cd TestMe
func new --name TestEndpoint --template "HTTP trigger" --authlevel "anonymous"
func start

要创建测试计时器触发器：

func new --name Timer --template "TIMER trigger"

推荐阅读