Convert SQL to LINQ

Asked

Viewed 247 times

3

How do I convert the SQL below to LINQ?

SELECT 
`clientes`.`Nome`,
`clientes`.`Cpf`,
`clientes`.`email`,
`clientes`.`Id` as clienteId,
`cartoes`.`Id` as CartaoId,
SUM(`historicoTransacaoCartao`.`Valor`) AS `saldo`
FROM
  `clientes`
  INNER JOIN `cartoesClientes` ON (`clientes`.`Id`=`cartoesClientes`.`ClienteId`)
  INNER JOIN `cartoes` ON (`cartoesClientes`.`CartaoId` = `cartoes`.`Id`)
  INNER JOIN `historicoTransacaoCartao` ON (`cartoes`.`Id` = `historicoTransacaoCartao`.`CartaoId`)
GROUP BY
  `clientes`.`Nome`,
  `clientes`.`Cpf`,
  `clientes`.`email`,
  `clientes`.`Id`,
  `cartoes`.`Id`

Is there any website/software that I can do this type of conversion?

c# sql linq

  • take a look at this discussion: https://stackoverflow.com/questions/296972/sql-to-linq-tool may be useful

  • The point is that an automatic translation from SQL to LINQ will usually have to run more transliteration than translation - generating examples of how to NOT write LINQ queries. For this reason, there are few (if any) tools that can reliably convert SQL to LINQ. But I highly recommend Linqpad to learn LINQ.

  • You have the entities, being able to editing your question?

1 answer

3


Check also on Ideone.

using System;
using System.Linq;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            var clientes = new[]
            {
                new { Id = 1, Email = "[email protected]", Nome = "Nome 1", Cpf = 1L },
                new { Id = 2, Email = "[email protected]", Nome = "Nome 2", Cpf = 2L },
                new { Id = 3, Email = "[email protected]", Nome = "Nome 3", Cpf = 3L },
                new { Id = 4, Email = "[email protected]", Nome = "Nome 4", Cpf = 4L },
                new { Id = 5, Email = "[email protected]", Nome = "Nome 5", Cpf = 5L },
            };

            var cartoesClientes = new[]
            {
                new { Id = 1, ClienteId = 1, CartaoId = 1 },
                new { Id = 2, ClienteId = 1, CartaoId = 2 },
                new { Id = 3, ClienteId = 2, CartaoId = 3 },
                new { Id = 4, ClienteId = 2, CartaoId = 4 },
                new { Id = 5, ClienteId = 4, CartaoId = 5 },
                new { Id = 6, ClienteId = 4, CartaoId = 6 },
            };

            var cartoes = new[]
            {
                new { Id = 1, Numero = 1L },
                new { Id = 2, Numero = 2L },
                new { Id = 3, Numero = 3L },
                new { Id = 4, Numero = 4L },
                new { Id = 5, Numero = 5L },
                new { Id = 6, Numero = 6L },
                new { Id = 7, Numero = 7L },
                new { Id = 8, Numero = 8L },
                new { Id = 9, Numero = 9L },
                new { Id = 10, Numero = 10L },
            };

            var historicoTransacaoCartao = new[]
            {
                new { Id = 1, CartaoId = 1, Valor = 100m },
                new { Id = 2, CartaoId = 5, Valor = 100m },
                new { Id = 3, CartaoId = 1, Valor = 100m },
                new { Id = 4, CartaoId = 3, Valor = 100m },
                new { Id = 5, CartaoId = 3, Valor = 100m },
                new { Id = 6, CartaoId = 4, Valor = 100m },
                new { Id = 7, CartaoId = 1, Valor = 100m },
            };

        var query = from cliente in clientes
                    join cartaoCliente in cartoesClientes 
                        on cliente.Id equals cartaoCliente.ClienteId
                    join cartao in cartoes
                        on cartaoCliente.CartaoId equals cartao.Id
                    join historico in historicoTransacaoCartao
                        on cartao.Id equals historico.CartaoId
                    let x = new
                    {
                        cliente.Nome,
                        cliente.Cpf,
                        cliente.Email,
                        cliente.Id,
                        CartaoId = cartao.Id,
                        historico.Valor
                    }
                    group x by new { ClienteId = cliente.Id, CartaoId = cartao.Id } into g
                    select new
                    {
                        g.First().Nome,
                        g.First().Cpf,
                        g.First().Email,
                        g.First().CartaoId,
                        Saldo = g.Sum(s => s.Valor)
                    };

            Console.WriteLine("Nome CPF E-mail CartaoId Saldo");

            foreach (var q in query)
            {
                Console.WriteLine("{0}; {1}; {2}; {3}; {4}",
                    q.Nome, q.Cpf, q.Email, q.CartaoId, q.Saldo);
            }

            Console.Read();
        }
    }
}

Browser other questions tagged c# sql linq

You are not signed in. Login or sign up in order to post.