How to correctly call Postgres Function from entity framework core?

Question

I have a function i need to call from Postgres, This is what i have so far:

var s1 = new Npgsql.NpgsqlParameter("_date", NpgsqlDbType.Date)
{
  Direction = System.Data.ParameterDirection.Input,
  Value = DateTime.Now.Date
};

var command = await _context.AggregateBalances.FromSqlRaw($"SELECT * from get_aggregate_balance(@s1)", s1).ToListAsync();

When i try this i am getting this exception:

This is the output from pgAdmin:

To Map this result I created this model/dbset:

public class SPGetAggregateBalance
{
   public int Id { get; set; }
   public decimal Balance { get; set; }
}

public DbSet<SPGetAggregateBalance> AggregateBalances { get; set; }

Why "s1" is a column? and why it does not exist?

Please post text instead of images where possible, eg your exception stack trace. It looks like full table value support will be completed in EF Core 5, including db functions that return IQueryable. eg github.com/dotnet/efcore/issues/22829 — Jeremy Lakeman
– Jeremy Lakeman, Commented Nov 9, 2020 at 0:09

Serge · Accepted Answer · 2020-11-08 23:19:41Z

4

try to change your code using

var s1=DateTime.Now.ToString("yyyy/MM/dd");

var command = await _context.SPGetAggregateBalances.FromSqlInterpolated($"SELECT * FROM  get_aggregate_balance({s1})")

Add add to you EF context :

  modelBuilder.Entity<SPGetAggregateBalance>(e =>
            {
                e.HasNoKey();
            });

edited Nov 8, 2020 at 23:19

answered Nov 8, 2020 at 23:00

Serge

44.4k4 gold badges26 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to correctly call Postgres Function from entity framework core?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related