Recover data from a Linq SQL query with Join

Question

In the presentation layer I call a method consLaboratorio who is in a namespace separate where all access to the data is carried out. This method returns an object MiddleOneReturn with: codeErro (0 ran without errors) and the result of the execution ("Object[]"). I recover the data through the following Cast: ((List<Laboratorio>)(md.Mensagem[0])).ToList();, where Laboratorio refers to the class mapped via Entity framework.

EstudoDotNetNegocio objEstudoDotNet = new EstudoDotNetNegocio();
MiddleOneReturn md = new MiddleOneReturn();
md = objEstudoDotNet.consLaboratorio(1);
if (md.CodigoErro == 0)
{
    var local = ((List<Laboratorio>)(md.Mensagem[0])).ToList();
    string idLaboratorio = local.FirstOrDefault().IdLaboratorio.ToString();
    string nomeLaboratorio = local.FirstOrDefault().NmLaboratorio.ToString();
}

MiddleOneReturn gets into a namespace separated with other utility methods. It is used to traffic data between layers and has the following structure:

public class MiddleOneReturn
{
    public MiddleOneReturn() { }
    public MiddleOneReturn(int codigo, params object[] mensagem) {
        CodigoErro = codigo;
        Mensagem = mensagem;
    }
    public int CodigoErro { get; set; }
    public object[] Mensagem { get; set; }
}

The method consLaboratorio gets into a namespace with all other methods of data access via Linq.

public MiddleOneReturn consLaboratorio(Int64 IdLaboratorio)
{
    using (BDEntities db = new BDEntities())
    {
        var laboratorio = db.Laboratorio.Where(v => v.IdLaboratorio.Equals(IdLaboratorio)).ToList();
        MiddleOneReturn md = new MiddleOneReturn();
        md.CodigoErro = 0;
        md.Mensagem = new object[] { laboratorio };
        return md;
    }
}

The problem is when I run a Join with return belonging to two distinct tables as the example below:

public MiddleOneReturn consLaboratorioCidade(Int64 IdLaboratorio)
{
    using (BDEntities db = new BDEntities())
    {
        var laboratorio = (from l in db.Laboratorio
                           join c in db.Cidade on l.IdLaboratorio equals c.IdCidade into l_join_c
                           from c in l_join_c.DefaultIfEmpty()
                           where l.IdLaboratorio == IdLaboratorio
                            select new
                            {
                                l.IdLaboratorio,
                                l.NmLaboratorio,
                                c.NmCidade
                            }).ToList();
        MiddleOneReturn md = new MiddleOneReturn();
        md.CodigoErro = 0;
        md.Mensagem = new object[] { laboratorio };
        return md;
    }
}

In the model, generated from the database, there is no class with the fields returned when executing the above command, so it is not possible to perform the Cast.

The returned content is:

md.Mensagem[0]
Count = 1
    [0]: { IdLaboratorio = 1, NmLaboratorio = "Laboratório 1", NmCidade = "Campinas" }

How to solve this problem?

Answer 1

When you do not set the return type of your query, it is typed as Anonymous Type. In this case, you are placing it in a variable object to return it from the method. However, this variable object cannot be converted to the type List<Laboratorio>. But you can do for Linq:

var local = from x in md.Mensagem[0] select new {
    l.IdLaboratorio,
    l.NmLaboratorio,
    c.NmCidade
}

For a better programming practice, you can type the query return in a Class of its own:

public class LaboratorioCidade {
    public int IdLaboratorio {get;set;}
    public string NmLaboratorio {get;set;}
    public string NmCidade {get;set;}

    public LaboratorioCidade(){}
    public LaboratorioCidade(int idLaboratorio, string NmLaboratorio, string NmCidade){
        IdLaboratorio = idLaboratorio;
        NmLaboratorio = NmLaboratorio;
        NmCidade = NmCidade;
    }
}

Query "typing" in your class:

                where l.IdLaboratorio == IdLaboratorio
                select new LaboratorioCidade
                {
                    IdLaboratorio = l.IdLaboratorio,
                    NmLaboratorio = l.NmLaboratorio,
                    NmCidade = c.NmCidade
                }).ToList();

(another approach calling the constructor with parameters):

var queryLocal = (from x in db.Tabela
                  where x.param == true
                  select new LaboratorioCidade {
                      l.IdLaboratorio,
                      l.NmLaboratorio,
                      c.NmCidade
                  }).ToList();

var queryTipada = from x in queryLocal
                  select new LaboratorioCidade(x.IdLaboratorio, x.NmLaboratorio, x.NmCidade);

Finally, call the function:

md = objEstudoDotNet.consLaboratorio(1);
if (md.CodigoErro == 0)
{
    // pode trocar "var" por List<LaboratorioCidade>, se preferir
    var local = md.Mensagem[0].FirstOrDefault(); 
    string idLaboratorio = local.IdLaboratorio.ToString();
    string nomeLaboratorio = local.NmLaboratorio;
}

Answer 2

By running select new {} in your Entityframework query, you are creating an Anonymoustype. Unfortunately it is not possible to cast an Anonymous type for a defined type (Laboratorio). The solution to your problem can be many, the definition of a new type, the implementation of an Extension method of Cast or the use of a cast function between dynamic types.

Take a look at this post here to better understand!