Entity Framework and CROSS/OUTER APPLY
In LINQ 2 SQL this always results in an APPLY
:
from t1 in tab1
from t2 in tab2.Where(t2 => t2.SomeCol == t1.SomeCol).Take(1)
select new { t1, t2 }
In EF this will either fail, or also result in an APPLY
(I don't know which one). This is a correlated join which requires an APPLY
on the SQL side.
How do I create a T-SQL Cross-Apply with Entity Framework Core?
Nevermind. Found a solution that works for me.
var q = from c in comCommunicationContext.Communication.Where(c=>c.Label.InternalName==labelName && c.Businesstransaction.DocumentType.DocumentTypeName==documentType && c.Businesstransaction.SourceSystem.SourceSystemName==sourceSystem)
join coc in comCommunicationContext.CommunicationOutputChannel
on c.CommunicationId equals coc.CommunicationId
from csoc in comCommunicationContext.CommunicationStatusOutputChannel.Where(x=>x.CommunicationOutputChannelId==coc.CommunicationOutputChannelId).OrderByDescending(y=>y.CreatedOn).Take(1)
where csoc.Status == "to process"
select new Communication(){CommunicationDataEnriched = c.CommunicationDataEnriched}
;
How do I mimic a SQL Outer Apply in Linq using Entity Framework?
Try the following queries. EF Core 3.1, should translate this to Outer Appply, but higher versions may use JOIN
and ROW_NUMBER
var query1 =
from m in dbContext.Main
from s in dbContext.Sub
.Where(s => m.Id == s.Id && m.Number == s.Number)
.Take(1)
.DefaultIfEmpty()
select new
{
m,
SubName = s.SubName
}
Or this variant:
var query1 =
from m in dbContext.Main
select new
{
m,
SubName = dbContext.Sub
.Where(s => m.Id == s.Id && m.Number == s.Number)
.Select(s => s.SubName)
.FirstOrDefaut()
}
OUTER APPLY in .net 4.5 and Entity Framework
Ok. The only solution, which is what I found while reading in the initial table to the list. Unfortunately, not quite what I meant, but it works.
AIds = (from x in
(
context.RISK_T_ASSESS_HIST.Where(x => x.ID_RISKOBJECT.HasValue && x.F_CREATEDON >= Freq.StartDate && x.F_CREATEDON <= Freq.EndDate)
.Select(x => new { x.ID_RISKOBJECT, x.F_CREATEDON, x.F_STATUS }).ToList()
)
group x by x.ID_RISKOBJECT into gr
let lastCreated = gr.Max(p => p.F_CREATEDON)
select new
{
ObjId = gr.Key
,
LastStatus = gr.Where(p => p.F_CREATEDON == lastCreated && p.ID_RISKOBJECT == gr.Key).Select(p => p.F_STATUS).FirstOrDefault()
}).Where(x => x.LastStatus == 0 || x.LastStatus == 1).Select(x => x.ObjId.Value).ToArray();
This issue Entity Framework and will be passed as an error on their forum.
C# Outer Apply in LINQ
from u in Users
join UserHistory on u.masterID equals h.masterID into h
select new {u.masterID, u.user, h.created.OrderByDescending().First()}
OUTER APPLY is not supported by MySQL
If this error is coming from MySQL one of two things have happened:
- You haven't configured the provider correctly.
- There is a bug in your EF provider.
If this error is coming from your MySQL EF provider, then one of two things have happened:
- You haven't configured the provider correctly.
- There is a limitation in your EF provider.
SQL is generated by the provider. You can configure server-version-specific SQL generation via the ProviderManifestToken attribute in EDMX. This is how you tell the provider not to use SQL features which older server versions don't support.
It's possible that some MySQL storage engines support SQL features which others do not. In this case, the provider would need to either use a common subset of features supported by most engines or use ProviderManifestToken
to allow you to choose.
However, it's also possible that a buggy provider simply returns incorrect SQL. If this is the case then you must either find an update or avoid queries which touch the bug.
Update: Based on @Devart's answer, it seems that this is a limitation of the provider, which is designed in due to limitations of MySQL. The EF will produce an ADO.NET canonical command tree. It is the provider's responsibility to translate this to SQL. If the EF returns a cross/outer apply node in the CCT, it seems that Devart has not found a way to translate this to SQL which MySQL can handle. So either MySQL is just not up to the task of supporting all EF queries, or someone who is a MySQL expert (not me!) needs to show Devart how to produce MySQL-compatible SQL which can properly return rows for the cross/outer apply CCT nodes.
Related Topics
Sync Between SQL Server and MySQL Server
How to Retrieve Same Column Twice with Different Conditions in Same Table
SQL Query with Union in Doctrine Symfony
How to Store SQL Server Sort Order in a Variable
Query Records and Group by a Block of Time
Find the Maximum Consecutive Years for Each Id's in a Table(Oracle SQL)
SQL Function to Get Count of How Many Times String Appears in Column
SQL Scheduling - Select All Rooms Available for Given Date Range
Xml Query() Works, Value() Requires Singleton Found Xdt:Untypedatomic
How to Group by and Return Sum Row in Postgres
SQL Server 2005: How to Subtract 6 Month
Auto Increment on Composite Primary Key
"Invalid Column Name" Error on SQL Statement from Openquery Results
Bulk Load Data Conversion Error (Truncation)
Is an Overuse of Nullable Columns in a Database a "Code Smell"
Cumulative Sum of Values by Month, Filling in for Missing Months