Connecting SQL Server database with time-triggered Azure Function using DBContext

Question

I am very new to Azure Functions and I am facing problems in connecting it with the SQL Server database. I am using DbContext to do so.

Here is my code:

DbContext (EntityContext.cs):

public EntityContext(DbContextOptions<EntityContext> options) : base(options) { }
public DbSet<User> Users{ get; set; }

public class User
{
        public long UserId{ get; set; }
        public DateTime CreatedOn{ get; set; }
        public long CreatedBy{ get; set; }
        public DateTime ModifiedOn { get; set; }
        public long ModifiedBy { get; set; }
        public string EmailId { get; set; }
        public long PhoneNumber{ get; set; }
}

IUserRepository.cs:

public interface IUserRepository
{
    IEnumerable<User> GetUsersData();
    User UpdateUser(User userList);
}

UserRepository.cs:

public class UserRepository: IUserRepository
{
        private readonly EntityContext context;

        public UserRepository(EntityContext context)
        {
            this.context = context;
        }

        public IEnumerable<User> GetUsersData()
        {
            var s = context.Users.Where(x => x.UserId == 123).ToList();
            return s;
        }

        public User UpdateUser(User userList)
        {
            var users = context.Users.Attach(userList);
            users.State = Microsoft.EntityFrameworkCore.EntityState.Modified;
            context.SaveChanges();
            return userList;
        }
}

Startup.cs:

public class Startup
{
        private IConfiguration Configuration;

        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext<EntityContext>(options =>
            options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
            services.AddScoped<IUserRepository, UserRepository>();
        }
}

local.settings.json:

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "UseDevelopmentStorage=true",
    "FUNCTIONS_WORKER_RUNTIME": "dotnet"
  },
  "ConnectionStrings": {
    "DefaultConnection": "Server= ;Initial Catalog=;User ID=;Password= ;MultipleActiveResultSets= True;Persist Security Info=True;"
  }
}

Function1.cs:

private readonly IUserRepository _irepo;

public Function1(IUserRepository irepo)
{
  _irepo = irepo;
}

[FunctionName("Function1")]
public void Run([TimerTrigger("0 15 18 * * *")]TimerInfo myTimer, ILogger log)
{
    // I have set the cron expression to 6.15 pm for the testing purpose only
    _irepo.GetUsersData();            
  
    log.LogInformation($"C# Timer trigger function executed at: {DateTime.Now}");
}

Here is the list of packages I am using:

Packages

I have also consulted this but it didn't help me out.

Also I am getting an error

Microsoft.Extensions.DependencyInjection.Abstractions: Unable to resolve service for type 'MyProject.Models.Repository.IUserRepository' while attempting to activate 'MyProject.Function1'.

Somewhere I also learnt to use the package manager console to add the migrations, but I am not sure if it helps or not and I am getting the error as:

EntityFramework6\Add-Migration initial

No migrations configuration type was found in the assembly 'MyProject'. (In Visual Studio you can use the Enable-Migrations command from Package Manager Console to add a migrations configuration).

EntityFrameworkCore\Add-Migration initial

Unable to create an object of type 'EntityContext'. For the different patterns supported at design time, see https://go.microsoft.com/fwlink/?linkid=851728

I'm not sure what I'm doing wrong. Please suggest.

Answer 1

Please refer the link of the official document of Microsoft for Azure Functions.

In your case change Startup class as:

[assembly: FunctionsStartup(typeof(MyProject.Startup))]
  namespace MyProject
  {
    public class Startup : FunctionsStartup
      {
        public override void Configure(IFunctionsHostBuilder builder)
          {
            builder.Services.AddDbContext<EntityContext>(options=> 
            options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
            builder.Services.AddScoped<IUserRepository, UserRepository>();       
          }
      }
  }

Also in the DbContext class (EntityContext.cs):

public DbSet<User> User{ get; set; }
//Removed s from Users
//Also remove s from Users in the file `UserRepository.cs`

And finally in local.settings.json as:

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "UseDevelopmentStorage=true",
    "FUNCTIONS_WORKER_RUNTIME": "dotnet",
    "DefaultConnection": "Data Source= ;Initial Catalog=;User ID=;Password= ;MultipleActiveResultSets= True;Persist Security Info=True;"
  }
}