SingleOrDefault or FirstOrDefault does not work

[jeremy-morren]

The Linq Function "DefaultIfEmpty" does not work with JetEntityFrameworkProvider. Upon investigation, the reason is that EntityFramework uses "LEFT OUTER JOIN" in the expression, which is not supported in Access.

For Example:

//Attempts to use "LEFT OUTER JOIN"
//Will throw Exception "Join Expression not supported"
Entity.Table.DefaultIfEmpty(null).FirstOrDefault(e => e.Id = 1)

As a caveat, this is a problem with Access, not JetEntityFrameworkProvider. The workaround I am using is the following extension method:

namespace System.Linq
{
    public static class LinqExtensions
    {
        /// 

        /// Workaround for 
        /// not working with JetEntity
        /// 
        /// First Value, otherwise default()
        public static TSource FirstOrEmpty(this IQueryable source, Expressions.Expression> predicate)
        {
            IQueryable result = source.Where(predicate);
            if (result.Count() == 0)
                return default(TSource);
            return result.First();
        }
    }
}

[bubibubi]

Thanks for the workaround!

About your issue, the queries are generated by the provider not by Entity Framework. Jet + OleDb should support LEFT OUTER JOIN (the syntax is slightly different from Microsoft Access user interface). To understand the issue you can enable SQL Logging setting JetConnection.ShowSqlStatements = true. Thinking about how DefaultIfEmpty could work, it could be related to DUAL table (Jet does not support query like SELECT 10, is similar to Oracle in this behaviour).

[jeremy-morren]

About JetEntity generating the SQL, I later took a look around the repository and noticed that (I'm new to Open source).

If it's any help, the Sql Generated is as follows:

SELECT TOP 2
[Extent1].[Id] AS [Id],
[Extent1].[Description] AS [Description],
[Extent1].[ReportId] AS [ReportId]
FROM (  ( SELECT 1 AS X FROM (SELECT COUNT(*) FROM MSysAccessStorage) ) AS [SingleRowTable1]
LEFT OUTER JOIN [ChartOfAccountTypes] AS [Extent1] ON (true = true))
WHERE ([Extent1].[Description] = @p__linq__0) OR (([Extent1].[Description] IS NULL) AND (@p__linq__0 IS NULL))
-- p__linq__0: 'Sales' (Type = AnsiString, Size = 5)
-- Executing at 22/03/2019 11:14:28 AM -04:00

It seems the error is actually JOIN expression not supported, which may have something to do with the (true = true) part.

[bubibubi]

I think so. There are several issue related to the use of DUAL table. I need to understand if true=true is a request of entity framework (so it could be hard to fix it) or if it is generated by the provider (so I can change to a better implementation).