I have for example two tables like:
CREATE TABLE [dbo].[Parent](
[Id] [int] NOT NULL,
[Name] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO
BEGIN TRANSACTION;
INSERT INTO [dbo].[Parent]([Id], [Name])
SELECT 1, N'First parent' UNION ALL
SELECT 2, N'Second parent' UNION ALL
SELECT 3, N'Parent with no childrens'
COMMIT;
CREATE TABLE [dbo].[Child](
[Id] [int] NOT NULL,
[ParentId] [int] NOT NULL,
[Name] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO
BEGIN TRANSACTION;
INSERT INTO [dbo].[Child]([Id], [ParentId], [Name])
SELECT 1, 1, N'First child' UNION ALL
SELECT 2, 2, N'Second child'
COMMIT;
And then two c# classes like:
public class Parent
{
public int Id { get; set; }
public string Name { get; set; }
public IList<Child> Children { get; set; }
}
public class Child
{
public int Id { get; set; }
public int ParentId { get; set; }
public string Name { get; set; }
}
When I use Petapoco and relationextensions like (where db is a instance of Petapocos database object):
var parents = db.FetchOneToMany<Parent, Child>(p => p.Id, "SELECT * FROM Parent AS p LEFT JOIN Child AS c ON p.Id = c.ParentId");
I get the expected result of parents, three items in my list with correct ids but NOT with correct children. The first two are correct with their connected children, "first child" and "second child" BUT the third parent which has no children in the database get a default instance of a child. With that I mean the third parent has a collection of children with count 1. And that list contains one child which has only default values. Id = 0, Name = NULL, ParentId = 0. This is not what I want. I would like that collection to not contain any children. The alternatives I see it is that collection to be null or an instance of IList but with no items.
What am I doing wrong and how could I correct it?
