2

How can I call a stored procedure using Entity Framework Core 2.0? Asp.net core 2.0 site in C#

ilMattion
  • 1,841
  • 2
  • 24
  • 47
Jason Webber
  • 323
  • 1
  • 4
  • 14
  • What have you tried? For example have you tried googling this? Because you'll get a lot of results. Please try something and come back with a specific question. – Nick.Mc Nov 13 '17 at 05:02
  • I have googled and tried several option but most of the google stuff refers to entity core 1.0 and not 2.0 With members of the data context not there for it. – Jason Webber Nov 13 '17 at 05:03
  • 1
    Can you post the code you tried and post the error message you get from it – Nick.Mc Nov 13 '17 at 05:05
  • I am not getting an error as I cant find a syntax for calls off the db context to even try that will combine with the properties available. – Jason Webber Nov 13 '17 at 05:09
  • 1
    hmmm.. does this link help? https://stackoverflow.com/questions/28599404/how-to-run-stored-procedures-in-entity-framework-core – Nick.Mc Nov 13 '17 at 05:14
  • Thanks. It did. Was missing the namespace reference to Microsoft.EntityFrameworkCore in the class. Once there all the functions i expected to see. – Jason Webber Nov 13 '17 at 15:31

1 Answers1

2

csproj file:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp2.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="2.0.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.0.0" />
  </ItemGroup>

</Project>

program.cs:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using Microsoft.EntityFrameworkCore;

namespace EfExperiment
{
    class Program
    {
        public class Record
        {
            public int Id { get; set; }
            public DateTime Date { get; set; }
            public decimal Value { get; set; }
        }

        public class RecordsDbContext : DbContext
        {
            public DbSet<Record> Records { get; set; }

            public List<Record> RecordsByDateRange(DateTime fromDate, DateTime toDate)
            {
                var fromDateParam = new SqlParameter("fromDate", fromDate);
                var toDateParam = new SqlParameter("toDate", toDate);

                return Records
                    .FromSql("EXECUTE dbo.RecordsByDateRange @fromDate, @toDate", fromDateParam, toDateParam)
                    .ToList();
            }

            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                optionsBuilder.UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=RecordsDb;Trusted_Connection=True;");
            }
        }

        static void Main(string[] args)
        {
            using (var dbc = new RecordsDbContext())
            {
                var records = dbc.RecordsByDateRange(DateTime.Now.AddMonths(-1), DateTime.Now);
                Console.WriteLine($"{records.Count} records loaded");

                foreach (var record in records)
                {
                    Console.WriteLine($"[{record.Id}] [{record.Date}] {record.Value}");
                }
            }

            Console.WriteLine("Press <enter>");
            Console.ReadLine();
        }
    }
}
Антон Красильщиков
  • 21
  • 1
  • 4