Retrieve data from a SQL Linq query with Join

Question

Retrieve data from a SQL Linq query with Join

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

In the presentation layer I call a consLaboratorio method that is in a separate namespace where all data access is performed. This method returns an object MiddleOneReturn with: error_code (0-if run without errors) and execution result ("object []"). I retrieve the data through the following Cast : ((List<Laboratorio>)(md.Mensagem[0])).ToList(); , where Laboratorio refers to the mapped class 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 in a separate namespace 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 consLaboratorio method gets into a namespace with all other data access methods 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 execute a join with a return that belongs to two distinct tables like 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 can not cast .

The content returned is:

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

How can I solve this problem?

.net linq

asked by anonymous 21.11.2014 / 21:06

2 answers

1

When you run select new {} in your query with EntityFramework, you are creating an AnonymousType. Unfortunately it is not possible to cast an Anonymous type to a defined type (Lab). 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 understand better!

21.11.2014 / 22:30

Is it possible to assign repeat loop to a variable? PHP function in_array ()

score 1 · Accepted Answer

When you do not set the return type of your query, it is typed as Anonymous Type. In this case, you are putting it in a object variable so that it can return from the method. However, this variable object can not be converted to 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 best programming practice, you can type the query return into a class of your 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;
}