Use Linq to create an inner join on data from SQL Server and Excel

Question

Trying to use Linq to create an inner join on data from SQL Server and Excel. I can query each source independently but get an error when joining sources. The error returned is

An IQueryable that returns a self-referencing Constant expression is not supported.

What does that mean and how can I fix the method?

/// <summary>
/// LINQ inner join to Excel query 
/// Sends the results of the query to a dataGridView.
/// Requires a DATAContext to talk to SQL Server.
/// Uses Linq to Excel to talk to Excel
/// </summary>
private void QueryDatabase()
{
    var excelFile = @"C:\Test\Cad_Database.xlsx";
    var excel = new ExcelQueryFactory(excelFile);

    GdaDataContext gda= new GdaDataContext();

    var query = from f in gda.DirectoryAnalysis
                join e in excel.Worksheet("Sheet1") on f.Fullname equals e["FullPath"]
                select new
                {
                    f.Fullname,
                    f.Name,
                    ExcelFullName =  e["FullPath"],
                    DrawingTitle = e["Drawing Title"],
                    DrawingNumber = e["Drawing Number"],
                    DrawingDate = e["Drawing Date"],
                    VendorName = e["Vendor Name"],
                    f.DA_Id
                };

    foreach (var item in query)
    {
        LogWriter.LogEvent($"{item.Fullname}    {item.ExcelFullName}   {item.DrawingTitle} {item.DrawingTitle}", "InnerJoinLinqToExcel");
    }
    dataGridView1.DataSource = query;
}

Answer 1

I don't know what gda.DirectoryAnalysis is but since you are joining two different data sources it makes sense to make an in-memory join (of course take care you are not joining too much data in memory). So perhaps changing the 4th line to var query = from f in gda.DirectoryAnalysis.ToList() could work ?