none
Transformar a seguinte consulta em LINQ... RRS feed

  • Pergunta

  • Olá pessoal.

    Tenho um programa q fiz a algum tempo, onde utilizei SQL para retornar dados do banco de dados (o banco é o mysql).

    Paralelamente, quando estava desenvolvendo este programa até os dias de hj, venho estudando LINQ.

    Já criei pequenos apliucativos com consultas simples e gostei muito da facilidade e simplicidade do LINQ.

    No entanto, surgiu a idéia de reescrever meu antigo programa, deixando o SQL de lado para dar lugar ao LINQ.

    Todavia, estou tendo dificuldades em algumas consultas. O MySQL suporta subqueries e não estou conseguindo fazer isto com LINQ. Da mesma forma não estou sabendo construir consultas com tabelas aninhadas.

    Por exemplo, vejam a query a seguir:

    "SELECT contratos.id_contrato, contratos.senha, contratos.id_grupo, clientes.nome AS 'nome_cliente', produtos.nome_produto, (SELECT parcelas.parcela FROM parcelas WHERE parcelas.vencimento <= sorteios.data_sorteio AND parcelas.id_contrato = contratos.id_contrato ORDER BY parcelas.parcela DESC LIMIT 1) AS 'parcela_sorteada', (SELECT DATE_FORMAT(parcelas.vencimento, '%d/%m/%Y') FROM parcelas WHERE parcelas.vencimento <= sorteios.data_sorteio AND parcelas.id_contrato = contratos.id_contrato ORDER BY parcelas.parcela DESC LIMIT 1) AS 'parcela_sorteada_vencimento', (SELECT DATE_FORMAT(sorteios.data_sorteio, '%d/%m/%Y') FROM parcelas WHERE parcelas.vencimento <= sorteios.data_sorteio AND sorteios.id_contrato = contratos.id_contrato ORDER BY parcelas.parcela DESC LIMIT 1) AS 'sorteada_em', (SELECT COUNT(parcelas.parcela) FROM parcelas WHERE parcelas.situacao = '1' AND parcelas.id_contrato = contratos.id_contrato) AS 'parcelas_pagas', (SELECT GROUP_CONCAT(CONVERT(parcelas.parcela, CHAR(8)), DATE_FORMAT(parcelas.vencimento, 'ª-%d/%m/%Y') ORDER BY parcelas.parcela SEPARATOR ', ') FROM parcelas WHERE parcelas.situacao = '0' AND parcelas.vencimento < '" + DateTime.Now.ToString("yyyyMMdd") + "' AND parcelas.id_contrato = contratos.id_contrato) AS 'parcelas_em_atraso', (SELECT parcelas.parcela FROM parcelas WHERE parcelas.vencimento >= '" + DateTime.Now.ToString("yyyyMMdd") + "' AND parcelas.situacao = '0' AND parcelas.id_contrato = contratos.id_contrato LIMIT 1) AS 'proxima_parcela_parcela', (SELECT DATE_FORMAT(parcelas.vencimento, '%d/%m/%Y') FROM parcelas WHERE parcelas.vencimento >= '" + DateTime.Now.ToString("yyyyMMdd") + "' AND parcelas.situacao = '0' AND parcelas.id_contrato = contratos.id_contrato LIMIT 1 )  AS 'proxima_parcela_vencimento' FROM contratos LEFT JOIN clientes ON contratos.id_cliente = clientes.id_cliente LEFT JOIN sorteios ON contratos.id_contrato = sorteios.id_contrato LEFT JOIN parcelas ON parcelas.id_contrato = contratos.id_contrato LEFT JOIN produtos ON contratos.id_produto = produtos.id_produto WHERE contratos.id_contrato = '" + Codigo_1 + "'"

    Ela possui algumas subqueries e aninha algumas tabelas do banco por suas chaves estrangeiras... algo bastante comum... como ficaria essa mesma query em LINQ? E, caso não seja possível fazer com LINQ, como ficaria usando o Entitie Data Model?

    Um grande abraço a todos, fiquem com Deus.


    Se a resposta foi útil, por favor marque como útil. Leia a bíblia.
    quarta-feira, 21 de abril de 2010 01:16

Respostas

  • Cristiano as entidades devem estar em memória. O que você pode fazer é bem parecido como faria no sql.

    Exemplo:

     

    var Resultado = (from ent1 in Colecao1

     

    from ent2 in Colecao2

     

    where ent1.ID == ent2.ID && ent1.Campo1 == ent2.Campo2 && ent1.Campo3 == txtCampo3.Text

     

    select new { ent1.Campo1, ent2.Campo2});

    depois pode continuar utilizando filtros com lambda, exemplo:

    Resultado = Resulto.Where(p => p.CodigoCliente == int.Parse(txtCodigoCliente.Text));

    Resultado = Resulto.Where(p => p.DataNascimento >= Convert.ToDateTime(txtDataNascimento.Text));

    e assim por diante


    Minato alexandre.minato@hotmail.com - www.alexandreminato.com.br
    quinta-feira, 29 de abril de 2010 01:17

Todas as Respostas

  • Você pode passar esse seu cógido para uma SP e chamar via LINQ.
    quinta-feira, 22 de abril de 2010 12:11
  • Mas aí eu teria + trabalho e vez de produtividade, q é uma das propostas do LINQ.

    Teria que criar parametros de entrada e criar a stored rocedure no banco de dados.

    Depois utilizar o LINQ pra chamar a SP.

    É inviável. Pra q o LINQ seja atrativo ele tem q ser capaz de reorduzir queryz como esta.

    Já li em vários lugares da internet mas nao achei a resposta ainda pra este problema.

    Em tese: quanto mais complexo o SQL mais difícil, ou impossível, criar o equivalente em LINQ.

    Vou continuar esperando pra ver se alguem sabe fazer.

    Grande abraço gente.


    Se a resposta foi útil, por favor marque como útil. Leia a bíblia.
    quinta-feira, 22 de abril de 2010 12:34
  • Cristiano, pelo contrario, teu programa ficaria mais enxuto taria uma manutenção mais fácil, pois qualquer alteração do select seria diretamente no Banco e não na tua aplicação, além do mais se essa consulta for muito requisitada o trânsito de informações entre a tua aplicação e o BD aumenta consideravelmente.

    Atualmente minha aplicação para operações CRUD são 100% LINQ e todas via SP.

    O LINQ não foi desenvolvido para substituir o SQL, ele veio só facilitar o espelhamento do modelo Entidade-Relacionamento do Banco de Dados para o modelo de classes em uma aplicação.

    Acredito que o restante do código posso ser criando em LINQ mas a parte em negrito nem imagino com possa ser feito:

    SELECT
        contratos.id_contrato,
        contratos.senha,
        contratos.id_grupo,
        clientes.nome AS 'nome_cliente',
        produtos.nome_produto,
        (
            SELECT
                parcelas.parcela
            FROM
                parcelas
            WHERE
                parcelas.vencimento <= sorteios.data_sorteio
            AND parcelas.id_contrato = contratos.id_contrato
            ORDER BY
                parcelas.parcela DESC LIMIT 1
        ) AS 'parcela_sorteada',
        (
            SELECT
                DATE_FORMAT(parcelas.vencimento, '%d/%m/%Y')
            FROM
                parcelas
            WHERE
                parcelas.vencimento <= sorteios.data_sorteio
            AND parcelas.id_contrato = contratos.id_contrato
            ORDER BY
                parcelas.parcela DESC LIMIT 1
        ) AS 'parcela_sorteada_vencimento',
        (
            SELECT
                DATE_FORMAT(sorteios.data_sorteio, '%d/%m/%Y')
            FROM
                parcelas
            WHERE
                parcelas.vencimento <= sorteios.data_sorteio
            AND sorteios.id_contrato = contratos.id_contrato
            ORDER BY
                parcelas.parcela DESC LIMIT 1
        ) AS 'sorteada_em',
        (
            SELECT
                COUNT(parcelas.parcela)
            FROM
                parcelas
            WHERE
                parcelas.situacao = '1'
            AND parcelas.id_contrato = contratos.id_contrato
        ) AS 'parcelas_pagas',
        (
            SELECT
                GROUP_CONCAT(CONVERT(parcelas.parcela, CHAR(8)), DATE_FORMAT(parcelas.vencimento, 'ª-%d/%m/%Y')
            ORDER BY
                parcelas.parcela SEPARATOR ', ')
            FROM
                parcelas
            WHERE
                parcelas.situacao = '0'
            AND parcelas.vencimento < '" + DateTime.Now.ToString("yyyyMMdd") + "'
            AND parcelas.id_contrato = contratos.id_contrato
        ) AS 'parcelas_em_atraso'
    ,
        (
            SELECT
                parcelas.parcela
            FROM
                parcelas
            WHERE
                parcelas.vencimento >= '" + DateTime.Now.ToString("yyyyMMdd") + "'
            AND parcelas.situacao = '0'
            AND parcelas.id_contrato = contratos.id_contrato LIMIT 1
        ) AS 'proxima_parcela_parcela',
        (
            SELECT
                DATE_FORMAT(parcelas.vencimento, '%d/%m/%Y')
            FROM
                parcelas
            WHERE
                parcelas.vencimento >= '" + DateTime.Now.ToString("yyyyMMdd") + "'
            AND parcelas.situacao = '0'
            AND parcelas.id_contrato = contratos.id_contrato LIMIT 1
        ) AS 'proxima_parcela_vencimento'
    FROM
        contratos
    LEFT JOIN clientes
    ON
        contratos.id_cliente = clientes.id_cliente
    LEFT JOIN sorteios
    ON
        contratos.id_contrato = sorteios.id_contrato
    LEFT JOIN parcelas
    ON
        parcelas.id_contrato = contratos.id_contrato
    LEFT JOIN produtos
    ON
        contratos.id_produto = produtos.id_produto
    WHERE
        contratos.id_contrato = '" + Codigo_1 + "'

     

    quinta-feira, 22 de abril de 2010 13:21
  • Boa Marques, entendi.

    Gostei de vc ter dissecado a query... tirando o negrito, como ficaria essa consulta com o LINQ?

    A minha maior dúvida é como fazer JOIN's e subquerys no LINQ.


    Se a resposta foi útil, por favor marque como útil. Leia a bíblia.
    quinta-feira, 22 de abril de 2010 14:41
  • Cristiano as entidades devem estar em memória. O que você pode fazer é bem parecido como faria no sql.

    Exemplo:

     

    var Resultado = (from ent1 in Colecao1

     

    from ent2 in Colecao2

     

    where ent1.ID == ent2.ID && ent1.Campo1 == ent2.Campo2 && ent1.Campo3 == txtCampo3.Text

     

    select new { ent1.Campo1, ent2.Campo2});

    depois pode continuar utilizando filtros com lambda, exemplo:

    Resultado = Resulto.Where(p => p.CodigoCliente == int.Parse(txtCodigoCliente.Text));

    Resultado = Resulto.Where(p => p.DataNascimento >= Convert.ToDateTime(txtDataNascimento.Text));

    e assim por diante


    Minato alexandre.minato@hotmail.com - www.alexandreminato.com.br
    quinta-feira, 29 de abril de 2010 01:17