Filtering with Linq.Where

[mauriciomagni]

trafficstars

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.

[Thorium]

The code sample you provided is not complete, but I think SQLProvider may be working correctly: You say select c and after that a filter. So the only available object to filter is c and not i. What if you leave the select away and use also LINQ Select later?

Edit: I guess you are trying to do something like this: https://fsprojects.github.io/SQLProvider/core/composable.html#Generate-composable-queries-by-using-NET-LINQ-functions-with-IQueryable

Before going that complex, check this first: https://fsprojects.github.io/SQLProvider/core/querying.html#Best-practices-working-with-queries

`

[mauriciomagni]

Hello @Thorium, Thank you for your proper response, and sorry, it took me a while to come back to this. What is curious for me, is that inverting the order of the tables in the query it works: So, it is like when using IQueryable.Where it cannot recognize which of the tables in the join should be taken into account. Please, keep in mind that Company has "Name" but Insurer do not. I was not aware of the possibility to combine queries, I will play with them as they seem to be what I need to solve this issue in a different way. Thank you for your help, Mauricio