Entity Framework - execute function without import not populating entity fields

Entity Framework - execute function without import not populating entity fields

Postby samtech » Mon 10 Mar 2014 11:51

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: "noone@nowhere.com"
    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/22284620/devart-dotconnect-for-postgresql-entity-framework-execute-function-without-impor but didn't receive any answer there.

Can someone help?
samtech
 
Posts: 2
Joined: Mon 10 Mar 2014 11:44

Re: Entity Framework - execute function without import not populating entity fields

Postby MariiaI » Thu 13 Mar 2014 07:48

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.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Entity Framework - execute function without import not populating entity fields

Postby samtech » Thu 13 Mar 2014 09:30

How i missed to test that small thing :D

Thanks a lot, it is working well.
samtech
 
Posts: 2
Joined: Mon 10 Mar 2014 11:44

Re: Entity Framework - execute function without import not populating entity fields

Postby MariiaI » Thu 13 Mar 2014 10:56

Glad to see that the issue was resolved. If you have any further questions, feel free to contact us.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to dotConnect for PostgreSQL