Converting SQL to LINQ C# to return data with most recent entry of a field

Question

I'm trying to convert this to LINQ C#. I am expecting a single result with the most recent data but the LINQ returns 3 results with the individual data.

SQL:

select * from ticket t 
  join tollBoothShift tbs on t.fkTollBoothShift = tbs.uniqueId
  join classification c on c.uniqueId = t.fkClassification 
  join classificationPrice cp on cp.fkClassification = c.uniqueId
  join person p on p.uniqueId = tbs.fkPerson
WHERE convert(date, t.entryDate) = '6/20/2018' 
  and (convert(time, t.entryDate) >= convert(time, '12:00') 
  and convert(time,  t.entryDate) <= convert(time, '15:00')) 
  and cp.entryDate = (SELECT MAX(entryDate) 
                     from classificationPrice
                     where classificationPrice.fkClassification = c.uniqueId 
                       and entryDate <= t.entryDate)
order by convert(time, t.entryDate)

I have attempted to do so here:

from t in context.Ticket
                       join c in context.Classification on t.FkClassification equals c.UniqueId
                       join cp in context.ClassificationPrice on c.UniqueId equals cp.FkClassification into cpc
                       let price = cpc.Where(f => f.FkClassification == c.UniqueId && f.EntryDate <= t.EntryDate).OrderByDescending(p => p.EntryDate).Take(1).FirstOrDefault().Price
                       join tbs in context.TollBoothShift on t.FkTollBoothShift equals tbs.UniqueId
                       join p in context.Person on tbs.FkPerson equals p.UniqueId
                       where t.EntryDate.Date == date.Date && (t.EntryDate.TimeOfDay >= startTime.TimeOfDay && t.EntryDate.TimeOfDay <= endTime.TimeOfDay) && t.Weight != null
                       orderby t.EntryDate.TimeOfDay
                       select
                       new WeighingLogReportModel
                       {
                           ticketNumber = t.UniqueId.ToString(),
                           entryDate = t.EntryDate,
                           entryTime = t.EntryDate.ToShortTimeString(),
                           tollBoothNumber = tbs.UniqueId.ToString(),
                           supervisor = "",
                           weight = t.Weight,
                           cost = price.ToString("N2"),
                           ownerDriver = string.Empty,
                           tollBoothShift = tbs.StartTime.ToShortTimeString() + " to " + tbs.EndTime.Value.ToShortTimeString() + "h",
                           licencePlatePrefix = t.LicencePlatePrefix,
                           licencePlateSuffix = t.LicencePlateSuffix
                       }

Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) would help you? — NetMage, Jul 20 '18 at 18:26

Vinit · Answer 1 · 2018-07-23T17:56:02.583

0

It seems, you missed the last condition in LINQ query

and cp.entryDate = (SELECT MAX(entryDate) 
                     from classificationPrice
                     where classificationPrice.fkClassification = c.uniqueId 
                       and entryDate <= t.entryDate)

You can covert this condition to LINQ and add it to existing where clause

cp.EntryDate == ClassificationPrice.Where(x=> x.FkClassification == c.UniqueId).Max(y=> y.EntryDate)

So your final where clause will be

where t.EntryDate.Date == date.Date 
      && (t.EntryDate.TimeOfDay >= startTime.TimeOfDay 
      && t.EntryDate.TimeOfDay <= endTime.TimeOfDay) 
      && t.Weight != null
      && cp.EntryDate == ClassificationPrice.Where(x=> x.FkClassification == c.UniqueId).Max(y=> y.EntryDate)

edited Jul 23 '18 at 17:56

answered Jul 20 '18 at 18:06

Vinit

2,540
1
15
22

Yes I did...I am finding it difficult to convert the LINQ to that...How would I achieve that? – Ggow Nick Jul 23 '18 at 12:57
@GgowNick I just tried to convert that SQL to linq condition using the notepad, and updated my answer. (might have some syntax errors), try that. – Vinit Jul 23 '18 at 17:57
I was able to solve the problem by performing another linq query as the sub query for the variable. – Ggow Nick Jul 24 '18 at 14:30

Converting SQL to LINQ C# to return data with most recent entry of a field

1 Answers1