LINQ - select statement in the selected column

Question

i am intend to convert the following query into linQ

SELECT TOP 100 S.TxID, 
    ToEmail, 
    [Subject], 
    ProcessedDate,
    [Status] = (CASE WHEN EXISTS (SELECT TxID FROM TxBounceTracking
                            WHERE TxID = S.TxID)
                THEN 'Bounced'
                WHEN EXISTS (SELECT TxID FROM TxOpenTracking
                            WHERE TxID = S.TxID)
                THEN 'Opened'
                ELSE 'Sent' END)
FROM TxSubmissions S
WHERE S.UserID = @UserID 
AND ProcessedDate BETWEEN @StartDate AND @EndDate
ORDER BY ProcessedDate DESC

The following code is the linq that i converted.

v = (from a in dc.TxSubmissions
where a.ProcessedDate >= datefrom && a.ProcessedDate <= dateto && a.UserID == userId
let bounce = (from up in dc.TxBounceTrackings where up.TxID == a.TxID select up)
let track = (from up in dc.TxOpenTrackings where up.TxID == a.TxID select up)
select new { a.TxID, a.ToEmail, a.Subject, 
    Status = bounce.Count() > 0 ? "Bounced" : track.Count() > 0 ? "Opened" : "Sent", 
    a.ProcessedDate });

However this linq is too slow because the bounce and track table, how should i change the linq query to select one row only to match the SQL query above >>

SELECT TxID FROM TxOpenTracking WHERE TxID = S.TxID

in my selected column, so it can execute faster.

Note that the record contained one million records, thats why it lag

To match the SQL query, replace `Count() > 0` calls with `Any()`. Also add at the end `OrderByDescending(r => r.ProcessedDate).Take(100)`. — Ivan Stoev, Mar 13 '17 at 09:55

score 1 · Answer 1 · edited May 23 '17 at 12:00

1

As you don't care about readability because you will end up generating the query via EF you can try to join with those two tables. (it looks that TxID is a FK or a PK/FK)

More about JOIN vs SUB-QUERY here: Join vs. sub-query

Basically your SQL looks like this:

SELECT TOP 100 S.TxID, ToEmail, [Subject], ProcessedDate,
                [Status] = (CASE WHEN BT.TxID IS NOT NULL
                                 THEN 'Bounced'
                                 WHEN OP.TxID IS NOT NULL
                                 THEN 'Opened'
                                 ELSE 'Sent' END)
            FROM TxSubmissions S
                 LEFT JOIN TxBounceTracking BT ON S.TxID = BT.TxID
                 LEFT JOIN TxOpenTracking OP ON S.TxID = OP.TxID
                WHERE S.UserID = @UserID 
                AND ProcessedDate BETWEEN @StartDate AND @EndDate
            ORDER BY ProcessedDate DESC

And then, you can try to convert it to LINQ something like:

v = (from subs in dc.TxSubmissions.Where(sub => sub.ProcessedDate >= datefrom && sub.ProcessedDate <= dateto && sub.UserID == userId)
from bts in dc.TxBounceTrackings.Where(bt => bt.TxID == subs.TxID).DefaultIfEmpty()
from ots in dc.TxOpenTrackings.Where(ot => ot.TxID == subs.TxID).DefaultIfEmpty()
select new {   });

More about left join in linq here: LEFT JOIN in LINQ to entities?

Also if you remove default if empty you'll get a inner join.

Also you need to take a look at generated SQL in both cases.

edited May 23 '17 at 12:00

Community

1
1

answered Mar 13 '17 at 09:34

Razvan Dumitru

11,815
5
34
54

hi dear this is my statement, v = (from subs in dc.TxSubmissions.Where(sub => sub.ProcessedDate >= datefrom && sub.ProcessedDate <= dateto && sub.UserID == userId) from bts in dc.TxBounceTrackings.Where(bt => bt.TxID == subs.TxID).DefaultIfEmpty() from ots in dc.TxOpenTrackings.Where(ot => ot.TxID == subs.TxID).DefaultIfEmpty() select new { subs.TxID, subs.ToEmail, subs.Subject, Status = bts != null ? "Bounced" : ots != null ? "Opened" : "Sent", subs.ProcessedDate }); still lag – Ryan Shine Mar 14 '17 at 02:32
sorry for my mistake, the lag is due to the skip,var data = v.Skip(skip).Take(pageSize).ToList(); the skip keyword cause lag – Ryan Shine Mar 14 '17 at 03:01
Yep, your problem is related to offset fetch and is known. You can probably access first couple of rows in a good time but for higher pages you'll have a drawback. A stack question about this: http://stackoverflow.com/q/34531818/2659796 and an article with some tips about how you can redactor this: http://use-the-index-luke.com/no-offset – Razvan Dumitru Mar 14 '17 at 08:37
But you'll probably need to go back to sql as entity framework doesn't support that. For keeping the query alive with entity, you can ask someone that's more like a dba to search for index improvements – Razvan Dumitru Mar 14 '17 at 08:39

LINQ - select statement in the selected column

1 Answers1