C# Join 3 tables using Linq?

Question

I’m picking it up to learn C# and I’m picking it up at some points.

I have 3 tables.

Follow the models

public class Tag
{
    [Key]
    public int ID_Tag { get; set; }

    [Required(ErrorMessage = "Preencha o Nome")]
    [MaxLength(100)]
    public string Nome_Tag { get; set; }
}

---

public class Local
{
    [Key]
    public int ID_Local { get; set; }

    [Required(ErrorMessage = "Preencha o Nome")]
    [MaxLength(100)]
    public string Nome_Local { get; set; }
    public string Tipo_Local { get; set; }
}

---

public class LocTag
{
    [Key]
    public int ID_LocTag { get; set; }

    [Required]
    [ForeignKey("Tags")]
    public int ID_Tags { get; set; }

    [Required]
    [ForeignKey("Locais")]
    public int ID_Local { get; set; }

    public virtual Tag Tags { get; set; }
    public virtual Local Locais { get; set; }
}

and would like to make the return of SQL return me as follows.

"result":[ {  
         "Locais" :  { 
                        ID_Local, 
                        Nome_Local, 
                        Tag: [{ 
                              ID_Tag, 
                              Nome_Tag }]
                      }  
           }]

I tried in various ways some were very close, but not the way I intended.

var result = db.LocTag.Where(x => x.Locais.Tipo_Local == Nome).ToList();

give me the following return:

"result": [
        {
            "Locais": {
                "ID_Local": 5,
                "Nome_Local": "NOME'
            },
            "Tags": {
                "ID_Tag": 1,
                "Nome_Tag": "NOME_TAG"
            },
            "ID_LocTag": 1,
            "ID_Tags": 1,
            "ID_Local": 5
        },....}]

Answer 1

You need to add a Select after Where created the structure you want, below follows an example with anonymous class:

var result = db.LocTag.Where(x => x.Locais.Tipo_Local == Nome)
    .Select(c =>  new { 
                          ID_Local = c.Locais.ID_Local, 
                          Nome_Local = c.Locais.Nome_Local, 
                          Tag = c.Tags 
                       }).ToList();

Here’s another example where I created a class to represent the structure:

public class Locais
{
    public int ID_Local { get; set; }
    public string Nome_Local { get; set; }
    public Tag Tag { get; set; }
}

Again I run the same command but in this case I fill the above class:

 var result = db.LocTag.Where(x => x.Locais.Tipo_Local == Nome)
      .Select(c => new Locais { 
                                  ID_Local = c.Locais.ID_Local, 
                                  Nome_Local = c.Locais.Nome_Local, 
                                  Tag = c.Tags 
                              }).ToList();

I hope I’ve helped, any doubt I’m available.