Translate complex query to linq

Question

I am trying to use the following query with Entity framework, that is why I am trying to use linq.

SELECT DISTINCT
  elt.Type,
  ( SELECT TOP 1 Value
    from ELD
    where ELDTID = 2 AND ELID = el.ELID
  ) ID,
  ( SELECT TOP 1 Value
    from ELD
    Where ELDTID = 1 AND ELID = el.ELID
  ) Company,
  ( SELECT TOP 1 Value
    from ELD
    Where ELDTID = 5 AND ELID = el.EventLogID
  ) Message,
  ( SELECT Max(ET)
    FROM EL el
    INNER JOIN ELD eld ON eld.ELID = el.ELTID
    WHERE el.ELID = el.ELID
  ) ET
FROM EL el
INNER JOIN ITS.ELT elt ON elt.ELTID = el.ELTID
WHERE ELSID = 3

Answer 1

• It's not visible from the example whether ELSID is a EL or ELT attribute, assuming ELT.

• I think to have spotted a mistake in your ET subquery: where el.ELID = el.ELID. You probably want to reference the el from the outer query on one side, but can't because you're using the same name. Used el1 in this answer for the subquery EL.

Tried to create a matching data model (next time please provide us with one, as you'll probably have it already).

public class Elt
{
    public int ELTID;
    public int ELSID;
    public int Type;
}

public class El
{
    public int ELID;
    public int ELTID;
    public int EventLogID;
}

public class Eld
{
    public int ELDTID;
    public int ELID;
    public int ET;
    public int Value;
}

public class Db
{
    public IQueryable<El> Els;
    public IQueryable<Elt> Elts;
    public IQueryable<Eld> Elds;
}

Your SQL then should correspond to this query:

from el in db.Els
join elt in db.Elts on el.ELTID equals elt.ELTID
where elt.ELSID == 3
select new
{
    elt.Type,
    ID = (from eld in db.Elds where eld.ELDTID == 2 && eld.ELID == el.ELID select eld.Value).FirstOrDefault(),
    Company = (from eld in db.Elds where eld.ELDTID == 1 && eld.ELID == el.ELID select eld.Value).FirstOrDefault(),
    Message = (from eld in db.Elds where eld.ELDTID == 5 && eld.ELID == el.EventLogID select eld.Value).FirstOrDefault(),
    ET = (
        from el1 in db.Els
        join eld in db.Elds on el1.ELTID equals eld.ELDTID
        where el1.ELID == el.ELID
        select eld)
        .Max(eld => eld.ET),
};

If you don't have navigation properties (child collection of ELs on ELT, child collection of ELDs on EL, parent properties on EL and ELD) I'd suggest you add them, which would simplify the query further (join conditions injected by EF):

from elt in db.Elts
from el in elt.Els
...