How to convert the following SQL statement to LINQ Method Syntax

Question

I am looking for assistance in converting the following SQL statement over to LINQ using EF6. I have spent what seems like forever looking at different examples and have not found anything that works. I have looked at linqer (no success there) i have looked at linqpad (it does not convert sql to linq)

The query below returns exactly what I'm looking for in SQL format, the goal is to return all columns of the table based based on the inner grouping (using the grouping as a DISTINCT Query) using the WHERE clause of the Group to filter the record set to what is required, and Joining the inner grouping on [CdrCallId] only to return records where the [CdrCallId] match.

SELECT ct1.StartTime, ct1.CdrCallID, ct1.CallingNumberId, ct1.CalledNumberId, ct1.ThreadSequence
FROM CallTransactions as ct1
join (select CdrCallID
      from CallTransactions as ct2 
      WHERE [StartTime] >= '10/1/2020 00:00:00 AM' AND [StartTime] <= '03/31/2021 00:00:00 AM' AND [CalledNumberId] = '1670589' OR [CallingNumberId] = '1670589' OR [DestinationNumberId] = '1670589' OR [TransferringNumberId] = '1670589' OR [KeyPartyNumberId] = '1670589'
      group by ct2.CdrCallID) ct2
on ct1.CdrCallID = ct2.CdrCallID

StartTime	CdrCallID	CallingNumberId	CalledNumberId	ThreadSequence
2020-11-02 12:49:34.007	995368-307-63751883929019	1670589	1658625	995368
2021-02-19 14:38:54.600	78900-050-63751893781085	1670589	1658625	78900
2020-10-27 09:58:15.007	704239-301-63751883392147	1663834	1667952	704239
2020-10-27 09:58:15.007	704239-301-63751883392147	1663834	1670589	704239
2020-10-27 09:57:14.007	704239-301-63751883392147	1663834	1667952	704239
2020-10-27 09:57:59.000	704239-301-63751883392147	1663834	1670589	704239
2020-11-02 10:15:06.007	497923-307-63751883688115	1663847	1670589	497923

I have been struggling to find the proper LINQ Method syntax to mimic the above query.

Answer 1

I think you have some flaw in your SQL and should be in fact like:

SELECT ct1.StartTime, ct1.CdrCallID, ct1.CallingNumberId, ct1.CalledNumberId, ct1.ThreadSequence 
FROM CallTransactions as ct1 
where exists 
(select *
    from CallTransactions as ct2 
     WHERE ct1.CdrCallID = ct2.CdrCallID and 
            (([StartTime] >= '20201001' AND [StartTime] <= '20210331') AND 
           ([CalledNumberId] = '1670589' OR 
           [CallingNumberId] = '1670589' OR 
           [DestinationNumberId] = '1670589' OR 
           [TransferringNumberId] = '1670589' OR 
           [KeyPartyNumberId] = '1670589'));

In Linq that would be like:

var start = new DateTime(2020,10,1);
var end = new DateTime(2021,4,1);
var numberId = "1670589";
var callIDs = ctx.CallTransactions
    .Where(x => x.StartTime >= start && x.StartTime < end && 
        (x.CalledNumberId == numberId ||
         x.CallingNumberId == numberId ||
         x.DestinationNumberId == numberId ||
         x.TransferringNumberId == numberId ||
         x.KeyPartNumberId == numberId))
    .Select(x => x.CdrCallId);

var result = ctx.CallTransactions.Where(x => callIDs.Any(ci => ci.CdrCallId == x.CdrCallId)
   .Select(ct1 => new {ct1.StartTime, ct1.CdrCallID, ct1.CallingNumberId, ct1.CalledNumberId, ct1.ThreadSequence});

PS: You don't really need to convert everything into EF Linq. You could call a raw SQL from Linq as well.