0

I am trying to convert below SQL query to LINQ,

SELECT  
    TD.*, RD.*
FROM    
    dbo.TransactionDetail TD
INNER JOIN 
    dbo.Measure M ON M.InternalID = TD.MetricCode 
LEFT OUTER JOIN 
    (SELECT 
         tmp.ID, tmp.ReportingDate, 1 AS Match
     FROM 
         tmp) AS RD ON RD.ID = M.Frequency 
                    AND RD.ReportingDate = TD.ReportingDate
WHERE   
    RD.Match IS NULL 
    AND TD.BatchID = 'e07f9855-b286-4406-9189-5cfb2a7914c8'

My Linq query looks like below,

Update : Added td and rd definition

var rd = (from tt in result
                  select new { ID = tt.Id, tt.ReportingDate });

        // inner join
        var td = TransactionDetail.Join(
            MesureTb,
            t => t.MetricCode,
            m => m.InternalId,
            (t, m) => new
            {
                t.Id,
                t.RowAction,
                t.BatchId,
                t.TrustCode,
                t.MetricCode,
                t.ReportingDate,
                t.Value,
                t.UpperBenchmark,
                t.LowerBenchmark,
                m.InternalId,
                Frequency = m.Frequency
            });


var result2 = from a in td//inner join already done in previous step
                      join b in rd
                      on new { ReportingDate = (DateTime)a.ReportingDate, ID = a.Frequency } equals new { b.ReportingDate, b.ID } into j
                      from b in j.DefaultIfEmpty()
                      where b == null && a.BatchId == batchId
                      select a.Id;

Could someone help me how I can write this in a simple and efficient way? It is basically selecting the Non matching rows where Match = null Update : The TD in Linq contains list of reporting dates ranging from 2009 to 2021 The Right table RD is a lookup table which has date values between 2010 to 2020 Expected out put : The query should return the values from TD table which has Reporting date < 2010 and > 2020

Example below :

enter image description here

Expected Output : As in above screen shot, I need values which have Match as NULL.

I hope this explains what I am trying to achieve.

Any help is appreciated.

Thanks in advance.

PUBG
  • 199
  • 2
  • 12
  • Can you please share some sample data and expected output? – SelvaS Mar 11 '19 at 16:58
  • Looks fine to me. Performance wise I would `from a.Where(p=> p.BatchId==xxxx) in td` so only one or a few rows are joined – Cleptus Mar 11 '19 at 16:58
  • @SelvaTS that SQL is similar to "not exists (select related table)" – Cleptus Mar 11 '19 at 17:00
  • Please provide some inputs and expected outputs. – Ido H Levi Mar 11 '19 at 17:04
  • Added some content since the input data is huge, I hope this helps to understand the problem I am facing, The above Linq returns ID of all 8 values as in screenshot. – PUBG Mar 11 '19 at 17:25
  • 1
    Perhaps my [SQL to LINQ recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you? – NetMage Mar 11 '19 at 18:57
  • You might need to show us `td` definition since the LINQ shouldn't return the matches. – NetMage Mar 11 '19 at 19:01
  • @NetMage I have added the td and rd definition above. the sql query returns the ID of the reporting date which has Match as Null as shown in screenshot. I am trying to achieve same in my code. – PUBG Mar 11 '19 at 21:07
  • 1
    Your LINQ query appears to be correct, it should return only the non-matching rows. Something is happening such that nothing matches in your LINQ query. – NetMage Mar 11 '19 at 21:40

0 Answers0