Entity Framework - execute function without import not populating entity fields

[samtech]

I have following POCO class against database table

Code: Select all

public class Contact
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public bool Checked { get; set; }
    public string Mobile { get; set; }
    public string Email { get; set; }
}

A function in PostgreSQL database

Code: Select all

CREATE OR REPLACE FUNCTION test_proc_pg()
  RETURNS TABLE(id integer, name character varying, Checked boolean, Mobile character varying, Email character varying) AS
$BODY$
BEGIN
    RETURN QUERY select temp1.id, temp1.Name, temp1.Checked, temp1.Mobile, temp1.Email from temp1;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

Defined a generic function to call PostgreSQL function and get back data

Code: Select all

public static List<T> ExecuteProc<T>(string procname, params PgSqlParameter[] param)
{
    List<T> list;
    string paranames = string.Empty;
    foreach (PgSqlParameter p in param)
    {
    if (paranames != string.Empty)
        paranames += ", ";
    paranames = paranames + "?";
    }

    using (var context  = new EntityContext())
    {
    list = context.Database.SqlQuery<T>("select " + procname + "(" + paranames + ")", param).ToList<T>();
    }
    return list;
}

Calling above function in Controller

Code: Select all

public ActionResult ProcTest()
{
    List<Contact> contacts = DAL.ExecuteProc<Contact>("test_proc_pg");
    return View(contacts);
}

Function is executing and returning List<Contact> but all the fields in class are null.

Code: Select all

{TestPG.Models.Contact}
    Checked: false
    Email: null
    Id: 0
    Mobile: null
    Name: null

However if i use SQL Server/SqlClient and call proc similarly it is populating all fields. Here is similar function written for SQL Server

Code: Select all

public static List<T> ExecuteProc<T>(string procname, params SqlParameter[] param)
    {
        List<T> list;
        string paranames = string.Empty;
        foreach (SqlParameter p in param)
        {
            if (paranames != string.Empty)
                paranames += ", ";
            paranames = paranames + "@" + p.ParameterName;
        }

        using (var context = new SSContext())
        {
            list = context.Database.SqlQuery<T>("exec " + procname + " " + paranames, param).ToList<T>();
        }
        return list;
    }

When i call it from controller it gives class with all fields populated

Code: Select all

public ActionResult ProcTest()
    {
        List<Contact> contacts = DAL.ExecuteProc<Contact>("test_proc_ss");
        return View(contacts);
    }

Here is result from SQL Server/SqlClient

Code: Select all

{TestSS.Models.Contact}
    Checked: false
    Email: "[email protected]"
    Id: 1
    Mobile: "1234567890"
    Name: "samtech"

What the wrong i am doing with dotConnect PostgreSQL?

I am using EF6 with latest trial version of dotConnect for PostgreSQL. I do not want to import functions.

I asked this question before at https://stackoverflow.com/questions/222 ... hout-impor but didn't receive any answer there.

Can someone help?

[MariiaI]

Please change this code

Code: Select all

list = context.Database.SqlQuery<T>("select " + procname + "(" + paranames + ")", param).ToList<T>();

with this:

Code: Select all

list = context.Database.SqlQuery<T>("select * from " + procname + "(" + paranames + ")", param).ToList<T>();

This is related to the peculiarity of working with the PostgreSQL stored procedures. In case of "select test_proc_ss" the PostgreSQL record is returned. It is necessary to disclose the query, so that all columns are returned and EF could materialize the corresponding objects.

Please tell us if this helps.

[samtech]

How i missed to test that small thing

Thanks a lot, it is working well.

[MariiaI]

Glad to see that the issue was resolved. If you have any further questions, feel free to contact us.