I wrote LINQ query using LinqPad to return users that should retrieve an invitation for a questionnaire. The query runs fine and returns the correct results.
However I need this query in SQL form. When I click on the SQL button in LinqPad it returns the SQL form but with a separate select statement for each individual record. Leaving me with an SQL query of 2000 lines.
This is my LINQ query
var result2 = AI_Conversations
.Where(f => f.End_DateTime.Value.Date == DateTime.Now.AddDays(-1).Date).GroupBy(f => f.Email)
.Where(f => f.GroupBy(z => z.Chatbot_Name).Count() == 1)
.ToList().Select(f =>
new
{
Name = f.LastOrDefault().Disp_Name,
f.LastOrDefault().Email,
Reference = f.LastOrDefault().Report_ConvNumber
});
result2.Dump();
Which in SQL returns the following
-- Region Parameters
DECLARE @p0 DateTime = '2017-06-07 00:00:00.000'
DECLARE @p1 Int = 1
-- EndRegion
SELECT [t1].[Email] AS [Key]
FROM (
SELECT [t0].[Email]
FROM [AI_Conversations] AS [t0]
WHERE CONVERT(DATE, [t0].[End_DateTime]) = @p0
GROUP BY [t0].[Email]
) AS [t1]
WHERE ((
SELECT COUNT(*)
FROM (
SELECT NULL AS [EMPTY]
FROM [AI_Conversations] AS [t2]
WHERE ((([t1].[Email] IS NULL) AND ([t2].[Email] IS NULL)) OR (([t1].[Email] IS NOT NULL) AND ([t2].[Email] IS NOT NULL) AND ([t1].[Email] = [t2].[Email]))) AND (CONVERT(DATE, [t2].[End_DateTime]) = @p0)
GROUP BY [t2].[Chatbot_Name]
) AS [t3]
)) = @p1
GO
And after the above statement another select statement is appended for each record returned by the linq query. (>200 times)
-- Region Parameters
DECLARE @p0 DateTime = '2017-06-07 00:00:00.000'
DECLARE @x1 VarChar(1000) = '(CENSORED)'
-- EndRegion
SELECT [t0].[rownames] AS [Rownames], [t0].[Report_ConvNumber], [t0].[Email], [t0].[AD_Num], [t0].[Disp_Name], [t0].[Start_DateTime], [t0].[End_DateTime], [t0].[NumOfMessages], [t0].[Duration], [t0].[Chatbot_Name], [t0].[feedback_score] AS [Feedback_score]
FROM [AI_Conversations] AS [t0]
WHERE (((@x1 IS NULL) AND ([t0].[Email] IS NULL)) OR ((@x1 IS NOT NULL) AND ([t0].[Email] IS NOT NULL) AND (@x1 = [t0].[Email]))) AND (CONVERT(DATE, [t0].[End_DateTime]) = @p0)
GO
I would expect the SQL query returned to look similar to this
-- Region Parameters
DECLARE @p0 DateTime = DATEADD(dd, -1, cast(Getdate() as date))
DECLARE @p1 Int = 1
-- EndRegion
SELECT [t1].[Email],
[t1].[Disp_Name] AS Name,
[t1].[Report_ConvNumber] AS Reference
FROM (
SELECT [t0].[Email],
[t0].[Disp_Name],
[t0].[Report_ConvNumber]
FROM [AI_Conversations] AS [t0]
WHERE CONVERT(DATE, [t0].[Start_DateTime]) = @p0
GROUP BY [t0].[Email],
[t0].[Disp_Name],
[t0].[Report_ConvNumber]
) AS [t1]
WHERE ((
SELECT COUNT(*)
FROM (
SELECT NULL AS [EMPTY]
FROM [AI_Conversations] AS [t2]
WHERE ((([t1].[Email] IS NULL) AND ([t2].[Email] IS NULL)) OR (([t1].[Email] IS NOT NULL) AND ([t2].[Email] IS NOT NULL) AND ([t1].[Email] = [t2].[Email]))) AND (CONVERT(DATE, [t2].[Start_DateTime]) = @p0)
GROUP BY [t2].[Chatbot_Name]
) AS [t3]
)) = @p1
GO
Is anyone able to tell me why LinqPad is returning such a verbose SQL query? How can I adjust the LINQ Query to return a 'simple' SQL query like my provided expectations?
It's an MSSQL 2014 server using Linqpad 5 if that matters
Edit:
After removing the .ToList() as Sgtmoore suggested I got the following SQL output which is more or less to my expectations.
-- Region Parameters
DECLARE @p0 DateTime = '2017-05-04 00:00:00.000'
DECLARE @p1 Int = 1
-- EndRegion
SELECT (
SELECT [t5].[AD_Num]
FROM (
SELECT TOP (1) [t4].[AD_Num]
FROM [AI_Conversations] AS [t4]
WHERE ((([t1].[Email] IS NULL) AND ([t4].[Email] IS NULL)) OR (([t1].[Email] IS NOT NULL) AND ([t4].[Email] IS NOT NULL) AND ([t1].[Email] = [t4].[Email]))) AND (CONVERT(DATE, [t4].[End_DateTime]) = @p0)
) AS [t5]
) AS [Name], (
SELECT [t7].[Email]
FROM (
SELECT TOP (1) [t6].[Email]
FROM [AI_Conversations] AS [t6]
WHERE ((([t1].[Email] IS NULL) AND ([t6].[Email] IS NULL)) OR (([t1].[Email] IS NOT NULL) AND ([t6].[Email] IS NOT NULL) AND ([t1].[Email] = [t6].[Email]))) AND (CONVERT(DATE, [t6].[End_DateTime]) = @p0)
) AS [t7]
) AS [Email], (
SELECT [t9].[Report_ConvNumber]
FROM (
SELECT TOP (1) [t8].[Report_ConvNumber]
FROM [AI_Conversations] AS [t8]
WHERE ((([t1].[Email] IS NULL) AND ([t8].[Email] IS NULL)) OR (([t1].[Email] IS NOT NULL) AND ([t8].[Email] IS NOT NULL) AND ([t1].[Email] = [t8].[Email]))) AND (CONVERT(DATE, [t8].[End_DateTime]) = @p0)
) AS [t9]
) AS [Reference]
FROM (
SELECT [t0].[Email]
FROM [AI_Conversations] AS [t0]
WHERE CONVERT(DATE, [t0].[End_DateTime]) = @p0
GROUP BY [t0].[Email]
) AS [t1]
WHERE ((
SELECT COUNT(*)
FROM (
SELECT NULL AS [EMPTY]
FROM [AI_Conversations] AS [t2]
WHERE ((([t1].[Email] IS NULL) AND ([t2].[Email] IS NULL)) OR (([t1].[Email] IS NOT NULL) AND ([t2].[Email] IS NOT NULL) AND ([t1].[Email] = [t2].[Email]))) AND (CONVERT(DATE, [t2].[End_DateTime]) = @p0)
GROUP BY [t2].[Chatbot_Name]
) AS [t3]
)) = @p1
I think the main issue is the .ToList() in the middle is the query. However if you remove that, the query will fail as Linq2Sql does not support LastOrDefault.
So I think you would need to use .FirstOrDefault() and if necessary reverse the sort order.
This produces simpler TSQL, but still isn't anything like your expected output. From that, it would look like you want to group by Email, Reference and Report_ConvNumber, ie something like
var result2 = AI_Conversations
.Where(f => f.End_DateTime.Value.Date == DateTime.Now.AddDays(-1).Date)
.GroupBy(f => new {f.Email, f.Reference, f.Report_ConvNumber)
.Where(f => f.GroupBy(z => z.Chatbot_Name).Count() == 1)
.Select(f => new
{
Name = f.Key.Disp_Name,
f.Key.Email,
Reference = f.KeyReport_ConvNumber
});
result2.Dump();