Databases
StackOverflow2013
Postsdbo
POWhat is the difference between the following queries? One is straight up SQL and the other is its equivalent in LINQ
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhat is the difference between the following queries? One is straight up SQL and the other is its equivalent in LINQ
    primarykey
    data
    text
    <p>I have a SQL query here that looks like this:</p> <pre><code>SELECT R.Extra3 AS 'Practice', SUM(DATEDIFF(s, R.Pickup, R.Hangup)) AS 'Seconds', COUNT(R.Extra3) AS 'Calls' FROM Outbound.dbo.Results R JOIN Outbound.dbo.Queue Q ON Q.QueueID = R.QueueID AND Q.Attempt = R.Attempt WHERE R.CampaignId = 1 AND DATEPART(m, R.Pickup) = DATEPART(m, DATEADD(m, -1, getdate())) AND DATEPART(y, R.Pickup) = DATEPART(y, DATEADD(m, -1, getdate())) GROUP BY R.Extra3 </code></pre> <p>I have to use this in a program and decided to go the LINQ route. So here is what I came up with:</p> <pre><code>IQueryable&lt;PracticeSummary&gt; query = db.Results .Join( db.Queues, r =&gt; new { Id = r.QueueID.Value, Attempt = r.Attempt.Value }, q =&gt; new { Id = q.QueueID, Attempt = (byte)q.Attempt }, (r, q) =&gt; r ) .Where( r =&gt; r.CampaignID == 1 &amp;&amp; r.PickUp.Value.Month == lastMonth &amp;&amp; r.PickUp.Value.Year == lastMonthYear ) .GroupBy(g =&gt; g.Extra3) .Select(r =&gt; new PracticeSummary { Practice = r.Key, Calls = r.Count(), Seconds = (r.Sum(item =&gt; EntityFunctions.DiffSeconds(item.PickUp, item.HangUp).Value)) }); </code></pre> <p>My SQL query is giving me the correct result while my LINQ query is returning more than 10 times the rows and as a result, the sum and count are more.</p> <p>I even looked at the TSQL that was generated. It looks like this:</p> <pre><code>SELECT 1 AS [C1], [GroupBy1].[K1] AS [Extra3], [GroupBy1].[A1] AS [C2], [GroupBy1].[A2] AS [C3] FROM ( SELECT [Filter1].[K1] AS [K1], COUNT([Filter1].[A1]) AS [A1], SUM([Filter1].[A2]) AS [A2] FROM ( SELECT [Extent1].[Extra3] AS [K1], 1 AS [A1], DATEDIFF (second, [Extent1].[PickUp], [Extent1].[HangUp]) AS [A2] FROM [dbo].[Results] AS [Extent1] INNER JOIN [dbo].[Queue] AS [Extent2] ON ([Extent1].[QueueID] = [Extent2].[QueueID]) AND (([Extent1].[Attempt] = CAST( [Extent2].[Attempt] AS tinyint)) OR (([Extent1].[Attempt] IS NULL) AND ( CAST( [Extent2].[Attempt] AS tinyint) IS NULL))) WHERE (1 = [Extent1].[CampaignID]) AND ((DATEPART (month, [Extent1].[PickUp])) = @p__linq__0) AND ((DATEPART (year, [Extent1].[PickUp])) = @p__linq__1) ) AS [Filter1] GROUP BY [K1] ) AS [GroupBy1] </code></pre> <p>As far as I can tell it is pretty similar to what I have and want.</p> <p>So my question is why are the results different? What is the difference between my SQL and LINQ queries? What am I missing?</p> <p>Thanks in advance for your time and effort!</p> <p>EDIT:</p> <p>Here are the classes for Queue &amp; Result:</p> <pre><code>public partial class Queue { public long QueueID { get; set; } public long CampaignID { get; set; } public int Attempt { get; set; } public System.DateTime StartTime { get; set; } public System.DateTime EndTime { get; set; } public string Extra1 { get; set; } public string Extra2 { get; set; } public string Extra3 { get; set; } } public partial class Result { public long ResultID { get; set; } public Nullable&lt;long&gt; QueueID { get; set; } public Nullable&lt;long&gt; CampaignID { get; set; } public Nullable&lt;byte&gt; Attempt { get; set; } public Nullable&lt;System.DateTime&gt; PickUp { get; set; } public Nullable&lt;System.DateTime&gt; HangUp { get; set; } public string Extra1 { get; set; } public string Extra2 { get; set; } public string Extra3 { get; set; } } </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USKarthic Raghupathi
    1. USKarthic Raghupathi
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. CODo you have records in your Q and R tables that have the matching QueueID but Attempt is NULL? Also, could you please post your classes for Q and R?
      singulars
      1. POWhat is the difference between the following queries? One is straight up SQL and the other is its equivalent in LINQ
      1. USIronMan84
    2. COPlease check the updated question. I have posted the classes. Also there are no records where Attempt is NULL.
      singulars
      1. POWhat is the difference between the following queries? One is straight up SQL and the other is its equivalent in LINQ
      1. USKarthic Raghupathi
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload