Filtering with Linq.Where
Nobody has claimed this yet.
Assessment
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Newbie friendliness
- 35/100
Research direction
Start with the LINQ query expression and filterByName example in the issue, then reproduce the generated SQL using SQLProvider 1.1.91, FSharp.Core 4.7.2, and .NET Core 3.1. Compare the table alias used for the filtered Name field with the expected SQL; done means the filter targets the intended joined table without requiring the workaround of reversing table order.
Written by the indexing model from the issue text.
Description
Describe the bug
Trying to execute a join and filtering using Linq.Where on the second table produces a sql where clause where the field is linked to the first table not the second one.
To Reproduce
let filterByName (filter: Filter): Filtering =
fun q ->
match filter.Name with
| ValueNone -> q
| ValueSome n -> n |> Str.value |> (fun v -> q.Where(fun e -> e.Name.Contains(v)))
let search: Insurer.Query.DbSearch =
fun (tenantId, filter) ->
let tenantId = tenantId |> TenantId.value
let ctx = Db.readerCtx ()
query {
for i in ctx.Insurance.InsurerTable do
join c in ctx.People.Company on
((i.TenantId, i.PersonId) = (c.TenantId, c.Id))
where
(c.TenantId = tenantId)
select c
}
|> filterByName filter
generates the following sql clause:
SELECT [Column List]
FROM [Insurance].[InsurerTable] as i
INNER JOIN [People].[Company] as c
on i.[TenantId] = c.[TenantId] AND i.[PersonId] = c.[Id]
WHERE ((c.[TenantId] = 561) AND (c.[Name] LIKE N'%Name209092560%'))
Expected behavior
SELECT [Column List]
FROM [Insurance].[InsurerTable] as i
INNER JOIN [People].[Company] as c
on i.[TenantId] = c.[TenantId] AND i.[PersonId] = c.[Id]
WHERE ((c.[TenantId] = 561) AND (i.[Name] LIKE N'%Name209092560%'))
Please, note the expected clause uses i.[Name] instead of c.[Name] in WHERE section.
Additional context
SQLProvider (1.1.91)
FSharp.Core (4.7.2)
Net Core 3.1
Workaround for this particular case
Inverting the order of the tables in the query solves the problem:
for c in ctx.People.Company do
join i in ctx.Insurance.InsurerTable on
((c.TenantId, c.Id) = (i.TenantId, i.PersonId))
But this is valid in this case, in many others, this solution won't be possible.
- Dominant language
- F#
- Stars
- 627
- Forks
- 147
- Avg merge
- 2h 2m
- Merged PRs (30d)
- 1
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from fsprojects/SQLProvider
-
enhancement
Difficulty 3/5 1-2 days Newbie friendliness 55/100
fsprojects/SQLProvider#872 · 2 comments ·
-
Repo Assist? Openenhancement
Difficulty 5/5 Over a week Newbie friendliness 10/100
fsprojects/SQLProvider#870 · 1 comment ·
-
postgresql
Difficulty 4/5 3-5 days Newbie friendliness 58/100
fsprojects/SQLProvider#869 · 2 comments ·
-
documentation
Difficulty 2/5 1-3 hours Newbie friendliness 42/100
fsprojects/SQLProvider#868 · 2 comments ·
-
sql server
Difficulty 4/5 3-5 days Newbie friendliness 45/100
fsprojects/SQLProvider#851 · 1 comment ·
All issues in fsprojects/SQLProvider
Similar issues
-
bug: AI Gateway client filter lists "Unknown" twice when NULL and literal Unknown clients coexist Openbug
Difficulty 2/5 1-3 hours Newbie friendliness 90/100
-
[BUG] A column whose default is the empty string is drawn in the ER diagram as having no default Openbug database-provider good first issue hacktoberfest
Difficulty 2/5 1-3 hours Newbie friendliness 90/100
libredb/libredb-studio#1030 · 6 comments ·
-
comp-datalake
Difficulty 2/5 1-3 hours Newbie friendliness 88/100
ClickHouse/ClickHouse#121222 ·
-
bug
Difficulty 2/5 1-3 hours Newbie friendliness 75/100
-
bug redshift
Difficulty 2/5 1-3 hours Newbie friendliness 88/100