Can PetaPoco populate an object using a stored procedure with a join clause?

Question

I have a stored procedure that does something similar to:

SELECT a.TaskId, b.CompanyCode FROM task a JOIN company b ON b.CompanyId = a.CompanyId;

I have an object called TaskItem that has the TaskId and CompanyCode properties, but when I execute the following (which I would have assumed worked):

        var masterDatabase = new Database("MasterConnectionString");
        var s = PetaPoco.Sql.Builder.Append("EXEC spGetTasks @@numberOfTasks = @0", numberOfTasks);
        var tasks = masterDatabase.Query<TaskItem>(s);

The problem is that the CompanyCode column does not exist in the task table, I did a trace and it seems that PetaPoco is trying to select all the properties from the task table and populating using the stored procedure.

Here is a definition of TaskItem:

public class TaskItem {
    public int TaskItemId { get; set; }
    public int CompanyId { get; set; }
    public string CompanyCode { get; set; }
    public int Status { get; set; }
}

How can I use PetaPoco to simply populate the list of task objects with the results of the stored procedure?

it would help if you showed definition of `TaskItem` – Dmitry Efimenko Jun 24 '13 at 23:12 — Dmitry Efimenko, Jun 24 '13 at 23:12
Sorry @Dmitry, just added – Mark Kadlec Jun 25 '13 at 19:31 — Mark Kadlec, Jun 25 '13 at 19:31

Dmitry Efimenko · Accepted Answer · 2013-06-25T20:29:06.390

1

If you only need two columns from the database, don't bother with POCOs. Use dynamic:

var d = masterDatabase.Query<dynamic>("Exec spGetTasks @@numberOfTasks = @0", numberOfTasks);
var result = d.Select(item => new Tuple<int, string>(item.TaskId, item.CompanyCode)).ToList();

otherwise the POCO that you are using to query database, i.e. TaskItem need to have a public property for each column name returned by the query.

UPDATE Now that you've posted definition of TaskItem I see that you have all the properties that you need on it. In theory it is enough to have a public property with the exact same name as the returned column for it to be populated. So in your case since query returns column CompanyCode, the appropriate property should be populated on the TaskItem. In case the property name differs, you can map it by decorating it with [Column] attribute

[Column("NameOfTheColumn")]
public string CompanyCode { get; set; }

If for some reason CompanyCode still remains not populated, the issue is with the query. Make sure that it returns appropriate results.

edited Jun 25 '13 at 20:29

answered Jun 24 '13 at 23:18

Dmitry Efimenko

10,973
7
62
79

Thanks @Dmitry, but what if the object started to get more complex, with say 5 or 10 properties? – Mark Kadlec Jun 25 '13 at 19:26
Thanks @Dmitry, but how am I retrieving and populating the data? If I use the masterDatabase.Fetch, it complains that the Task table doesn't contain CompanyCode. If I use your dynamic way, I'm stuck populating each field individually (at that point I don't see the advantage of using the ORM) – Mark Kadlec Jun 26 '13 at 15:28
I don't know why it would complain about CompanyCode. It's clearly is returned by your query. Try using `.Query` – Dmitry Efimenko Jun 26 '13 at 17:04
Thanks @Dmitry, the problem is that it assumes the column belongs to the task table, but I fixed this by creating a Company object and doing a fetch on . Thanks for your help! – Mark Kadlec Jun 26 '13 at 20:10

score 1 · Answer 2 · answered Jun 25 '13 at 14:06

1

Besides using dynamic as @Dmitry points, you can add the property to the TaskItem POCO, and decorate it with ResultColumn

    [ResultColumn]
    public string CompanyCode { get; set; }

answered Jun 25 '13 at 14:06

Eduardo Molteni

38,786
23
141
206

Thanks @Eduardo, that worked, but the CompanyCode was not populated, was left blank, how can I tell PetaPoco to map the resulting CompanyCode column to the property? – Mark Kadlec Jun 25 '13 at 19:27

Can PetaPoco populate an object using a stored procedure with a join clause?

2 Answers2