How to properly return children collection with parent via SQL db in C#

Question

may I ask what's the most efficient way in C# to get parent table data plus children data.

Currently I have a parent table. The parents can have multiple children

CREATE TABLE Parents (
    Id       INT IDENTITY(1,1) NOT NULL
    Name     VARCHAR(255)
)
CREATE TABLE Childrens (
    Id       INT IDENTITY(1,1) NOT NULL
    ParentId INT NOT NULL
    Name     VARCHAR(255)
)

Parent.cs

public int Id {get; set;}
public string Name {get; set;}
public List<Child> Childrens {get; set;}

Child.cs

public int Id {get; set;
public string Name {get; set;}
public int ParentId {get; set;}

Currently I have make multiple database calls. First one gets all the parents. The second one gets all the childrens. Then when all the children returns, it gets added to the parent. I'm wondering is there a way to optimize my sproc or logic call so that I can reduce the number of db calls and reduce the server side operations.

getParents stored procedure

SELECT * FROM Parents

C#

var parents = myContext.Database.SqlQuery<Parent>("getParents");

foreach (var parent in parents)
{
    parent.Childrens = myContext.Database.SqlQuery<Parent>($"SELECT * FROM CHILDRENS WHERE ParentId = {parent.Id}").ToList();

}

No, that's exactly how SQL is meant to work. SQL was not designed nor intended for retrieving _object graphs_, but we're stuck with it (and this explains the rise in the popularity of things like GraphQL) What you _can_ do is use Query Batching: where you have multiple _separate_ `SELECT` queries in a single `SqlCommand`, but that doesn't play-nice with EF: you need to use `SqlCommand`/`SqlDataReader` directly which is more tedious than EF. But if you're using EF, why are you writing raw SQL? Why aren't you using `await db.Parents.Include( p => p.Children ).ToListAsync()`? — Dai, Jan 24 '23 at 19:03
This is called the [n+1 problem](https://stackoverflow.com/q/97197/11683), and the ORM frameworks commonly deal with it by joining the parent and child tables and then parsing out the result into object graphs. That is, when you let them do that by specifying the connections between your data classes, instead of querying everything manually. — GSerg, Jan 24 '23 at 19:05
You can do it all in one call if you so please; a single statement can return multiple result sets. Dapper has [special methods](https://www.learndapper.com/dapper-query/selecting-multiple-results) to consume this; at the core is ADO.NET's `DataReader.NextResult`. The logic for processing things doesn't exactly get a lot simpler, though, and if you're using your database as a glorified data dump while you're doing all the real processing in memory after retrieving it, you're probably leaving a lot of performance on the table. — Jeroen Mostert, Jan 24 '23 at 19:05
@Dai EntityFramework Core has support for [query batching (split queries)](https://learn.microsoft.com/en-us/ef/core/querying/single-split-queries?WT.mc_id=DOP-MVP-5001942#split-queries-1): `var parents = await myContext.Parents.AsSplitQuery().Include(p => p.Children).ToListAsync()` — Andrew Williamson, Jan 24 '23 at 19:13
@AndrewWilliamson Yep - I _waited patiently_ for over _12 years_ (since EF 1.x 2008) for that support to appear - but cruel fate has deemed me to work solely on .NET Framework-only projects that can't run EF Core at all — Dai, Jan 24 '23 at 19:16
@Dai Oh, I'm so sorry for your loss... Have you seen the NuGet package 'Z.EntityFrameworkPlus'? It's an extension for Entity Framework 6 that does essentially the same thing, it's just not as smooth as EF Core. We used it at my last workplace with good results — Andrew Williamson, Jan 24 '23 at 19:22
@AndrewWilliamson I'm familiar with it, but not had an opportunity to use it in production yet - though quite a few of the things that EFP does are things that I already independently developed in my own personal utility library (_great minds..._). — Dai, Jan 24 '23 at 21:18
why are you using two tables for a job can be achieved by just one table? — Gurhan Polat, Jan 24 '23 at 22:30

How to properly return children collection with parent via SQL db in C#

0 Answers0