I have the following LINQ query:
var queryEvents = (from p in db.cl_contact_event
where p.time_of_contact >= beginDate && p.time_of_contact < endDate
group p by p.contact_list_name into g
select new PenRawModel
{
listName = g.Key,
download = g.Max(a => a.total_number_of_records),
dials = g.Where(a => a.ov_dial_start_time != null).Count(),
agentConnects = g.Sum(a => a.ov_trunk_released_time != null ? 1 : 0),
abandons = g.Sum(a => a.response_status == "DAC" || a.response_status == "DAD" ? 1 : 0),
rightPartyContacts = g.Sum(a => a.response_status == "PTP" || a.response_status == "RPC" ? 1 : 0),
promiseToPays = g.Sum(a => a.response_status == "PTP" ? 1 : 0),
talkTime = g.Sum(a => EntityFunctions.DiffSeconds(a.ov_call_connected_time, a.ov_trunk_released_time)) ?? 0,
wrapTime = g.Sum(a => EntityFunctions.DiffSeconds(a.ov_trunk_released_time, a.record_released_time)) ?? 0
}
On startup, it gives me an error:
"The column prefix 'Project1' does not match the table name or the alias name used in the query. Either the table is not specified in FROM or has a correlation name that should be used instead."
The reason for the refusal is:
dials = g.Where(a => a.ov_dial_start_time != null).Count(),
If I replace this line of code with the comment below, then the query works just fine. I would rather use .Where / .Count, because it's easier for someone to decrypt and understand the intent of the code.
Can someone help me make it clear why this fails and how can I fix it?
- SQL, Sybase :
SELECT
1 AS [C1],
[Project2].[contact_list_name] AS [contact_list_name],
[Project2].[C1] AS [C2],
[Project2].[C10] AS [C3],
[Project2].[C2] AS [C4],
[Project2].[C3] AS [C5],
[Project2].[C4] AS [C6],
[Project2].[C5] AS [C7],
CASE WHEN ([Project2].[C6] IS NULL) THEN 0 ELSE [Project2].[C7] END AS [C8],
CASE WHEN ([Project2].[C8] IS NULL) THEN 0 ELSE [Project2].[C9] END AS [C9]
FROM ( SELECT
[Project1].[C1] AS [C1],
[Project1].[C2] AS [C2],
[Project1].[C3] AS [C3],
[Project1].[C4] AS [C4],
[Project1].[C5] AS [C5],
[Project1].[C6] AS [C6],
[Project1].[C7] AS [C7],
[Project1].[C8] AS [C8],
[Project1].[C9] AS [C9],
[Project1].[contact_list_name] AS [contact_list_name],
(SELECT
Count([Filter2].[A1]) AS [A1]
FROM ( SELECT
1 AS [A1]
FROM [mel].[cl_contact_event] AS [Extent2]
WHERE ((([Extent2].[time_of_contact] >= @p__linq__0) AND ([Extent2].[time_of_contact] < @p__linq__1)) AND (([Project1].[contact_list_name] = [Extent2].[contact_list_name]) OR (([Project1].[contact_list_name] IS NULL) AND ([Extent2].[contact_list_name] IS NULL)))) AND ([Extent2].[ov_dial_start_time] IS NOT NULL)
) AS [Filter2]) AS [C10]
FROM ( SELECT
[GroupBy1].[A1] AS [C1],
[GroupBy1].[A2] AS [C2],
[GroupBy1].[A3] AS [C3],
[GroupBy1].[A4] AS [C4],
[GroupBy1].[A5] AS [C5],
[GroupBy1].[A6] AS [C6],
[GroupBy1].[A7] AS [C7],
[GroupBy1].[A8] AS [C8],
[GroupBy1].[A9] AS [C9],
[GroupBy1].[K1] AS [contact_list_name]
FROM ( SELECT
[Filter1].[K1] AS [K1],
Max([Filter1].[A1]) AS [A1],
Sum([Filter1].[A2]) AS [A2],
Sum([Filter1].[A3]) AS [A3],
Sum([Filter1].[A4]) AS [A4],
Sum([Filter1].[A5]) AS [A5],
Sum([Filter1].[A6]) AS [A6],
Sum([Filter1].[A7]) AS [A7],
Sum([Filter1].[A8]) AS [A8],
Sum([Filter1].[A9]) AS [A9]
FROM ( SELECT
[Extent1].[contact_list_name] AS [K1],
[Extent1].[total_number_of_records] AS [A1],
CASE WHEN ([Extent1].[ov_trunk_released_time] IS NOT NULL) THEN 1 ELSE 0 END AS [A2],
CASE WHEN ((N'DAC' = [Extent1].[response_status]) OR (N'DAD' = [Extent1].[response_status])) THEN 1 ELSE 0 END AS [A3],
CASE WHEN ((N'PTP' = [Extent1].[response_status]) OR (N'RPC' = [Extent1].[response_status])) THEN 1 ELSE 0 END AS [A4],
CASE WHEN (N'PTP' = [Extent1].[response_status]) THEN 1 ELSE 0 END AS [A5],
DATEDIFF (second, [Extent1].[ov_call_connected_time], [Extent1].[ov_trunk_released_time]) AS [A6],
DATEDIFF (second, [Extent1].[ov_call_connected_time], [Extent1].[ov_trunk_released_time]) AS [A7],
DATEDIFF (second, [Extent1].[ov_trunk_released_time], [Extent1].[record_released_time]) AS [A8],
DATEDIFF (second, [Extent1].[ov_trunk_released_time], [Extent1].[record_released_time]) AS [A9]
FROM [mel].[cl_contact_event] AS [Extent1]
WHERE ([Extent1].[time_of_contact] >= @p__linq__0) AND ([Extent1].[time_of_contact] < @p__linq__1)
) AS [Filter1]
GROUP BY [K1]
) AS [GroupBy1]
) AS [Project1]
) AS [Project2]