C# subquery in LINQ select-CodePudding

I'm trying to convert this query to LINQ, but I'm not getting the value of the subquery

QUERY:

select c.cod, c.cpfcnpj, c.razaosocial, c.nome, c.fone, c.celular, c.email, c.dtcad, s.dataval as validade,
(select max(datapagamento) from vendas where c.cod = coduser) as datapag
from usuarios c, libsys s
WHERE c.cod = s.codcli
and c.cod in (select coduser from vendas)
AND c.cod in (select l.codcli from libsys l where l.dataval >= current_date)
order by c.dtcad asc

LINQ:

        var rel = await (from u in _contexto.usuarios
                         from v in _contexto.libsys
                         where (
                                 (u.cod == v.codcli) &&
                                 _contexto.vendas.Any(y => y.coduser == u.cod) &&
                                 _contexto.libsys.Any(y => y.codcli == u.cod && y.pcpdataval >= System.DateTime.Now)
                               )
                         select new RelatorioLicsModel
                         {
                             
                             cod = u.cod,
                             cpfcnpj = u.cpfcnpj,
                             razaosocial = u.razaosocial,
                             nome = u.nome,
                             fone = u.fone,
                             celular = u.celular,
                             email = u.email,
                             dtcad = u.dtcad,
                             validade = v.pcpdataval.ToString(),
                             dtpag = Convert.ToDateTime(_contexto.vendas.Where(s => s.datapagamento == _contexto.vendas.Max(x => x.datapagamento) && s.coduser == u.cod).FirstOrDefault())
                         }).ToListAsync();

the error I get is:

{"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY v.coduser ORDER BY v.cod) AS row\r\n FROM vendas ' at line 7"}

is this the correct way to do this? thanks for any help!

CodePudding user response：

Problem with dtpag property.

var query = 
    from u in _contexto.usuarios
    from v in _contexto.libsys
    where (
            (u.cod == v.codcli) &&
            _contexto.vendas.Any(y => y.coduser == u.cod) &&
            _contexto.libsys.Any(y => y.codcli == u.cod && y.pcpdataval >= System.DateTime.Now)
        )
    select new RelatorioLicsModel
    {
        
        cod = u.cod,
        cpfcnpj = u.cpfcnpj,
        razaosocial = u.razaosocial,
        nome = u.nome,
        fone = u.fone,
        celular = u.celular,
        email = u.email,
        dtcad = u.dtcad,
        validade = v.pcpdataval.ToString(),
        dtpag = Convert.ToDateTime(_contexto.vendas.Where(s => s.coduser == u.cod).Max(x => x.datapagamento))
    }