Entity Framework vs. Oracle Nested Query Limitations

I have the following Code First models (although I'm actually just using EF to access a subset of the existing database, for brevity, this example omitted the table and column attributes):

public enum AlphaState { Unknown = '\0', Good = 'G', Bad = 'B' }

public class Alpha
{
    [Key]
    public long AlphaIndex { get; set; }

    public string Deleted { get; set; }
    public AlphaState State { get; set; }

    [InverseProperty("Alpha")]
    public ICollection<Bravo> Bravos { get; set; }
}

public class Bravo
{
    [Key]
    public long BravoIndex { get; set; }

    [ForeignKey("Alpha")]
    public long? AlphaIndex { get; set; }
    public virtual Alpha Alpha { get; set; }

    [InverseProperty("Bravo")]
    public ICollection<Charlie> Charlies { get; set; }
}

public class Charlie
{
    [Key]
    public int CharlieIndex { get; set; }

    public string Deleted { get; set; }
    public DateTime CreatedAt { get; set; }

    [ForeignKey("Bravo")]
    public long BravoIndex { get; set; }
    public virtual Bravo Bravo { get; set; }

    [ForeignKey("Delta")]
    public long DeltaIndex { get; set; }
    public virtual Delta Delta { get; set; }

    [InverseProperty("Charlie")]
    public virtual ICollection<Delta> AllDeltas { get; set; }
}

public class Delta
{
    [Key]
    public long DeltaIndex { get; set; }

    [ForeignKey("Charlie")]
    public long CharlieIndex { get; set; }
    public virtual Charlie Charlie { get; set; }

    [InverseProperty("Delta")] // actually a 1:0..1 relationship
    public ICollection<Echo> Echoes { get; set; }
}

public enum EchoType { Unknown = 0, One = 1, Two = 2, Three = 3 }

public class Echo
{
    [Key]
    public int EchoIndex { get; set; }

    public EchoType Type { get; set; }

    [ForeignKey("Delta")]
    public long DeltaIndex { get; set; }
    public virtual Delta Delta { get; set; }
}

      

        
        
        
      

    

When I try this query:

var result = context.Alphas.Where(a => a.State == AlphaState.Good)
                           .Where(a => a.Deleted != "Y")
                           .Where(a => a.Bravos.SelectMany(b => b.Charlies)
                                               .Where(c => c.Deleted != "Y")
                                               .Where(c => c.Delta.Echoes.Any())
                                               .OrderByDescending(c => c.CreatedAt).Take(1)
                                               .Any(c => c.Delta.Echoes.Any(e => e.Type == EchoType.Two)))
                           .Select(a => a.AlphaIndex);

      

        
        
        
      

    

... or this equivalent query (B-> A and D-> E are 1: 0..1 ratios):

var result = context.Bravos.Where(b => b.Alpha != null)
                           .Where(b => b.Alpha.State == AlphaState.Good)
                           .Where(b => b.Alpha.Deleted != "Y")
                           .Where(b => b.Charlies.Where(c => c.Deleted != "Y")
                                                 .Where(c => c.Delta.Echoes.Any())
                                                 .OrderByDescending(c => c.CreatedAt)
                                                 .FirstOrDefault().Delta.Echoes
                                                 .Any(e => e.Type == EchoType.Two))
                           .Select(b => b.AlphaIndex);

      

        
        
        
      

    

... I am getting an exception due to a problem in the SQL statement that is being generated. Specifically, it tries to use the declared identifier for the Alpha table in a multi-user subquery and, as described here , Oracle does not map identifiers more than 1 level deep.

Am I crazy or am I missing something obvious? Has Oracle embraced a serious EF service provider that will generate queries that their own database cannot handle for sensible (though admittedly non-trivial) queries?

Is there a config option that can be set (in the database or in EF) that will help with this? Is there some strategy I can use to construct queries in different ways, or separate them, that will solve this problem (without improving performance, turning one round trip into many)?

Note that I can write a SQL statement that gets the same information:

select A.alpha_index
from ALPHA A
inner join BRAVO B on B.alpha_index = A.alpha_index
inner join CHARLIE C on C.bravo_index = B.bravo_index
inner join ECHO E on E.delta_index = D.delta_index
where (A.deleted is null or A.deleted <> 'Y')
  and A.state = 'G'
  and E.type = 2
  and C.created_at = (select max(C2.created_at)
                      from CHARLIE C2
                      inner join ECHO E2 on E2.delta_index = C2.delta_index
                      where (C2.deleted is null or C2.deleted <> 'Y')
                        and C2.bravo_index = C.bravo_index)

      

        
        
        
      

    

I want to use EF to create a modular system that allows non-SQL users to create their own queries using building blocks; each "block" would have Expression<Func<Model, bool>>

one that can be dropped into a chain of Where clauses to build a query. It's all about avoiding the need to write real SQL queries for anything users might want.