0

I'm trying to rewrite following SQL query into LINQ:

SELECT `i`.`symbol`, `i`.`id`, `t0`.`close`, `t`.`close`, `t`.`close` - `t0`.`close`, (`t`.`close` - `t0`.`close`) / `t0`.`close`
FROM `investment` AS `i`
LEFT JOIN `investment_record` AS `t0` ON `t0`.id = (
    SELECT `i0`.id
    FROM `investment_record` AS `i0`
    WHERE (`i0`.`date` <= @dateFrom) AND i.id = i0.investment_id
    ORDER BY `i0`.`date` DESC
    LIMIT 1
)
LEFT JOIN `investment_record` AS `t` ON `t`.id =(
    SELECT `i0`.id
    FROM `investment_record` AS `i0`
    WHERE (`i0`.`date` <= @dateTo) AND i.id = i0.investment_id
    ORDER BY `i0`.`date` DESC
    LIMIT 1
) 

WHERE `i`.`id` IN (@id0, @id1, ....)

My main issues are the AND i.id = i0.investment_id and LIMIT 1 parts of JOINs.

Currently the best I could achieve is this:

from inv in _context.Investment
join recTo in _context.InvestmentRecord on inv.Id equals recTo.InvestmentId into recToColl
from recToNullable in recToColl.Where(x => x.Date <= dateTo).OrderByDescending(x => x.Date).Take(1).DefaultIfEmpty()
join recFrom in _context.InvestmentRecord on inv.Id equals recFrom.InvestmentId into recFromColl
from recFromNullable in recFromColl.Where(x => x.Date <= dateFrom).OrderByDescending(x => x.Date).Take(1).DefaultIfEmpty()
where investmentIds.Contains(inv.Id)
let amountFrom = recFromNullable.Close
let amountTo = recToNullable.Close
select new InvestmentPerformance(
  inv.Symbol,
  inv.Id,
  amountFrom,
  amountTo,
  amountTo - amountFrom,
  (amountTo - amountFrom) / amountFrom
);

but the problem is it doesn't work.

It gives the expression cannot be translated exception:

System.InvalidOperationException: The LINQ expression 'DbSet() .GroupJoin( inner: DbSet(), outerKeySelector: inv => inv.Id, innerKeySelector: recTo => recTo.InvestmentId, resultSelector: (inv, recToColl) => new { inv = inv, recToColl = recToColl })' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'A sEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Point of this ugly SQL (and LINQ) is to calculate performance of investment for given time interval. User is able to specify from-to dates. Problem is sometimes user could specify date without any records (bank holiday for example). So for given date, I want to use the closest previous record (that is the reason for <= @dateFrom conditions and ORDER BY date DESC LIMIT 1 parts of the SQL.

I tried many variations of the LINQ with different forms of joins, but none of them worked as I need :(

I'm using EF.Core 5 and MySQL database.

Klinki
  • 1,399
  • 3
  • 15
  • 33
  • Why not just look up the closest valid date as s precursor to doing the main query ? It's `select x order by x desc limit 1` basically a `select max`, by the way – Caius Jard Jun 30 '21 at 19:23
  • If you already have the query in SQL working, I seriously would recommend just saving that query as a stored procedure or view and using that in EF Core. I use stored procedures whenever my query logic becomes complicated – Chad K Jun 30 '21 at 19:39
  • Also, I'm not familiar with your schema or exactly what you're trying to do, but the fact that you're doing a sub-query and a Limit 1 on your join makes me wonder if you should be looking at doing an OUTER APPLY instead – Chad K Jun 30 '21 at 19:40
  • I see your point, I use MS SQL at work a lot and I'd also use something like that, but I don't think OUTER APPLY is supported by MySQL :( – Klinki Jun 30 '21 at 20:29
  • This definitely should be either `OUTER APPLY`, or `LEFT JOIN` with `ROW_NUMBER`, the current code is wrong/inefficient anyway – Charlieface Jun 30 '21 at 20:40
  • If you have better implementation, please free to share it, but `OUTER APPLY` is not supported by MySQL. – Klinki Jun 30 '21 at 21:08

1 Answers1

0

The original SQL query seems complex to me. I would already rewrite it using a OUTER APPLY instead of sub-join queries.

SELECT `i`.`symbol`, `i`.`id`, `t0`.`close`, `t`.`close`, `t`.`close` - `t0`.`close`, (`t`.`close` - `t0`.`close`) / `t0`.`close`
FROM `investment` AS `i`
OUTER APPLY (
  SELECT `i0`.id
  FROM `investment_record` AS `i0`
  WHERE (`i0`.`date` <= @dateFrom) AND i.id = i0.investment_id
  ORDER BY `i0`.`date` DESC
  LIMIT 1
) AS t0
OUTER APPLY (
  SELECT `i0`.id
  FROM `investment_record` AS `i0`
  WHERE (`i0`.`date` <= @dateTo) AND i.id = i0.investment_id
  ORDER BY `i0`.`date` DESC
  LIMIT 1
) AS t

WHERE `i`.`id` IN (@id0, @id1, ....)

Then I would translate this using EF's way to write OUTER APPLY. This SO post might be of help.

It would look something like this:

from inv in _context.Investments
from rec1 in _context.InvestmentsRecords.Where(ir => ir.InvestmentId = inv.InvestmentId).Where(ir => ir.Date <= DateFrom).OrderByDescending().Take(1)
from rec1 in _context.InvestmentsRecords.Where(ir => ir.InvestmentId = inv.InvestmentId).Where(ir => ir.Date <= DateTo).OrderByDescending().Take(1)
...
jdebarochez
  • 1