I have a use case whereby a table (ExternalData) needs to be joined to an input table (ExternalDataInbox). All Entities present in the Inbox but not the receiving table are Inserts, all Entities present in both are updates, and all Entities not present in the Inbox are deletes. I cannot get the syntax to work:

  var db = new LinqMetaData(dataAccessAdapter);
        
        // Inserts: no ExternalData exists
        var insertsQuery = from edi in db.ExternalDataInbox
            where edi.TenantGuid == tenantGuid &&
                  !db.ExternalData.Any(ed =>
                      ed.TenantGuid == edi.TenantGuid &&
                      ed.ExternalEntityName == edi.ExternalEntityName &&
                      ed.ExternalReference == edi.ExternalReference)
            select edi;

        // Updates: matching ExternalData exists
        var updatesQuery = from edi in db.ExternalDataInbox
            join ed in db.ExternalData
                on new { edi.TenantGuid, edi.ExternalReference, edi.ExternalEntityName }
                equals new { ed.TenantGuid, ed.ExternalReference, ed.ExternalEntityName }
                into edJoin
            // inner join
            from ed in edJoin
            where edi.TenantGuid == tenantGuid &&
                  edi.UpdateDateTime > lastUpdateDateTimeFromInbox
            select new { edi, ed };

        // Deletes: ExternalData without matching Inbox
        var deletesQuery = from ed in db.ExternalData
            where ed.TenantGuid == tenantGuid &&
                  !db.ExternalDataInbox.Any(edi =>
                      edi.TenantGuid == ed.TenantGuid &&
                      edi.ExternalEntityName == ed.ExternalEntityName &&
                      edi.ExternalReference == ed.ExternalReference)
            select ed;

        return new ExternalDataStateDto(
            insertsQuery.Select(e => new ExternalDataWithInbox(null, e)).ToArray(),
            updatesQuery.Select(e => new ExternalDataWithInbox(e.ed, e.edi)).ToArray(),
            deletesQuery.Select(e => new ExternalDataWithInbox(e, null)).ToArray());

This results in an Exception of type:

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: 'An exception was caught during the execution of a retrieval query: The multi-part identifier "LPLA_2.guid" could not be bound.
The multi-part identifier "LPLA_2.id" could not be bound.
... etc

The fields that could not be bound belong to the ExternalData (receiving) Entity. I don't see where I am going wrong.

I previously tried it with an outer join (combining Insert and Update), but in this case I get returned an array of ExternalDataInbox Entities (on the Insert part) which are all empty (filled with default values). I caught this query using the debugger and ran it myself, it returns the proper dataset. So the 'error' seems to occur during the part where the retrieved data is propagated into the DTO:

  var db = new LinqMetaData(dataAccessAdapter);
       
        // in case of an Insert, there is no ExternalData record yet. Else it's an update.
        var insertsUpdates = from edi in db.ExternalDataInbox
            join ed in db.ExternalData
                on new { edi.TenantGuid, edi.ExternalReference, edi.ExternalEntityName }
                equals new { ed.TenantGuid, ed.ExternalReference, ed.ExternalEntityName }
                into edJoin
            from ed in edJoin.DefaultIfEmpty()
            where edi.TenantGuid == tenantGuid &&
                  // only return Inbox Entities later than the last sync action
                  edi.UpdateDateTime > lastUpdateDateTimeFromInbox
            select new
            {
                Insert = ed == null,
                ed,
                edi
            };

        // deletes normally don't occur (unless a Developer cleans Inbox Entities), but are
        // defined as an ExternalData without corresponding ExternalDataInbox
        var deletes = from ed in db.ExternalData
            join edi in db.ExternalDataInbox
                on new { ed.TenantGuid, ed.ExternalReference, ed.ExternalEntityName }
                equals new { edi.TenantGuid, edi.ExternalReference, edi.ExternalEntityName }
                into ediJoin
            from edi in ediJoin.DefaultIfEmpty()
            where ed.TenantGuid == tenantGuid
            select new
            {
                ed,
                ediFound = edi != null
            };
        
        IQueryable<ExternalDataWithInbox>? toInsert = insertsUpdates.Where(e => e.Insert)
            .Select(e => new ExternalDataWithInbox(null, e.edi));

        IQueryable<ExternalDataWithInbox> toUpdate = insertsUpdates.Where(e => !e.Insert)
            .Select(e => new ExternalDataWithInbox(e.ed, e.edi));

        IQueryable<ExternalDataWithInbox> toDelete = deletes.Where(e => !e.ediFound)
            .Select(e => new ExternalDataWithInbox(e.ed, null));

        return new ExternalDataStateDto(toInsert.ToArray(), toUpdate.ToArray(), toDelete.ToArray());

This latter query has my preference because it theoretically performs better (1 less query to the database) but I am unable to get it to work. Where am I going wrong?

I should add (on the second query, my preferred one) that I have been unable to confirm whether or not the Update and Delete parts of the query work, since I need to get Insert to work before I have data for the other two.

Strangely, this works:

       var inserts = insertsUpdates.Where(e => e.Insert).ToArray(); // all ExternalDatas are filled with default values, all ExternalDataInboxes are filled with the right values
        var updates = insertsUpdates.Where(e => !e.Insert).ToArray();

I guess the mix between Linq for SQL and 'regular' Linq ( .Where().Select() ) is not possible?

Too bad, because now I still need to execute the query twice in order to get the resultset.

Well, I did get it to work in the preferred way:

        var insertsUpdates = insertsUpdatesQuery.ToArray();
        var deletes = deletesQuery.ToArray();

        return new ExternalDataStateDto(
            insertsUpdates.Where(e => e.Insert).Select(e => new ExternalDataWithInbox(null, e.edi)).ToArray(),
            insertsUpdates.Where(e => !e.Insert).Select(e => new ExternalDataWithInbox(e.ed, e.edi)).ToArray(),
            deletes.Where(e => !e.ediFound).Select(e => new ExternalDataWithInbox(e.ed, null)).ToArray());

But I would still like to know what I was missing. Is it that the LLBL IQueryable 'resultset' is disposed after the regular Linq .Where(), so that the .Select() receives a dataset filled with defaults?

I would like to mark this post as a classic example of Rubber Ducking. I solved my own problem within under half an hour.

Well done.