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

Question

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

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?

FromSql can perform raw SQL queries only when the returned data is of the type being accessed and it also expects that the returned column names from the database match with the ones available in the mapping object. That's why you need to specifically define the dbset. — Dennis VW, Aug 24 '19 at 00:28
Thanks I renamed the question... I need to return types that are not in the database. — Kirsten, Aug 24 '19 at 00:28
Well, you are out of luck because EF Core does not support that yet. It's not about .NET Core, EF Core is completely different framework from EF6 you are upgrading from. See [Compare EF Core & EF6](https://learn.microsoft.com/en-us/ef/efcore-and-ef6/). — Ivan Stoev, Aug 24 '19 at 00:32
Net version doesn't matter. You are changing EF (Entity Framework) library. EF Core never had `ExecuteSqlQuery` method. And [Porting from EF6 to EF Core](https://learn.microsoft.com/en-us/ef/efcore-and-ef6/porting/) says: *"Because of the fundamental changes in EF Core we do not recommend attempting to move an EF6 application to EF Core unless you have a compelling reason to make the change. You should view the move from EF6 to EF Core as a port rather than an upgrade."*. — Ivan Stoev, Aug 24 '19 at 00:46
I was not using EF6. I updated the question to show the library for DatabaseFascade — Kirsten, Aug 24 '19 at 01:02
@Dennis1679 I dont need or want extra tables in the database — Kirsten, Aug 24 '19 at 01:16

score 0 · Answer 1 · answered Aug 24 '19 at 01:30

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

You'll find your answer in https://github.com/aspnet/EntityFramework/issues/1862#issuecomment-220787464 sirentek's posts. It describes how you create a database extension that lets you do what you want to do. — Dennis VW, Aug 24 '19 at 01:51

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

1 Answers1