Reading multiple values from a Postgres database using npgsql and EF Core in an ASP.NET Core MVC view:

var tulem = await ctx.Database
                     .SqlQuery<(string Klient, int Krediit, int Vaba)>($"select 'test',1,2")
                     .SingleAsync();

Throws an exception

Npgsql.PostgresException: column s.Value does not exist

How to get those values from the database?

Using ASP.NET Core 9 MVC, npgsql and EF Core

Reading multiple values from a Postgres database using npgsql and EF Core in an ASP.NET Core MVC view:

var tulem = await ctx.Database
                     .SqlQuery<(string Klient, int Krediit, int Vaba)>($"select 'test',1,2")
                     .SingleAsync();

Throws an exception

Npgsql.PostgresException: column s.Value does not exist

How to get those values from the database?

Using ASP.NET Core 9 MVC, npgsql and EF Core

• c#
• postgresql
• entity-framework-core
• asp-core-mvc
• npgsql

Share Improve this question Follow edited Mar 26 at 10:54 Michał Turczyn 37.8k1717 gold badges5454 silver badges8282 bronze badges asked Mar 25 at 17:32 AndrusAndrus 28k6767 gold badges214214 silver badges396396 bronze badges 3

• 1 You may be missing some code in your example because I do not see anywhere you are selecting Value or s.Value. The error though says you are trying to select s.Value and it does not exist in the table you are trying to select it from. – Brad Commented Mar 25 at 18:21
• 1 Could you show how your codes was translated into SQL?it seems that you create a .SqlQuery Extension method yourself – Ruikai Feng Commented Mar 26 at 6:40
• @brad Value is propery used in generated sql. It wraps command to select s.Value from (<my quey>) s – Andrus Commented Mar 27 at 6:56

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

I have tested your case and I got also the same error about Value column not existing.

This is because you have used the type parameter wrong and there is actually one more issue:

1. You should provide class that matches your select statement, i.e. properties can be mapped to the columns.
2. You have two integer values - other than order of properties/columns - how would you expect to reason about it/how to map it? From purely programmatical way - they are just two numbers and it's ambigous which one is which.

So i have troubleshoot the issue as follows:

1. Run the query as you did -> got the same error - I guess it is due to internal implementation of a Tuple and EF - EF might recognize some Value property on Tuple and try to find such column.
2. Create class

   public class TestResult
   {
       public string Client { get; set; }
       public int Credit { get; set; }
       public int Vaba { get; set; }
   }
   

   and used it

   _context.Database.SqlQuery<TestResult>($"select 'test', 42, 420").SingleAsync()
   

   which resulted in "Client column not found" error. Progress!
3. Added column names to correctly match my class definition:

   _context.Database.SqlQuery<TestResult>($"select 'test' \"Client\", 42 \"Credit\", 420 \"Vaba\"").SingleAsync()
   

   which worked perfectly.

Alternative approach with EnableRecordsAsTuples

As suggested by OP in comments, we can use EnableRecordsAsTuples method to handle that automatically, without need for new class:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseNpgsql(
        "connection string",
        options =>
        {
            options.ConfigureDataSource(builder =>
            {
                builder.EnableRecordsAsTuples();
            });
        });
    base.OnConfiguring(optionsBuilder);
}