How can I reorganize data from a table as a complex C# object?

I have a table containing several references to the same posts, themselves containing several references to the same languages like so :

Base on this table, I would like to create a C# object which ressemble to this :

{
   "posts":[
      {
         "1":[
            {
               "en":{
                  "title":"Post 1 title",
                  "body":"This is the content for the post 1"
               }
            },
            {
               "fr":{
                  "title":"Titre de l'article 1",
                  "body":"Voici le contenu de l'article 1"
               }
            }
         ]
      },
      {
         "2":[
            {
               "en":{
                  "title":"Post 2 title",
                  "body":"This is the content for the post 2"
               }
            }
         ]
      },
      {
         "3":[
            {
               "en":{
                  "title":"Post 3 title",
                  "body":"This is the content for the post 3"
               }
            },
            {
               "fr":{
                  "title":"Titre de l'article 3",
                  "body":"Voici le contenu de l'article 3"
               }
            },
            {
               "de":{
                  "title":"Titel von Artikel 3",
                  "body":"Lesen Sie den Inhalt von Artikel 3"
               }
            }
         ]
      }
   ]
}

How can I achieve that in C# ?

After selecting my datas from my SQL database, I loop through every line, trying to add it to a dictionnary of dictionnary of dictionnary of string Dictionary<string, Dictionary<string, Dictionary<string, string>>>, testing if the key exists in the dictionnary. If it exists, I just assign the value. If not, I add the key and then assign the value, like so :

Dictionary<string, Dictionary<string, Dictionary<string, string>>> postsDictionnary= new();
foreach(var post in postsList)
{
if (postsDictionnary.ContainsKey(post.postId))
{
if (postsDictionnary[post.postId].ContainsKey(post.language))
{
if (postsDictionnary[post.itemId][post.language].ContainsKey(post.name))
{
postsDictionnary[post.itemId][post.language][post.name] = post.value;
}
else
{
postsDictionnary[post.postId][post.language].Add(post.name, post.value);
}
}
else
{
postsDictionnary[post.postId].Add(post.language, new Dictionary<string, string>());
postsDictionnary[post.postId][post.language].Add(post.name, post.value);
}
}
else
{
postsDictionnary.Add(post.postId, new Dictionary<string, Dictionary<string, string>>());
postsDictionnary[post.postId].Add(post.language, new Dictionary<string, string>());
postsDictionnary[post.postId][post.language].Add(post.name, post.value);
}
}

postsDictionnary is quite well organized, but is there a better way to achieve this ? It seems pretty dirty to create a dictionnary of dictionnary of dictionnary of string...

Assuming that you have the following class and list of these objects

public class Post
{
    public int PostId { get; set; }
    public string Language { get; set; }
    public string Name { get; set; }
    public string Value { get; set; }
}

Then you can write the following LINQ Query:

var posts = new List<Post>
{
    new Post { PostId = 1, Language = "en", Name = "title", Value = "Post 1 title" },
    new Post { PostId = 1, Language = "en", Name = "body", Value = "This is the content for the post 1" },
    new Post { PostId = 1, Language = "fr", Name = "title", Value = "Titre de l'article 1" },
    new Post { PostId = 1, Language = "fr", Name = "body", Value = "Voici le contenu de l'article 1" },
    new Post { PostId = 2, Language = "en", Name = "title", Value = "Post 2 title" },
    new Post { PostId = 2, Language = "en", Name = "body", Value = "This is the content for the post 2" },
    new Post { PostId = 3, Language = "en", Name = "title", Value = "Post 3 title" },
    new Post { PostId = 3, Language = "en", Name = "body", Value = "This is the content for the post 3" },
    new Post { PostId = 3, Language = "fr", Name = "title", Value = "Titre de l'article 3" },
    new Post { PostId = 3, Language = "fr", Name = "body", Value = "Voici le contenu de l'article 3" },
    new Post { PostId = 3, Language = "de", Name = "title", Value = "Titel von Artikel 3" },
    new Post { PostId = 3, Language = "de", Name = "body", Value = "Lesen Sie den Inhalt von Artikel 3" }
};

var result = posts
    .GroupBy(x => 1)
    .ToDictionary(x => "posts",
        p => p.GroupBy(bp => bp.PostId)
            .ToDictionary(bp => bp.Key,
                bp => bp.GroupBy(lp => lp.Language)
                    .ToDictionary(lp => lp.Key, lp => lp.ToDictionary(n => n.Name, n => n.Value))
            )
    );

I think you need to convert this DataTable into JSON. For that you use can Newtonsoft Json Library in .NET and serialize the data to a valid JSON.

Something like this:

string result;
result = JsonConvert.SerializeObject(dt); //where dt is content of DataTable

For more info Newtonsoft Json

Why not create a class that represent that object?

Here is a class that can be use for your example:

internal class Post
{
public int PostId { get; set; }
public string Language { get; set; }
public string Name { get; set; }
public string Value { get; set; }
}

To convert the result of your SQL query into instance of this class, you can use an ORM. For this example you might want to use a micro ORM like dapper who will see this link https://www.learndapper.com/dapper-query/selecting-multiple-rows

using (var connection = new SqlConnection(connectionString))
{
var sql = "SELECT * FROM yourTable"; // Careful: perf! You might want filters
var posts = await connection.QueryAsync<Post>(sql);
foreach(var post in posts)
{
Console.WriteLine($"{post.PostId} {post.Language} {post.Name} {post.Value}");
}
}

Some example query you can do using LINQ:

var engPosts = posts.Where(x=>x.Language == "ENG").ToList();
var allTittles = posts.Where(x=>x.Name=="Title").ToList();

Bear in mind that depending of your use case you might want to have other data structure on top of this one to improve performance, usability, search, etc.

You could create an object model consisting of posts containing different language contents.

public class Content
{
    public string Title { get; set; }
    public string Body { get; set; }
}

public class Post
{
    public int Id { get; set; }
    public Dictionary<string, Content> LocalizedContent { get; } = new();
}

Example:

List<Post> posts = new();

var post = new Post { Id = 1 };
post.LocalizedContent["en"] = new Content { 
    Title = "Post 1 title", 
    Body = "This is the content for the post 1"
};
post.LocalizedContent["fr"] = new Content {
    Title = "Titre de l'article 1",
    Body = "Voici le contenu de l'article 1"
};
posts.Add(post);

A post contains a dictionary of contents where the language is used as key. This allows you to add different localized contents to a post.

In a DB you would have 2 tables:

TABLE Post
Id int PRIMARY KEY
TABLE Content
PostId int PRIMARY KEY, FOREIGN KEY
Language char(2) PRIMARY KEY
Title varchar(80)
Body varchar(max)

The language could could also be a foreign key into a language table containing supported languages along with the full language name.

For the people who prefer c# stricted code

    var result = new Dictionary<int, Dictionary<string, PostItem>>();
var postsG = posts.GroupBy(p => new { p.PostId });
foreach (var item in postsG)
{
var posGG = item.GroupBy(i => i.Language);
var dict = new Dictionary<string, Item>();
foreach (var p in posGG)
{
var i = new PostItem();
foreach (var pp in p)
if (pp.Name == "title") i.title = (string)pp.Value;
else i.body = (string)pp.Value;
dict.Add(p.Key, i);
}
result.Add(item.Key.PostId, dict);
}
public class PostItem
{
public string title { get; set; }
public string body { get; set; }
}

output

JsonConvert.SerializeObject(result, Newtonsoft.Json.Formatting.Indented);
{
"1": {
"en": {
"title": "Post 1 title",
"body": "This is the content for the post 1"
},
"fr": {
"title": "Titre de l'article 1",
"body": "Voici le contenu de l'article 1"
}
},
"2": {
"en": {
"title": "Post 2 title",
"body": "This is the content for the post 2"
}
},
"3": {
"en": {
"title": "Post 3 title",
"body": "This is the content for the post 3"
},
"fr": {
"title": "Titre de l'article 3",
"body": "Voici le contenu de l'article 3"
},
"de": {
"title": "Titel von Artikel 3",
"body": "Lesen Sie den Inhalt von Artikel 3"
}
}
}