PetaPoco request for int[] type not working

Question

Hello I am trying to make a fetch to Microsoft SQL Server Management studio where in one of my columns there is an array of ints that I want to retrieve based on another column value.

I am making the query like this:

int[] appsId = db.Fetch<int[]>($"SELECT Apps FROM [dbo].[Group] WHERE Name = @0", user.Groups[0]).FirstOrDefault();

The table has the following (shortened) info:

Name    Apps
123     [356, 1589]

Using this I get the exception: 'Input string was not in a correct format'.

Any ideas, thanks!

Answer 1

Probably need to unpack that array in the query:

SELECT Value 
FROM [dbo].[Group] 
CROSS APPLY 
( 
   SELECT CAST(VALUE AS INT) Value 
   FROM OPENJSON(APPS) 
) v
WHERE Name = @0

Answer 2

The solution was the following:

Entities.Database.Group dbScope = db.Fetch<Entities.Database.Group>($"SELECT * FROM [dbo].[Group] WHERE Name = @0", user.Groups[0]).FirstOrDefault();

Since Group is the class that defines the Object you can then map the field that you need, in this case Apps.

Thanks everyone!