CODEWITHSUNDEEP
c#sql-serverjsonentity-frameworklinq
Ruben
Ruben

Reputation: 509

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.

Upvotes: 2

Views: 1813

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205849

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();

Upvotes: 2

Related Questions