join query results in: System.InvalidOperationException: Collection was modified; enumeration operation may not execute.

[dallinbeutler]

Describe the bug I'm trying to inner join two tables. I can successfully query one table, and the other table, but the join query always fails. query in question (fails):

let getGroupRooms (signedInAs:PersonnelID) =
  let gc= int ChatroomHeaderLinkType.GroupChat 
  query{
    for crMem in crMembers do
    join ch in ChatroomHeaders
      on (crMem.ChatroomId = ch.ChatroomId )
    where ( ch.LinkType = gc && crMem.PersonnelId = signedInAs )
    select (crMem.ChatroomId,(ch.Title |> Option.defaultValue ""))
  } |> Sql.List.executeQueryAsync

one table (success):

query{
  for crH in ChatroomHeaders do
  where ( crH.AuthorId = signedInAs )
  select (crH.ChatroomId,(crH.Title |> Option.defaultValue "no-title"))
}

other table (success):

query{
  for crMem in crMembers do
  where ( crMem.PersonnelId = signedInAs )
  select (crMem.ChatroomId,(sprintf "test %i" crMem.ChatroomId))
}

error:

System.InvalidOperationException
  HResult=0x80131509
  Message=Collection was modified; enumeration operation may not execute.
  Source=FSharp.Core
  StackTrace:
   at Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.EvaluateQuotation(FSharpExpr e)
   at Microsoft.FSharp.Linq.QueryModule.EvalNonNestedInner(CanEliminate canElim, FSharpExpr queryProducingSequence)
   at Microsoft.FSharp.Linq.QueryModule.clo@1926-18.Microsoft.FSharp.Linq.ForwardDeclarations.IQueryMethods.Execute[a,b](FSharpExpr`1 q)
   at Queries.Select.getGroupRooms(Int32 signedInAs) in C:\repos\fsharp\Chat\src\Chat.Server\DB\CADChatDB.fs:line 292
   at <StartupCode$Chat-Server>.$APIService.get_Handler@236-25.Invoke(Int32 signedInAs) in C:\repos\fsharp\Chat\src\Chat.Server\APIService.fs:line 237
   at <StartupCode$Bolero-Server>.$Remoting.InvokeForClientSide@108.MoveNext()

  This exception was originally thrown at this call stack:
    [External Code]
    Queries.Select.getGroupRooms(int) in ChatDB.fs
    <StartupCode$Chat-Server>.$APIService.get_Handler@236-25.Invoke(int) in APIService.fs
    [External Code]

I've tried running context.SubmitUpdates() before the query but still get the same result. I feel like I've tried every variation, and am still failing. Am I missing something simple? I'd greatly appreciate the help!

Desktop

• OS: Windows 10
• Browser: Edge
• Version: 106.0.1370.52
• running from AspNetCore on .net6.0
• SQLProvider 1.2.11
• System.Data.SqlClient 4.8.3
• Fsharp.Core 6.0.3

[Thorium]

The weird thing here is that this is not SQLProvider error. Are you ever starting or awaiting your List.executeQueryAsync ?

Is the type PersonnelID some simple type alias or a complex type?

[dallinbeutler]

Quick response!

• yeah, I've been using Sql.List.executeQueryAsync and Sql.Seq.executeQueryAsync everywhere to evaluate the collections. I'm using Elmish, and I'm getting the results as an async Cmd. I can try messing around with that if it would help?
• yeah PersonnelID is just a simple type alias for int

[Thorium]

I'm expecting this comes from somewhere else than the code visible, I do have a lot of joins and never seen this before. That's why I was thinking what if you test to do just |> Seq.toList instead to get a better stacktrace.

[Thorium]

What are crMembers and ChatroomHeaders ? Are they the database tables directly from the context, or are they some other IQueryables executing some more logic there?

[dallinbeutler]

I'm expecting this comes from somewhere else than the code visible, I do have a lot of joins and never seen this before. That's why I was thinking what if you test to do just |> Seq.toList instead to get a better stacktrace.

I was getting weird issues with that, so I stopped trying it. I'll go do that now and report back!

What are crMembers and ChatroomHeaders ? Are they the database tables directly from the context, or are they some other IQueryables executing some more logic there?

let context =  SQL_CHAT.GetDataContext();
let crMembers        = context.Dbo.ChatroomMembers
let ChatroomHeaders  = context.Dbo.ChatroomHeader

for further reference here's some more info:

[<Literal>]
let private connectionString = "Server={redacted...}; Database =Chat; Trusted_Connection=True;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
type SQL_CHAT = SqlDataProvider<Common.DatabaseProviderTypes.MSSQLSERVER
, ConnectionString = connectionString
, CaseSensitivityChange = Common.CaseSensitivityChange.ORIGINAL
, UseOptionTypes = true>

[dallinbeutler]

same story. Also here is more context about where this gets called:

open Bolero.Remoting.Server
open ChatDB
type API =
  { 
    getGroupChats
      :PersonnelID -> Async< (ChatroomID*string)list>
   }  
   interface IRemoteService with
      member this.BasePath = "/api"  

type APIService
  (ctx: IRemoteContext
  , env: IWebHostEnvironment) =
  inherit RemoteHandler<API>()
  override this.Handler =
    {   
    getGroupChats = 
      fun signedInAs ->  
        async{
          return Queries.Select.getGroupRooms signedInAs 
    }
 }

[Thorium]

is the problem still there if you use "context.Dbo.ChatroomMembers" and "context.Dbo.ChatroomHeader" instead of capturing them to a temp variable?

[dallinbeutler]

Looks like that was it! successfully getting the joined tables now! Guess I was inadvertently creating weird references inside the context Type?

[dallinbeutler]

also, thanks so much! been racking my brain trying to fix this for a couple days

[Thorium]

Good!

The error is coming from somewhere here: https://github.com/dotnet/fsharp/blob/main/src/FSharp.Core/Query.fs which makes me wonder because your FSharp.Core 6.0.3 is not the latest.

Anyway, I usually tend to use the context tables directly.

[dallinbeutler]

I updated to 6.0.6 and the old code worked! Guess this is a valuable lesson in trying to create shorthand and not thinking about the implications