我有下一个SQL本机查询： 

select      a.id_agente,     a.alias,     a.direccion,     cd.description,      (   select te.data          from tevento te          left join tagente ta on ta.id_agente = te.id_agente          where ta.id_agente = a.id_agente order by timestamp desc limit 1     ) as data from tagente a  left join tagent_custom_data cd on a.id_agente = cd.id_agent  where cd.id_field = 6 and cd.description = $VAR;

我在这样的控制器的.net核心中有此查询： 

    [HttpGet]     public ActionResult<string> GetAgentesByPlanta(string idPlanta)     {         using (var db = new MyContext())         {             List<Object> lst = new List<Object>();              var q =                 from a in db.Agente                 join cd in db.CustomData on a.id_agente equals cd.id_agent                  where ((cd.id_field == 6) & (cd.description == idPlanta))                 select new { Agente = a, CustomData = cd };              foreach (var x in q)             {                 lst.Add(new {                     id_agente=x.Agente.id_agente,                     nombre=x.Agente.nombre,                     direccion=x.Agente.direccion,                     alias=x.Agente.alias,                     ultimo_contacto=x.Agente.ultimo_contacto                 });             }              dynamic response = lst;          return Ok(response);         }     }

该控制器以json响应，并且可以正常工作。 但是如您所见，select的子查询丢失了。

¿如何在此.NET Core查询中添加子查询？

        

===============>>#1 票数：0

我终于以这种方式解决了这个疑问： 

             var q =                 from a in db.Agente                 join cd in db.CustomData on a.id_agente equals cd.id_agent                  where ((cd.id_field == 6) && (cd.description == idConvert))                 select new {                      Agente = a,                      CustomData = cd,                     Evento = (from te in db.Evento                             join ta in db.Agente on te.id_agente equals ta.id_agente                             where ta.id_agente == a.id_agente                             orderby te.timestamp descending                             select new {Evento = te}).First()                 };              foreach (var x in q)             {                 lst.Add(new {                     id_agente=x.Agente.id_agente,                     nombre=x.Agente.nombre,                     direccion=x.Agente.direccion,                     alias=x.Agente.alias,                     ultimo_contacto=x.Agente.ultimo_contacto,                     data=x.Evento.Evento.data,                     ultimo_data=x.Evento.Evento.timestamp                 });             }

  ask by baquilare translate from so

===============>>#2 票数：0 已采纳

您可以在此处使用“急切加载”，修改@baquilare，以供参考 

 var q =   from a in db.Agente                 join cd in db.CustomData on a.id_agente equals cd.id_agent                  join te in db.Evento.AsQueryable().Include(x=>x.Agente) on                   te.Agente.id_agente == a.id_agente                 where ((cd.id_field == 6) && (cd.description == idConvert))                 select new {                               Agente = a,                               CustomData = cd,                              Evento =te.OrderByDescending(x=>x.timestamp).FirstOrDefault()                 };

在这里我再次不知道时间戳在哪个表中+它比当前代码更优化，但是可以改进很多...使用ef或efcore的简单建议我将始终建议使用您可能需要system.Linq的存储库模式命名空间

如果您对efcore的要求不严格，也可以使用sp并通过efcore执行它，对于这样的查询更好

  ask by baquilare translate from so

本文未有回复，本站智能推荐：