Return json from query Inner Join ASP.NET WEB app C#

Question

Good afternoon, you guys. I’m trying to return the result of a query with INNER JOIN in a C# REST API but I’m not getting it,I can’t use frameworks, it has to be at the base of the code, I’ve searched a lot but I only think with frameworks, follow me u code; TO:

  public class Marca
        {
            public int MarcaId { get; set; }
            public string Nome { get; set; }
            public Patrimonio patrimonio;
        }

Repository:

public List<Marca> GetComposto(int marcaId)
    {
        List<Marca> marcas = new List<Marca>();
        List<Patrimonio> patrimonios = new List<Patrimonio>();
        SqlCommand query = new SqlCommand();
        connection.Open();
        query.Connection = connection;
        query.CommandText = @"SELECT Patrimonio.Nome, Patrimonio.MarcaId, NumeroTombo, Descricao FROM Marca INNER JOIN Patrimonio ON  Marca.MarcaId = Patrimonio.MarcaId  WHERE Marca.MarcaId = @marcaId;";

        query.Parameters.AddWithValue("@marcaId", marcaId);

        SqlDataReader leitura = query.ExecuteReader();

        while (leitura.Read())
        {
            Marca marca = new Marca();
            Patrimonio patrimonio = new Patrimonio();
            marca.patrimonio.Nome = (string)leitura["Nome"];
            marca.patrimonio.MarcaId =  (int)leitura["MarcaId"];
            marca.patrimonio.NumeroTombo = (int)leitura["NumeroTombo"];
            marca.patrimonio.Descricao = (string)leitura["Descricao"];

            marcas.Add(marca);

        }
        return marcas;
    }

Controller:

[Route("api/marca/patrimonio")] 
    public List<Marca> GetComposto(int marcaId)
    {
        try
        {
            using (RepositorioMarca conexao = new RepositorioMarca())
            {
                marcas = conexao.GetComposto(marcaId);
                return marcas;
            }
        }
        catch (SqlException e)
        {
            return null;
        }
    }

This gives the error: "Object reference not defined for an instance of an object" in the Repository Class, in all the lines I try to build the patrimony(brand.patrimonio.Name, brand.patrimonio.Marcaid...

Answer 1

The error is in trying to access properties of an object that has not been initialized, in your case patrimonio class Marca, in her question she does not even own the getters and setters

public class Marca
{
    public int MarcaId { get; set; }
    public string Nome { get; set; }
    public Patrimonio patrimonio {get; set;}
}

But the problem, in fact, is where you’re reading the sql return and trying to popular the tag list, you create an instance of Patrimonio, does not use and tries to assign values to Marca.patrimonio that has not been incilized...

    while (leitura.Read())
    {
        Marca marca = new Marca();

        //AQUI ESTÁ O ERRO
        //Patrimonio patrimonio = new Patrimonio();

        marca.patrimonio = new Patrimonio();

        marca.patrimonio.Nome = (string)leitura["Nome"];
        marca.patrimonio.MarcaId =  (int)leitura["MarcaId"];
        marca.patrimonio.NumeroTombo = (int)leitura["NumeroTombo"];
        marca.patrimonio.Descricao = (string)leitura["Descricao"];

        marcas.Add(marca);

    }