C# guid[] in oracle caused System.ArgumentException:value does not fall within the expected range

[ai2soft]

when i use dapper with oracle database. and tranform Guid[] with IN operator code bellow

` var sqlBuilder = new StringBuilder() .Append(" SELECT w."CourseId", c."Title", c."CourseComment", c."CategoryName", (case c."Type" WHEN 1 THEN N'综合类' ELSE N'专题类' end) as "CourseType"") .Append(" FROM "tb_Violation_Item_KeyWord" k") .Append(" Left Join "v_AllCourseKeyWords" w") .Append(" ON w."CourseKeyWordId" = k."CourseKeyWordId"") .Append(" Left Join "v_AllCourse" c") .Append(" ON c."CourseId" = w."CourseId"") .Append(" WHERE k."ItemId" in (:Ids)") .Append(" and c."State" =1");

        var x = (from id in req.Ids select id).ToList();

        return WebQuery<dynamic>(sqlBuilder.ToString(), new { Ids = x});

`

and the parameter req is public class IdListRequest { public Guid[] Ids { get; set; } } when i executed the query, i got an error : value does not fall within the expected range

i have taken snap

please pay attention to this problem.

[ai2soft]

i have already add a guid handler

` public class RawGuidHandler : SqlMapper.TypeHandler<Guid> { public override Guid Parse(object value) { if (value is byte[] byteArray) { return new Guid(byteArray); }

        throw new NotImplementedException("RawGuidHandler->Parse");
    }

    public override void SetValue(IDbDataParameter parameter, Guid value)
    {
        if (parameter.DbType != DbType.Binary)
        {
            parameter.DbType = DbType.Binary;
        }

        if (value != Guid.Empty)
        {
            //parameter.Value = value.ToByteArray();
            OracleParameter oracleParameter = (OracleParameter)parameter;
            oracleParameter.OracleDbType = OracleDbType.Raw;
            parameter.Value = ((Guid)value);
        }
    }

`

[mgravell]

what is the stacktrace on this exception?

The in here looks unusual - might be worth removing the parens, i.e. in :Ids

unrelated, but I recommend using a constant here for the SQL; it'll be far more efficient - for example:

const string sql = @"SELECT w.""CourseId"", c.""Title"", c.""CourseComment"", c.""CategoryName"",
    (case c.""Type"" WHEN 1 THEN N'综合类' ELSE N'专题类' end) as ""CourseType""
FROM ""tb_Violation_Item_KeyWord"" k
Left Join ""v_AllCourseKeyWords"" w
-- etc
WHERE k.""ItemId"" in :Ids
and c.""State"" =1
";

[ai2soft]

in fact , i have removed brackets。 and the stacktrace is

[mgravell]

Looking at PackListParameters, I don't think we consider custom parameter handlers in this scenario. This won't work right now, if it depends on that handler.

[ai2soft]

in my db data models , Id is RAW(16), it should use RawGuidHandler.

[mgravell]

Well, right now it doesn't; I can't give you an ETA on revisiting that, so...

[ai2soft]

if the sql likes "select * from table where id = :Id"， and Id = { Guid }. dapper can execute correctly . can you tell me what does dapper do when the paramater is guid ?

[mgravell]

The issue here is the in command-rewriting and parameter addition. This does not (as far as I can see) currently consider custom handlers. This feels like an oversight - if you're thinking I don't see that: I do. I'm simply saying that I can't give you a "here's the line to change" answer that will work today.

[ai2soft]

alright. i did a temp solution, it's bellow ` var sqlBuilder = new StringBuilder() .Append(" with src as (") .Append(" SELECT DISTINCT r."UserId", f."UserName", f."UserNo", d."DeptName", SUM(r."Points") over (partition by r."UserId") as "Points"") .Append(" FROM "tb_Violation_Record" r") .Append(" Left Join "v_AllStaffs" f") .Append(" ON f."UserId" = r."UserId"") .Append(" Left Join "tb_Department" d") .Append(" ON d."DeptId" = f."DeptId"") .Append(" WHERE r."State" = '0'") .Append(" and r."ViolationItemId" in (");

        var parameters = new ExpandoObject();
        var xM = parameters as IDictionary<string, object>;

        for (int i = 0; i < req.Items.Length; i++)
        {
            sqlBuilder.Append($" :Id{i},");
            xM[$"Id{i}"] = req.Items[i];
        }

        sqlBuilder.Remove(sqlBuilder.Length - 1,1)
            .Append(')');

        sqlBuilder.Append(" )")
            .Append(" SELECT *")
            .Append(" from src")
            .Append(" WHERE src.\"Points\" >= :Points");

        xM["Points"] = req.Points;

`

[MeikelLP]

@mgravell any update on this?