I have the following Code First models (though in fact I'm just using EF to access a subset of an existing database; Table & Column attributes have been omitted from this example for brevity):
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 attempt 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 relations):
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 get an exception because of a problem in the SQL statement that is generated. Specifically, it's trying to use the declared identifier for the Alpha table in a multi-nested subquery, and, as described here, Oracle doesn't map identifiers more than 1 level deep.
Am I crazy, or missing something obvious? Did Oracle seriously ship an EF provider that will generate queries their own database can't run for reasonable (though admittedly not trivial) queries?
Is there a configuration option that can be set (in the database or in EF) that will help with this? Is there some strategy I can use to build queries differently or split them up that will solve this problem (without tanking performance by turning a single round-trip into many)?
Note that I can write a SQL statement myself that will get 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 make a modular system that allows users who don't know any SQL to build their own queries using building blocks I provide; each "block" would have an Expression<Func<Model, bool>> that can be slapped into a chain of Where clauses to build the query. The whole point of this is to avoid having to write actual SQL queries for everything users might want to look for.
