1

I am creating a report and need to pass a query from SQL Server to Entity Framework.

The query is:

SELECT        
    contas_receber.id, pessoa.nome, contas_receber.vencimento, 
    contas_receber.valor_pago, contas_receber.observacao, pessoa.estado
FROM            
    contas_receber 
INNER JOIN
    pessoa ON contas_receber.pessoa_id = pessoa.id 
INNER JOIN
    classificacoes ON classificacoes.id = pessoa.classificacao_id 
LEFT OUTER JOIN
    servicos ON servicos.id = contas_receber.servico_id 
LEFT OUTER JOIN
    produto ON produto.id = contas_receber.venda_id
WHERE        
    (contas_receber.quitado = 0) 
    AND (CAST(contas_receber.vencimento AS date) >= @datavenc1) 
    AND (CAST(contas_receber.vencimento AS DATE) <= @datavenc2) 
    AND (classificacoes.id = @idclassificacao OR @idclassificacao IS NULL) 
    AND (servicos.id = @idservico OR @idservico IS NULL) 
    AND (servicos.plano = @tiposervico OR @tiposervico IS NULL) 
    AND (produto.id = @idproduto OR @idproduto IS NULL) 
    AND (pessoa.status_financeiro = @statusfinanceiro OR @statusfinanceiro IS NULL) 
    AND (pessoa.estado = @estado OR @estado IS NULL)
ORDER BY 
    contas_receber.vencimento

How would you stay in Entity Framework?

what I've done so far:

var contas = from c in _context.ContasReceber
join p in _context.Pessoas on c.PessoaId equals  p.Id
join cl in _context.Classificacoes on p.ClassificacaoId equals cl.Id
join ps in _context.PlanosServicos on c.ServicoId equals ps.Id
join pr in _context.Produtos on c.IdVenda equals pr.Id
join tp in _context.TiposProdutos on pr.TiposProdutosId equals tp.Id
where c.Vencimento >= Inicial && c.Vencimento <= Final && (cl.Id == classificacoes || classificacoes is null)

The courses are different because the seats are different, but the sense is the same. My biggest problem is in the conditional whare, when you have to verify that the variable is null

Renato
  • 119
  • 1
  • 1
  • 10
  • Can you please show us what have you tried till now? – Rudresha Parameshappa Aug 15 '18 at 15:58
  • Perhaps this link will be helpful http://www.entityframeworktutorial.net/entityframework4.3/raw-sql-query-in-entity-framework.aspx – Tasos K. Aug 15 '18 at 16:03
  • @TasosK. Using this example of a policy error, the visual does not recognize the .SqlQuery – Renato Aug 15 '18 at 16:28
  • @RudreshaParameshappa I edited my question, please, look what I did. – Renato Aug 15 '18 at 17:04
  • 1
    If you have properly configured EF, you can eliminate those joins and make use of the navigation properties. http://www.entityframeworktutorial.net/efcore/querying-in-ef-core.aspx – Steve Greene Aug 15 '18 at 18:19
  • @SteveGreene I need to group and add some fields, so how? – Renato Aug 15 '18 at 20:36
  • @SteveGreene I checked here that this would work if I had foreign key of all the tables in a single table. As it is a cascade effect, that is, one table is tied to the other as if it were a tree. So that way it does not work. Thank you. – Renato Aug 16 '18 at 11:54
  • Not understanding you, but if you are saying that you have a deep structure of children, grandchildren, etc. EF can certainly handle that with navigation properties. Grouping is also not a problem. – Steve Greene Aug 16 '18 at 13:55
  • @SteveGreene It has to give me an example but deep, for me to be able to understand, what you have given me before is very simple, there are only two tables. – Renato Aug 16 '18 at 14:04
  • There are lots of examples out here like [this](https://stackoverflow.com/questions/5905716/how-do-i-eagerly-include-the-child-and-grandchild-elements-of-an-entity-in-entit). You can `Include` multiple navigation properties instead of using `joins`. The point I am trying to make is explained [here](https://coding.abel.nu/2012/06/dont-use-linqs-join-navigate/). I would recommend you build your complex query up step by step. – Steve Greene Aug 16 '18 at 15:24
  • @SteveGreene I'm trying to do the way you told me, but at the time of exposure I'm getting an error. Create a new question with this error so you do not get confused. You can access it here. [link](https://stackoverflow.com/questions/51882104/query-display-error) – Renato Aug 16 '18 at 17:07

1 Answers1

0

using

Database.SqlQuery():

The Database class represents the underlying database and provides various methods to deal with the database. The Database.SqlQuery() method returns a value of any type.

//Example refer this link

using (var ctx = new SchoolDBEntities())
{
    string studentName = ctx.Database.SqlQuery<string>("Select studentname from Student 
                         where studentid=1").FirstOrDefault();
}
Sunny Jangid
  • 578
  • 4
  • 19