i have a table in my database that stores translations

ResourseKey	ResourseText	LanguageId
Home	Home	1
Home	Hem	2
Search	Search	1
Search	Sök	2

and the Languages table

Id	Language
1	English
2	Swedish

I am trying to return an object that looks like this

{
   "Home":{
      "Swedish":"Hem",
      "English":"Home"
   },
   "Search":{
      "Swedish":"Sök",
      "English":"Search"
   }
}

i started by doing

var data = await _context.Translations
             .Include(x => x.Language)
             .ToListAsync();
return data.ToLookup(p => p.ResourseKey , p => p).ToDictionary(x => x.Key, x => x.ToArray());

but I can't go any further though

I want to format the lookup phrase to return the desired structure instead of returning just p

I can off course fix it by js on the client side but I want to all the heavy lift on the server

I'm really sorry if my question is unstructured this is the first time a post a question on SOF

CodePudding user response：

To format the json you can use the JsonConverter class, the write method is the method that generate the output. The parameter writer has many methods that helps you to generate the output just as you want.

public class ResourceByLanguage
{
    public string Language { get; set; }
    public string Value { get; set; }

    public ResourceByLanguage(Resource resource)
    {
        Language = resource.Language.Title;
        Value = resource.Text;
    }
}

and:

public class ResourceByLanguageConverter : JsonConverter<ResourceByLanguage[]>
{
    public override ResourceByLanguage[]? Read(ref Utf8JsonReader reader, Type typeToConvert,
        JsonSerializerOptions options)
    {
        throw new NotImplementedException();
    }

    public override void Write(Utf8JsonWriter writer, ResourceByLanguage[] values, JsonSerializerOptions options)
    {
        writer.WriteStartObject();
        foreach (var value in values)
        {
            writer.WritePropertyName(value.Language);
            writer.WriteStringValue(value.Value);
        }

        writer.WriteEndObject();
    }
}

and (on a console application):

var result = resources
    .GroupBy(p => p.ResourceKey)
    .ToDictionary(p => p.Key,
        p => p.Select(resource => new ResourceByLanguage(resource)).ToArray());

var jsonConverter = new JsonSerializerOptions()
{
    Converters = {new ResourceByLanguageConverter()}
};
var json = JsonSerializer.Serialize(result, jsonConverter);
Console.WriteLine(json);

will generate this output:

{"home":{"English":"Home","Swedish":"Hem"},"search":{"English":"Search"}}

if you are using aspnet, then register the converter by adding this code on your services.AddController line (that can be on Program.cs or Startup.cs):

services.AddControllers().AddJsonOptions(j => 
        {
            j.JsonSerializerOptions.Converters.Add(new ResourceByLanguageConverter());
        });

then use:

return resources
     // here you have to add the include that you used on question
    .GroupBy(p => p.ResourceKey)
    .ToDictionary(p => p.Key,
        p => p.Select(resource => new ResourceByLanguage(resource)).ToArray());

and aspnet you do the job.

CodePudding user response：

I think Inner Join can help you in this kind of scenario.

Consider there is two Entity as per below:

public class Word
{
    public int Id { get; set; }
    public string ResourseKey { get; set; }
    public string ResourseText { get; set; }
    public string Language { get; set; }
}

And the other:

public class Language
{
    public int Id { get; set; }
    public string Lang { get; set; }
}

Now we initialize some data for inner join and select the data that we need:

    internal class Program
{
    static void Main(string[] args)
    {
        List<Language> languages = new List<Language>()
        {
            new Language{Id = 1, Lang="English"},
            new Language{Id = 2, Lang="Swedish"}
        };

        List<Word> words = new List<Word>()
        {
            new Word{Id = 1, ResourseKey = "Home", ResourseText = "Home", Language = "English"},
            new Word{Id = 1, ResourseKey = "Home", ResourseText = "Hem", Language = "Swedish"},
            new Word{Id = 1, ResourseKey = "Search", ResourseText = "Search", Language = "English"},
            new Word{Id = 1, ResourseKey = "Search", ResourseText = "Sök", Language = "Swedish"},
        };

        var innerGroupJoinQuery =
                from lang in languages
                join word in words on lang.Lang equals word.Language into prodGroup
                select new { LangName = lang.Lang, Words = prodGroup };

        foreach (var item in innerGroupJoinQuery)
        {
            Console.WriteLine($"Name: {item.LangName}");
            foreach (var w in item.Words)
            {
                Console.WriteLine($"---Word: {w.ResourseText}");
            }
        }

    }
}