CollaboratingPlatypus / PetaPoco

Official PetaPoco, A tiny ORM-ish thing for your POCO's
Other
2.07k stars 601 forks source link

How to implement MTM relation without getting duplicate data on a Multi-Poco query? #718

Open Tilation opened 7 months ago

Tilation commented 7 months ago

I'm trying to get Many-To-Many related entities using the Multi-Poco query as such, but it returns duplicated data.

var sql = @"select m.*, q.*
from test.queue q
left join test._monitor_queue mq on q.id_queue = mq.id_queue
left join test.monitor m on m.id_monitor = mq.id_monitor
where m.id_monitor = 1;";

var result = db2.Query<Data.Monitor, Queue, Data.Monitor>((monitor, queue) => 
{
    if (monitor.Queues == null) monitor.Queues= new List<Queue>();
    monitor.Queues.Add(queue);
    return monitor;
}, sql).ToArray();

I expected the Func to run with the same monitor entity (as the sql query returns the queues for a single monitor) but it creates a new monitor for each queue.

Result: Monitor Queue List
Monitor0 [Queue0]
Monitor1 [Queue1]
Expected Result: Monitor Queue List
Monitor0 [Queue0, Queue1]

Is MTM supported by PetaPoco? How can I achieve my expected result?