dotnet / dotnet/fsharp

Unfavorable query builder multiple groupJoin/leftOuterJoin results

Open
#8,459 0 comments 1 reaction 0 assignees View on GitHub
Feature Improvement
Dominant language
F#
Stars
4.3k
Forks
876
Avg merge
4d 11h
Merged PRs (30d)
131

Description

Please provide a succinct description of the issue.

When using the query expression builder to perform multiple successive groupJoin/leftOuterJoins the resultant IQueryable cannot be converted to SQL in EF Core.

**Repro steps**

Provide the steps required to reproduce the problem:

```F#
query {
for hist in db.History do

groupJoin loc in db.Location
on (hist.LocId = loc.Id)
into loc'
for loc in loc'.DefaultIfEmpty() do

groupJoin model in db.Model
on (hist.ModelId = model.Id)
into model'
for model in model'.DefaultIfEmpty() do

select (Tuple (hist, loc, model))
}
```

**Expected behavior**

Generates a number of SQL LEFT JOIN clauses.

**Actual behavior**

Translates the first groupJoin into a LEFT JOIN correctly. The output indicates it cannot translate '.DefaultIfEmpty()' in the second groupJoin to SQL, and will be run on the client side.

**Known workarounds**

The query builder can be appeased with either of the following:

```F#
query {
for hist in db.History do

groupJoin loc in db.Location
on (hist.LocId = loc.Id)
into loc'
for loc in loc'.DefaultIfEmpty() do

select {| Hist = hist; Loc = loc |} into temp // works

groupJoin model in db.Model
on (temp.Hist.ModelId = model.Id)
into model'
for model in model'.DefaultIfEmpty() do

select (Tuple (temp.Hist, Temp.Loc, model))
}
```

or,

```F#
query {
for hist in db.History do

groupJoin loc in db.Location
on (hist.LocId = loc.Id)
into loc'
for loc in loc'.DefaultIfEmpty() do

select (Tuple (hist, loc, hist.NavPropToModel)) // works
}
```

**Related information**
I've only shown examples with groupJoin but using leftOuterJoin also behaves in the same manner.

* Operating system
Win 10
* .NET Runtime kind (.NET Core, .NET Framework, Mono)
.net core 3.1, EF core 2.2.6
* Editing Tools (e.g. Visual Studio Version, Visual Studio)
VS'19

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.