How do I replace Database.ExecuteSqlQuery when changing TargetFramework away from.NetCore2.1

I have the following which was working fine in .NetCore2.1 with SDKs

Microsoft.AspNetCore.App(2.1.1) 
Microsoft.NetCore.App(2.1.0)

My code is

public static int TransitTime(string postcode, ApiDbContext con)
{

    var query = "SELECT top 1 Mins from Transit where postcode = @Postcode order by mins desc;";
    var p1 = new SqlParameter("@Postcode",postcode);
    var result = 0;
    using (var dr = con.Database.ExecuteSqlQuery(query,p1))
    {
        var reader = dr.DbDataReader;
        while (reader.Read()) result = (int)reader[0];
    }
   return Convert.ToInt32(result);
}

Hovering over the word Database I could see it was in

Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade

DatabaseFacade

I don't see a specific reference to

Microsoft.EntityFrameworkCore.Infrastructure 

It is documented as being part of Entity Framework Core 2.1

inside either SDK so I wonder how it is referenced.

However I needed to add a reference to a Framework 4.7.2 dll

So I switched to the following project file

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Library</OutputType>
    <TargetFramework>netstandard2.0</TargetFramework>
  <ApplicationIcon />
    <StartupObject />
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="2.2.6" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="2.2.6" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="2.2.6" />
    <PackageReference Include="Microsoft.Extensions.Configuration.Json" Version="2.2.0" />
  </ItemGroup>
  <ItemGroup>
    <ProjectReference Include="..\Common\SBD.Common.csproj" />
  </ItemGroup>
</Project>

Now I get an error

CS1061 'DatabaseFacade' does not contain a definition for 'ExecuteSqlQuery'

I tried looking form Microsoft.EntityFrameworkCore.Infrastructure in Nuget Manage Packages for Solution but it does not show.

Looking at this question I decided to try using .FromSQl instead.

I found help in the docs but it does not explain how return non entity types. How do I do that?

1 answer

  • answered 2019-08-24 01:30 Kirsten Greed

    After reading the end of this link I am trying

    public static int TransitTime(string postcode, ApiDbContext con)
    {
        var query = "SELECT top 1 Mins from Transit where postcode = @Postcode order by mins desc;";
        var p1 = new SqlParameter("@Postcode", postcode);
        var result = 0;
        using (var command = con.Database.GetDbConnection().CreateCommand())
        {
            command.CommandText = query;
            command.Parameters.Add(p1);
            con.Database.OpenConnection();
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read()) result = (int)reader[0];
            }
        }
        return Convert.ToInt32(result);
    }
    

    I see that this is using an extension method in Microsoft.EntityFrameworkCore.Infrastructure

    Extension method