dotnet
EF Core: two joins to the same table via Fluent API
Hi!
EntityFrameworkCore 6.0.5
I have such tables in DB:
ApplicationsControlMonitorEntriesAggregate
...
TenantId
ApplicationsControlAggregateId
...
ApplicationsControlAggregate
...
string ProcessCaption
...
ProcessStatisticRule
...
string ProcessName
int? TenantId
...
ApplicationsControlAggregateId is ForeignKey on appropriate table.
ProcessStatisticRule has not any physical relations with any tables. But it has logical relation with ApplicationsControlAggregate by ProcessName.
SQL query I want to create with EF:
select *
from
ApplicationsControlMonitorEntriesAggregate ApplicationsControlMonitorEntriesAggregate
join ApplicationsControlAggregate ApplicationsControlAggregate
on ApplicationsControlMonitorEntriesAggregate.ApplicationsControlAggregateId = ApplicationsControlAggregate.Id
join ProcessStatisticRule ProcessStatisticRule1
on ApplicationsControlAggregate.ProcessCaption = ProcessStatisticRule1.ProcessName
and ProcessStatisticRule1.TenantId is null
join ProcessStatisticRule ProcessStatisticRule2
on ApplicationsControlAggregate.ProcessCaption = ProcessStatisticRule2.ProcessName
and ProcessStatisticRule2.TenantId = ApplicationsControlMonitorEntriesAggregate.TenantId
Here is my query with EF:
var query = db
.ApplicationsControlMonitorEntriesAggregates.Where(a => a.TenantId == _tenantId)
.Join(
db.ProcessStatisticRules,
a => new ProcessStatisticRulesKey(a.ApplicationsControlAggregates.ProcessCaption, null),
psr => new ProcessStatisticRulesKey(psr.ProcessName, psr.TenantId),
(a, psr) => new ApplicationRiskTemporarySelector(a, psr, null)
)
.Join(
db.ProcessStatisticRules,
a => new ProcessStatisticRulesKey(a.ApplicationsControlMonitorEntriesAggregate.ApplicationsControlAggregates.ProcessCaption, a.ApplicationsControlMonitorEntriesAggregate.TenantId),
psr => new ProcessStatisticRulesKey(psr.ProcessName, psr.TenantId),
(a, psr) => new ApplicationRiskTemporarySelector(a.ApplicationsControlMonitorEntriesAggregate, a.ProcessStatisticRuleGeneral, psr)
)
.Select(r => new ApplicationRiskRow
{
FilesCount = r.ApplicationsControlMonitorEntriesAggregate.FilesCount
})
;
It fails with error:
The LINQ expression 'DbSet<ApplicationsControlMonitorEntriesAggregate>()\r\n .Where(a => a.TenantId == ___tenantId_0)\r\n .Join(\r\n inner: DbSet<ApplicationsControlAggregate>(), \r\n outerKeySelector: a => EF.Property<int?>(a, "ApplicationsControlAggregatesId"), \r\n innerKeySelector: a0 => EF.Property<int?>(a0, "Id"), \r\n resultSelector: (o, i) => new TransparentIdentifier<ApplicationsControlMonitorEntriesAggregate, ApplicationsControlAggregate>(\r\n Outer = o, \r\n Inner = i\r\n ))\r\n .Join(\r\n inner: DbSet<ProcessStatisticRule>(), \r\n outerKeySelector: a => new ProcessStatisticRulesKey(\r\n a.Inner.ProcessCaption, \r\n null\r\n ), \r\n innerKeySelector: p => new ProcessStatisticRulesKey(\r\n p.ProcessName, \r\n p.TenantId\r\n ), \r\n resultSelector: (a, p) => new TransparentIdentifier<TransparentIdentifier<ApplicationsControlMonitorEntriesAggregate, ApplicationsControlAggregate>, ProcessStatisticRule>(\r\n Outer = a, \r\n Inner = p\r\n ))\r\n .Join(\r\n inner: DbSet<ProcessStatisticRule>(), \r\n outerKeySelector: ti => new ProcessStatisticRulesKey(\r\n new ApplicationRiskTemporarySelector(\r\n ti.Outer.Outer, \r\n ti.Inner, \r\n null\r\n ).ApplicationsControlMonitorEntriesAggregate.ApplicationsControlAggregates.ProcessCaption, \r\n (int?)new ApplicationRiskTemporarySelector(\r\n ti.Outer.Outer, \r\n ti.Inner, \r\n null\r\n ).ApplicationsControlMonitorEntriesAggregate.TenantId\r\n ), \r\n innerKeySelector: p0 => new ProcessStatisticRulesKey(\r\n p0.ProcessName, \r\n p0.TenantId\r\n ), \r\n resultSelector: (ti, p0) => new TransparentIdentifier<TransparentIdentifier<TransparentIdentifier<ApplicationsControlMonitorEntriesAggregate, ApplicationsControlAggregate>, ProcessStatisticRule>, ProcessStatisticRule>(\r\n Outer = ti, \r\n Inner = p0\r\n ))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
If I remove second one Join, for example:
var query = db
.ApplicationsControlMonitorEntriesAggregates.Where(a => a.TenantId == _tenantId)
.Join(
db.ProcessStatisticRules,
a => new ProcessStatisticRulesKey(a.ApplicationsControlAggregates.ProcessCaption, null),
psr => new ProcessStatisticRulesKey(psr.ProcessName, psr.TenantId),
(a, psr) => new ApplicationRiskTemporarySelector(a, psr, null)
)
.Select(r => new ApplicationRiskRow
{
FilesCount = r.ApplicationsControlMonitorEntriesAggregate.FilesCount
})
;
It works.
How can I do these two joins via Fluent API?
Initially I tried with anonymous types. But in second Join compiler says that it can't infer type and I can't specify needed generic type in this case because that is anonymous type. Or I can?
After that I declared usual types and tried to use them. I found out that in this case you can't use them using type initializer. Like this:
new ProcessStatisticRulesKey{a = a.ApplicationsControlAggregates.ProcessCaption, b = null}
In this case you will face an error during runtime. Error says something similar to what I get now, something about "could not be translated" and all that stuff.
The only way I found to get rid of this error - this is creation constructor for type and use this constructor in expression. It works if you have only one join to particular table. But when you add one more join to the same table, you face error I described in start post.
So, what I should use in this case?
But in second Join compiler says that it can't infer type and I can't specify needed generic type in this case because that is anonymous type. Or I can?
That should work as long as you make sure both anonymous types are same shape. Compiler will generate same type for it.
@smitpatel , thanks for the answer!
I got almost I want.
var query = db
.ApplicationsControlMonitorEntriesAggregates.Where(a => a.TenantId == _tenantId)
.Join(
db.ProcessStatisticRules,
a => new
{
ProcessCaption = a.ApplicationsControlAggregates.ProcessCaption,
TenantId = (int?) null
},
psr => new
{
ProcessCaption = psr.ProcessName,
TenantId = (int?) psr.TenantId
},
(a, psr) => new
{
ApplicationsControlMonitorEntriesAggregate = a,
ProcessStatisticRuleGeneral = psr
}
)
.Join(
db.ProcessStatisticRules,
a => new
{
ProcessCaption = a.ApplicationsControlMonitorEntriesAggregate.ApplicationsControlAggregates.ProcessCaption,
TenantId = (int?)_tenantId
},
(psr) => new
{
ProcessCaption = psr.ProcessName,
TenantId = (int?)psr.TenantId
},
(a, psr) => new
{
ApplicationsControlMonitorEntriesAggregate = a.ApplicationsControlMonitorEntriesAggregate,
ProcessStatisticRuleGeneral = a.ProcessStatisticRuleGeneral,
ProcessStatisticRuleByTenant = psr
}
)
.Select(r => new ApplicationRiskRow
{
FilesCount = r.ApplicationsControlMonitorEntriesAggregate.FilesCount,
})
;
I said "almost" because this code turns into the next SQL query:
SELECT [a].[FilesCount]
FROM [ApplicationsControlMonitorEntriesAggregates] AS [a]
INNER JOIN [ApplicationsControlAggregates] AS [a0] ON [a].[ApplicationsControlAggregatesId] = [a0].[Id]
INNER JOIN [ProcessStatisticRules] AS [p] ON ([a0].[ProcessCaption] = [p].[ProcessName]) AND ([p].[TenantId] IS NULL)
INNER JOIN [ProcessStatisticRules] AS [p0] ON ([a0].[ProcessCaption] = [p0].[ProcessName]) AND (@___tenantId_1 = [p0].[TenantId])
WHERE [a].[TenantId] = @___tenantId_0
But I need LEFT JOIN instead of INNER JOIN. I was trying to put DefaultOfEmty() wherever compiler allowed it, but in all cases I got not the query I really want. For example if I add DefaultOrEmpty() to first Join:
var query = db
.ApplicationsControlMonitorEntriesAggregates.Where(a => a.TenantId == _tenantId)
.Join(...).DefaultOrEmpty()
.Join(...)
.Select()
I get the next SQL query:
SELECT [t].[FilesCount]
FROM (
SELECT NULL AS [empty]
) AS [e]
LEFT JOIN (
SELECT [a].[FilesCount], [a0].[ProcessCaption]
FROM [ApplicationsControlMonitorEntriesAggregates] AS [a]
INNER JOIN [ApplicationsControlAggregates] AS [a0] ON [a].[ApplicationsControlAggregatesId] = [a0].[Id]
INNER JOIN [ProcessStatisticRules] AS [p] ON ([a0].[ProcessCaption] = [p].[ProcessName]) AND ([p].[TenantId] IS NULL)
WHERE [a].[TenantId] = @___tenantId_0
) AS [t] ON 1 = 1
INNER JOIN [ProcessStatisticRules] AS [p0] ON ([t].[ProcessCaption] = [p0].[ProcessName]) AND (@___tenantId_1 = [p0].[TenantId])
How can I get just LEFT JOINs in my case?
See https://docs.microsoft.com/en-us/ef/core/querying/complex-query-operators#left-join
It seems I've done it!
For LEFT JOIN you must use SelectMany() instead Join().
Am I the only one who thinks this is a good example of a bad API? It's so not obvious and obscure.
Somebody knows why we still don't have methods like LeftJoin(), RightJoin(), FullJoin()?