I have the folling 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(), //This fails.
//dials = g.Sum(a => a.ov_dial_start_time != null ? 1 : 0), //This works.
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
}
When run it gives me the error:
"The column prefix 'Project1' does not match with a table name or alias name used in the query. Either the table is not specified in the FROM clause or it has a correlation name which must be used instead."
The reason for the failure is:
dials = g.Where(a => a.ov_dial_start_time != null).Count(),
If I replace that line of code with the commented out one below it then the query works just fine. I'd prefer to use the .Where/.Count though because it's easier for someone else to decipher and understand the intent of the code.
Can anyone help give me a clue as to why this fails and how I might possibly fix it?
Edit- Here is the SQL that is sent to the Sybase database from the failed query:
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]
FULLY EDITED ANSWER
The problem was finally in the translation from LINQ to SQL of the Sybex ASE provider:
The queries should be well rendered using any LINQ sintax. They could produce different queries, but they all should work. However this provider fails to do it right with some of the possible sintax.
Is good to know that:
dials = g.Where(a => a.ov_dial_start_time != null).Count(), // fails.
dials = g.Count(a => a.ov_dial_start_time != null), // also fails.
dials = g.Sum(a => a.ov_dial_start_time != null ? 1 : 0), // works.
According to OP, adding AsEnumerable() before the Where() filter also makes it work.
If you find some failure with this database and provider it's a good idea to check the query that will be sent to the server to find the offending part and try alternative sintax until the problem is solved. You can check the query that will be sent to the server using toString(). Then you can check the sintax, or run it directly in the server and see what is failing.