System.InvalidOperationException: The input sequence has an insufficient number of elements.

[RicoSaupe]

Hi

I am using a select statement getting less colunms than the actual type in the result.

let sql =
            $"""
            SELECT g.SiteId, gm.GroupId,g.Title,g.Owner, g.OwnerIsUser,gm.MemberId, u.tp_Login, u.tp_Title, u.tp_Email
            FROM [dbo].[Groups] g WITH (NOLOCK)
            ...
            """

let cmd = new SqlCommand(sql, sqlConnection)
use! reader = cmd.ExecuteReaderAsync()

 let groups =
                [ while reader.Read() do
                      { SiteId = hydra.``dbo.Groups``.SiteId.Read()
                        GroupId = 0
                        Title = ""
                        } ]

When I am trying to do the SiteId.Read() the app crashed with the following message. I just dont know if that is a bug or something I should have handled. The fieldnames the I select are 9 but the dbo.groups table got 14 columns.

The problem is in this line. Since it tries to take 14 elements out of an array of 9.

|> Array.take fieldNames.Length

[JordanMarr]

You should probably use * when selecting for g, as in: SELECT g.*, u.ColA, u.ColB. That way, all columns for that table will be present. Generally, the HydraReader expects the exact columns in the table and isn't very flexible for custom queries.

For custom queries, you will be better off using the standard data reader. There are some helper methods like reader.Get and reader.GetOption that should make that pretty easy.

let sql =
    $"""
    SELECT g.SiteId, gm.GroupId,g.Title,g.Owner, g.OwnerIsUser,gm.MemberId, u.tp_Login, u.tp_Title, u.tp_Email
    FROM [dbo].[Groups] g WITH (NOLOCK)
    ...
    """

let cmd = new SqlCommand(sql, sqlConnection)
use! reader = cmd.ExecuteReaderAsync()

 let groups =
    [ while reader.Read() do
            {
                SiteId= reader.Get "SiteId"
                Title = reader.Get "Title"
                // ...
            }
    ]

[RicoSaupe]

I see, but is this always the best approach? Wouldn't it be more performant to only return what you really need instead of everything? Especially on larger tables?

I'll give the data reader a shot. Thanks.

[JordanMarr]

Yes, but the HydraReader is designed to be used in conjunction with the query expressions where the selected columns always match up with the generated types.

When you have custom sql statements with custom columns selected, the standard reader is way more flexible.