How to write sql to get data when using postgresql and EF?

Question

I have the below C# and Entity framework 6.4 code to get data from

postgresql function

return Database.SqlQuery<TEntity>(sql, parameters).ToArrayAsync();

and I tried

var sql = $@"select * from GetLogs(?,?,?,?,?,?,?,?,?,?,?,?,?)"; or 
var sql = $@"select * from GetLogs($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13)";

but got failed, anybody know about it?

I also try

var sql = "select * from GetLogs(TO_TIMESTAMP({0},'YYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP({1},'YYYY-MM-DD HH24:MI:SS'),{2},'{3}','{4}',{5},'{6}','{7}',{8},{9},{10})";
            var from = request.From.ToString("yyyy-MM-dd HH:mm:ss");
            var to = request.To.ToString("yyyy-MM-dd HH:mm:ss");
            return Database.SqlQuery<AdminLogMasterModel>(sql, 
                from,
                to,
                request.HttpStatusCode,
                request.ServerName,
                request.Page,
                request.TrackingId,
                request.Content,
                request.SortBy,
                request.OrderBy == SortOrder.Ascending ? 0 : 1,
                request.Page,
                request.Rows).ToArrayAsync();

This got no error but no date return. It seems postgresql didn't pass parameter in EF.

Does this answer your question? [Entity framework PostgreSQL](https://stackoverflow.com/questions/1211475/entity-framework-postgresql) — Vins, Feb 13 '20 at 07:48

score 0 · Accepted Answer · answered Feb 17 '20 at 08:42

I tried the below code can work

        var sql = "select * from GetLogs(TO_TIMESTAMP(@p0,'YYYY-MM-DD HH24:MI:SS'),TO_TIMESTAMP(@p1,'YYYY-MM-DD HH24:MI:SS'),@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10)";
        var para = new NpgsqlParameter[]
            {
                new NpgsqlParameter("@p0", request.From.ToString("yyyy-MM-dd HH:mm:ss")),
                new NpgsqlParameter("@p1", request.To.ToString("yyyy-MM-dd HH:mm:ss")),
                new NpgsqlParameter("@p2", CreateRequestValueifNull(request.HttpStatusCode)),
                new NpgsqlParameter("@p3", CreateRequestValueifNull(request.ServerName)),
                new NpgsqlParameter("@p4", CreateRequestValueifNull(request.Path)),
                new NpgsqlParameter("@p5", CreateRequestValueifNull(request.TrackingId)),
                new NpgsqlParameter("@p6", CreateRequestValueifNull(request.Content)),
                new NpgsqlParameter("@p7", request.SortBy),
                new NpgsqlParameter("@p8", request.OrderBy == SortOrder.Ascending ? 0 : 1),
                new NpgsqlParameter("@p9", request.Page),
                new NpgsqlParameter("@p10", request.Rows),
            };
        return Database.SqlQuery<LogModel>(sql, para).ToArrayAsync();

How to write sql to get data when using postgresql and EF?

1 Answers1