LINQ/C# - Making a DTO from a collection?

Question

I'm using EF 6.2 with SQL. Suppose I have these DTO classes:

private class ParentModel
{
    public string FullName { get; set; }
    public IEnumerable<ChildModel> Children { get; set; }
}
private class ChildModel
{
    public string FullName { get; set; }
    public string SpiritAnimalDescription { get; set; }
}

ParentModel is derived from an entity class Parent.

ChildModel is from Child, which has a relationship with another entity class SpiritAnimal. Note that I changed it in the .EDMX to Children.

As you can infer, SpiritAnimal has a Description field which I'm trying to retrieve into the ChildModel field, SpiritAnimalDescription.

Naturally, a Parent has a collection of Child, which in turn has one SpiritAnimal (by design). Now, I'm trying to obtain a List<ParentModel> with this code, which currently isn't working:

var query = from p in db.Parents
            join c in db.Children on p.Id equals c.Parent_Id
            join sa in db.SpiritAnimals on c.SpiritAnimal_Id equals sa.Id
            select new ParentModel
            {
                FullName = p.LastName + ", " + p.FirstName
                Children = c.Select(a => new ChildModel // <-- Error here :(
                {
                    FullName = a.FirstName + " " + a.LastName,
                    SpiritAnimalDescription = sa.Description
                }
            };

var list = query.ToList();

How can I solve this, as efficiently as possible? Thanks!

EDIT:

Entity classes look something like this, for brevity:

private class Parent
{
    public int Id { get; set; } // PK
    public string LastName { get; set; }
    public string FirstName { get; set; }
}
private class Child
{
    public int Id { get; set; } // PK
    public string LastName { get; set; }
    public string FirstName { get; set; }
    public int Parent_Id { get; set; } // FK
    public int SpiritAnimal_Id { get; set; } // FK
}
private class SpiritAnimal
{
    public int Id { get; set; } // PK
    public string Description { get; set; }
}

Can you post original classes `Parent` ,`Child` and `SpiritAnimal` . Want to check if foreign key is referenced — Sumit raj, Jul 28 '18 at 06:18
@Sumitraj Hey, can you check it out now, the changes? Thanks — AwonDanag, Jul 28 '18 at 07:04
A/q ur classes there is no foreign key reference. Ok so we'll have go with join. If there was foreign key using navigational properties it would have been one liner — Sumit raj, Jul 28 '18 at 07:07
What's the error message you get on ChildModel in the linq query? — Handbag Crab, Jul 28 '18 at 09:48
Not working here means that, there currently isn't any `.Select()` given by Intellisense from the joined `Child` table, on the variable `c`... the code above is what I think should work, which doesn't. — AwonDanag, Jul 28 '18 at 12:59
You should use [`GroupJoin`](https://stackoverflow.com/questions/15595289/linq-to-entities-join-vs-groupjoin/15599143#15599143). — Gert Arnold, Jul 28 '18 at 14:20

score 1 · Answer 1 · answered Jul 28 '18 at 12:29

1

Should look something like this:

var query = from p in db.Parents
        select new ParentModel()
        {
            FullName = p.LastName + ", " + p.FirstName,
            Children = p.Clildren.Select(a => new ChildModel() 
                        {
                            FullName = a.FirstName + " " + a.LastName,
                            SpiritAnimalDescription = sa.Description
                        }).ToList()
        };

answered Jul 28 '18 at 12:29

David Browne - Microsoft

80,331
6
39
67

Hey David, thanks but `p.Children` doesn't exist really. I'm manually joining the tables here. :( – AwonDanag Jul 28 '18 at 13:01
2

Why would you not have a Navigation Property? Anyway you could use a subquery instead of a join. – David Browne - Microsoft Jul 28 '18 at 13:03
Thanks. I eventually re-designed with them navigation properties as you stated, which I figured would help things more in the long run. Although I was initially planning to join everything manually. – AwonDanag Aug 01 '18 at 04:15

score 1 · Accepted Answer · answered Jul 28 '18 at 14:30

Your code cannot be compiled and run, so it is impossible to determine exactly what should be.

I can only assume that it should be something like this:

var query = from p in db.Parents
            select new ParentModel
            {
                FullName = p.LastName + ", " + p.FirstName,
                Children = db.Children.Where(c => c.Parent_Id == p.Id)
                    .Select(c => new ChildModel
                    {
                        FullName = c.FirstName + " " + c.LastName,
                        SpiritAnimalDescription = db.SpiritAnimals
                            .FirstOrDefault(sa => sa.Id == c.SpiritAnimal_Id).Description
                    })
            };

Note: use the navigation properties.

Although David's answer is what I would later implement, this actually answers the question. Thanks! — AwonDanag, Aug 01 '18 at 04:14

LINQ/C# - Making a DTO from a collection?

2 Answers2