Ruben Ruben - 3 months ago 33
JSON Question

Getting data from multiple tables (C# Entity), linked by foreign keys, converted to anonymous object

I'm new to Entity/Linq/Lambda and I have the following problem:

I have a web application which provides a JSON Api through ASP.NET MVC. The database is MSSQL and I use the C# entity framework as data access layer.

When getting data from a single table, I need to convert this to an anonymous object, before I can convert it to JSON to avoid a circular reference error.

This is a simplified example, but take these tables for example:

DB Schema

If I simply want to return all the translators in JSON, this is how I go about it:

DBEntities db = new DBEntities();

var data = db.Translator.Select(x => new
{
TranslatorID = x.TranslatorID,
FirstName = x.FirstName,
LastName = x.LastName,
Email = x.Email,
Referenced = x.TranslatorLanguage.Count != 0
});

return Json(data, JsonRequestBehavior.AllowGet);


The generated Model classes by Entity would look something like this:

public partial class Translator
{

public Translator()
{
this.TranslatorLanguage = new HashSet<TranslatorLanguage>();
}

public int TranslatorID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Email { get; set; }

public virtual ICollection<TranslatorLanguage> TranslatorLanguage { get; set; }
}

public partial class TranslatorLanguage
{
public int TranslatorLanguageID { get; set; }
public int SourceLanguageID { get; set; }
public int TargetLanguageID { get; set; }

public virtual Translator Translator { get; set; }
public virtual Language Language1 { get; set; }
public virtual Language Language2 { get; set; }
}

public partial class Language
{

public Language()
{
this.TranslatorLanguage = new HashSet<TranslatorLanguage>();
this.TranslatorLanguage1 = new HashSet<TranslatorLanguage>();
}

public int TranslatorLanguageID { get; set; }
public int SourceLanguageID { get; set; }
public int TargetLanguageID { get; set; }

public virtual ICollection<TranslatorLanguage> TranslatorLanguage { get; set; }
public virtual ICollection<TranslatorLanguage> TranslatorLanguage1 { get; set; }
}


But I would like to be able to return a JSON with all the translators where each Translator-object contains an array with the TranslatorLanguage entries, and for each source- and target language to have it's varchar code and description values.

I have no idea how to go about this,
Thanks in advance.

Answer

The same way you project (select) Translator to anonymous type, you can project TranslatorLanguage to a nested anonymous type list.

Since you have defined the necessary navigation properties, it's quite easy - all you need is to follow the navigation properties (i.e. navigate) inside the query like if they were objects:

var data = db.Translator.Select(t => new
{
    TranslatorID = t.TranslatorID,
    FirstName = t.FirstName,
    LastName = t.LastName,
    Email = t.Email,
    Languages = t.TranslatorLanguage.Select(tl => new
    {
        SourceCode = tl.Language1.Code,
        SourceDescription = tl.Language1.Description,
        TargetCode = tl.Language2.Code,
        TargetDescription = tl.Language2.Description,
    }).ToList()
}).ToList();