在查询 List<T> 时出现了未处理的 OleDb 异常。

Question

如果我直接在SQL数据库上运行以下查询，它可以工作。

var result =
    from log in db.DeviceLogs
    let byId = 
        db.DeviceLogs.Where(item => item.UserId == log.UserId)
    let first =
        byId.First(item => item.LogDate == byId.Min(min => min.LogDate))
    where 
        log.Equals(first) || (log.LogDate - first.LogDate).Minutes > 5
    select log;

但是如果我对筛选过的列表进行运行，就会出现 OleDb Exception 错误。

var filteredDeviceLogs = db.DeviceLogs
                        .Where(s => s.LogDate.Value.Date >= StartDate.Date  
                                   && s.LogDate.Value.Date < EndDate.AddDays(1));

 var result =
        from log in filteredDeviceLogs 
        let byId = 
            db.DeviceLogs.Where(item => item.UserId == log.UserId)
        let first =
            byId.First(item => item.LogDate == byId.Min(min => min.LogDate))
        where 
            log.Equals(first) || (log.LogDate - first.LogDate).Minutes > 5
        select log;

bool status = result.Any();    //OleDb Unhandled Exception

异常详情： 未识别的 WHEN 关键字。 堆栈跟踪：

   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.OleDb.OleDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)
   at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)

我猜这可能是因为在转换中使用了CASE WHEN语句，但如果我复制查询，就没有CASE WHEN语句。

Linq查询

SELECT [t0].[DeviceLogId], [t0].[DownloadDate], [t0].[DeviceId], [t0].[UserId], [t0].[LogDate], [t0].[Direction], [t0].[AttDirection], [t0].[C1], [t0].[C2], [t0].[C3], [t0].[C4], [t0].[C5], [t0].[C6], [t0].[C7], [t0].[WorkCode]
FROM [DeviceLogs] AS [t0]
WHERE ((EXISTS(
    SELECT NULL AS [EMPTY]
    FROM (
        SELECT TOP (1) [t1].[DeviceLogId]
        FROM [DeviceLogs] AS [t1]
        WHERE ([t1].[LogDate] = ((
            SELECT MIN([t2].[LogDate])
            FROM [DeviceLogs] AS [t2]
            WHERE [t2].[UserId] = [t0].[UserId]
            ))) AND ([t1].[UserId] = [t0].[UserId])
        ) AS [t3]
    WHERE [t0].[DeviceLogId] = [t3].[DeviceLogId]
    )) OR ((CONVERT(Int,(CONVERT(BigInt,(CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, (
    SELECT [t12].[LogDate]
    FROM (
        SELECT TOP (1) [t10].[LogDate]
        FROM [DeviceLogs] AS [t10]
        WHERE ([t10].[LogDate] = ((
            SELECT MIN([t11].[LogDate])
            FROM [DeviceLogs] AS [t11]
            WHERE [t11].[UserId] = [t0].[UserId]
            ))) AND ([t10].[UserId] = [t0].[UserId])
        ) AS [t12]
    ), [t0].[LogDate]))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, (
    SELECT [t12].[LogDate]
    FROM (
        SELECT TOP (1) [t10].[LogDate]
        FROM [DeviceLogs] AS [t10]
        WHERE ([t10].[LogDate] = ((
            SELECT MIN([t11].[LogDate])
            FROM [DeviceLogs] AS [t11]
            WHERE [t11].[UserId] = [t0].[UserId]
            ))) AND ([t10].[UserId] = [t0].[UserId])
        ) AS [t12]
    ), [t0].[LogDate]), (
    SELECT [t12].[LogDate]
    FROM (
        SELECT TOP (1) [t10].[LogDate]
        FROM [DeviceLogs] AS [t10]
        WHERE ([t10].[LogDate] = ((
            SELECT MIN([t11].[LogDate])
            FROM [DeviceLogs] AS [t11]
            WHERE [t11].[UserId] = [t0].[UserId]
            ))) AND ([t10].[UserId] = [t0].[UserId])
        ) AS [t12]
    )), [t0].[LogDate])) * 10000)) / 600000000)) % 60)) > @p0)) AND (CONVERT(DATE, [t0].[LogDate]) >= @p1) AND (CONVERT(DATE, [t0].[LogDate]) < @p2)

- Marshal

Answer 1

我猜测您通过OLEDB访问的任何数据源都不支持Case语句。

Linq查询正在转换为SQL，其中包括WHEN关键字（可能作为CASE语句的一部分），但是OLEDB可以用于包装各种数据源，并非所有数据源都支持这种构造。

如果您正在访问SQL Server、MySQL、Oracle或其他主要数据库系统，则应该使用该系统的适当ADO.NET提供程序，而不是OLEDB。如果您正在访问其他数据源，例如Excel，则可能会遇到麻烦。如果您可以找出向数据源发出的SQL是什么，那么可能会提供更多解决此问题的信息。

- Daniel Renshaw

---

我也曾经做出了同样的假设，但是当我查看linq语句中的SQL查询部分时，绝对没有CASE WHEN语句！！ - Marshal

OLEDB提供程序是否可以进一步翻译和修改SQL以包括CASE语句？您正在访问哪个数据库/数据源？ - Daniel Renshaw

我正在使用 Microsoft.ACE.OLEDB.12.0 驱动程序访问 MS Access 数据库。 - Marshal