Problem running a function in Asp.net.core.webapi + postgresql + npgsql+Dapper

Asked

Viewed 77 times

2

I have a Webapi in Asp.net core, using postgresql as a database. Inside postgresql, I have the following function:

CREATE OR REPLACE FUNCTION evento_distancia(
IN lat numeric,
IN lng numeric,
IN dist double precision)
RETURNS TABLE(id bigint, nome character varying, descricao character 
varying, ImgUrl character varying, longitude numeric, latitude numeric, 
situacao boolean, DataHora timestamp with time zone, distancia double 
precision) AS
$BODY$

BEGIN
RETURN QUERY

select  e.id, e.nome, e.descricao, e.img_url as ImgUrl, e.longitude, 
e.latitude, e.situacao, e.data_hora as DataHora, (
              6371 * acos(
               cos(radians(lat)) * cos(radians(e.latitude))
               * cos(radians(e.longitude) - radians(lng)) +
               sin(radians(lat)) * sin(radians(e.latitude))
              )
              ) AS "distance" 
    from public.evento e
    where (not(e.latitude is null) and not (e.longitude is null) and (
              6371 * acos(
               cos(radians(lat)) * cos(radians(e.latitude))
               * cos(radians(e.longitude) - radians(lng)) +
               sin(radians(lat)) * sin(radians(e.latitude))
              )
              ) < dist) order by "distance" asc;

END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION public.evento_distancia(numeric, numeric, double precision)
OWNER TO postgres;

I have my event class as follows:

public class Evento : BaseEntity
{
    [Key]
    public int Id {get;set;}
    public int IdUsuario {get;set;}
    public string Nome {get;set;}
    public DateTime DataHora {get;set;}
    public int IdEndereco {get;set;}
    public double Longitude {get;set;}
    public double Latitude {get;set;}
    public string Descricao {get;set;}
    public string ImgUrl {get;set;}
    public Boolean Situacao {get;set;}
}

mount my sql, for example:

string sql = select * from evento_distancia(minha_latitude, minha_longitude, 
 minha_distancia)

I ask my list and receive the execution of my query, which would bring several items:

IEnumerable<Evento> eventos;
eventos = dbConnection.Query<Evento>(sql);

The following error appears from npgsql:

An exception of type 'Npgsql.PostgresException' occurred in System.Private.CoreLib.dll but was not handled in user code: 'External component has thrown an exception.'

Has anyone ever come across the error or has any idea what it is?

  • Can be a type and format problem (decimal point x decimal co, when passing values minha_latitude and minha_longitude, checks if the format of the C# and Postgre numbers are matching each other.

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.