This in LINQPad and LLBL 5.5.3

from p__0 in Finding
select new
{
    FindingID = p__0.FindingID,
    FindingNo = p__0.FindingNo,
    FindingNoSortable = p__0.FindingNoSortable,
    FindingTitle = p__0.FindingTitle,
    ApprovalDefinitionID = p__0.Approvals.First().ApprovalDefinitionID, // ApprovalDefinitionID is a Guid
}

works fine in SQL Server works but in Oracle where a Typeconverter(GuidStringConverter) is needed gives

InvalidCastException Specified cast is not valid.

 at lambda_method(Closure , ProjectionRow , Int32[] )
   at SD.LLBLGen.Pro.LinqSupportClasses.DataProjectorToObjectList`1.<>c__DisplayClass4_0.<.ctor>b__0(Object[] r, Int32[] i)
   at SD.LLBLGen.Pro.LinqSupportClasses.DataProjectorToObjectList`1.AddRowToResults(Object[] rawProjectionResult)
   at SD.LLBLGen.Pro.LinqSupportClasses.DataProjectorToObjectList`1.SD.LLBLGen.Pro.ORMSupportClasses.IGeneralDataProjector.AddProjectionResultToContainer(List`1 valueProjectors, Object[] rawProjectionResult)
   at SD.LLBLGen.Pro.ORMSupportClasses.ProjectionUtils.FetchProjectionFromReader(List`1 valueProjectors, IGeneralDataProjector projector, IDataReader dataSource, Int32 rowsToSkip, Int32 rowsToTake, Boolean clientSideLimitation, Boolean clientSideDistinctFiltering, Boolean clientSidePaging, Boolean performValueProjectionsOnRawRow, Boolean postProcessDBNullValues, Dictionary`2 typeConvertersToRun, IRetrievalQuery queryExecuted)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute, Boolean performValueProjectionsOnRawRow, Boolean postProcessDBNullValues, Dictionary`2 typeConvertersToRun)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, QueryParameters parameters)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.<>c__DisplayClass22_0.<FetchProjection>b__0()
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteWithActiveRecoveryStrategy(Action toExecute)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, QueryParameters parameters)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression, Type typeForPostProcessing)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.IEnumerable.GetEnumerator()

We'll look into it. Type converters of projected elements are migrated in this case, at least the provider tries to do that. It can be it loses that info along the way btw, due to the projection in between (which runs in the DB).

I can reproduce it with guid, I can't reproduce it with decimal<->int, even though there's no implicit conversion in the types (but likely in the provider somewhere).

Looking into what we can do here (if possible).

The problem is that the First() query is inlined in the projection. This means the query isn't a separate nested query which has information regarding its source and projection, but a scalar query expression which can be anything (e.g. an aggregate). The code which determines which type converters to run sees the fields in the main projection and one of them is an expression so it's ignored in this case.

We'll see if we can add some checks to see if we can harvest type converter elements from the scalar query expression so it can still run properly here.

Fixed in hotfix 5.5.5 and 5.6.1

Now using ORMSupportClasses.5.5.5-hotfix-20190903 but didn't make any difference.

    [Test]
public void TypeConverterOnProjectedElementInProjectionTest1()
{
    using(var adapter = new DataAccessAdapter())
    {
        var metaData = new LinqMetaData(adapter);
        var q = from c in metaData.Customer
                select new TCCustomerGuidType()
                       {
                           CustomerID = c.CustomerId,
                           FirstGuid = c.CustomerGuids.First().GuidAsString
                       };
        var results = q.ToList();
        foreach(var x in results)
        {
            Assert.AreEqual(typeof(Guid), x.FirstGuid.GetType());
        }
    }
}

here GuidAsString is a string in the db and a Guid in the entity, and is converted to a guid value by a type converter. The TCCustomerGuidType() is a custom class where FirstGuid is a guid property.

This test succeeds. It failed before the fix, so the fix solves this. (This is on sqlserver, but it shouldn't make a difference: the type converter has to be added to the projection, which wasn't done).

Could you make absolutely sure you're using the 5.5.5 hotfix runtime at runtime in your tests?

In any case, the SQL query should have the ApprovalDefinitionID field as a scalar subquery in the projection. Please post your SQL query so we can have a look. (it would mean we have the same bug in another spot, and have to anticipate on that as well). I'll try to create a repro on oracle tomorrow as well, to see if that makes a difference.

(edit) thinking about it a bit, I think the query on oracle is a bit different and therefore doesn't trigger the check we added to find this particular situation. We'll have to look into it more in detail tomorrow (wednesday).

Can't reproduce it.

public class TCDeptGuidType
{
    public short Deptno { get; set; }
    public Guid? FirstGuid { get; set; }
}


[Test]
public void TypeConverterOnProjectedElementInProjectionTest1()
{
    using(var adapter = new DataAccessAdapter())
    {
        var metaData = new LinqMetaData(adapter);
        var q = from d in metaData.Dept
                where d.Deptno == 10
                select new TCDeptGuidType()
                       {
                           Deptno = d.Deptno,
                           FirstGuid = d.Deptguids.First().GuidAsString
                       };
        var results = q.ToList();
        Assert.AreEqual(1, results.Count);
        foreach(var x in results)
        {
            Assert.AreEqual(typeof(Guid), x.FirstGuid.GetType());
            Console.WriteLine("ID: {0}, Guid: {1}", x.Deptno, x.FirstGuid.Value.ToString("X"));
        }
    }
}

Works fine.

Generated Sql query: 
    Query: SELECT "LPLA_1"."DEPTNO" AS "Deptno", (SELECT "LPLA_2"."GUID_AS_STRING" AS "GuidAsString" FROM "SCOTT"."DEPTGUIDS" "LPLA_2" WHERE ( ( ( "LPLA_1"."DEPTNO" = "LPLA_2"."DEPTNO"))) AND rownum <=  1) AS "FirstGuid" FROM "SCOTT"."DEPT" "LPLA_1" WHERE ( ( ( ( ( "LPLA_1"."DEPTNO" = :p1)))))
    Parameter: :p1 : Int16. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 10.

GUID_AS_STRING is a char(36)

Results in:

ID: 10, Guid: {0x7cf99484,0x23cc,0x4e79,{0x8f,0x74,0xc8,0xe9,0x91,0xea,0x84,0x6e}}

I deliberately formatted it like this so it can't be the string in the DB, it has to be a guid value.

Mine is much the same

SELECT "LPLA_1"."FINDING_ID" AS "FindingID",
  "LPLA_1"."FINDING_NO" AS "FindingNo",
  "LPLA_1"."FINDING_NO_SORTABLE" AS "FindingNoSortable",
  "LPLA_1"."FINDING_TITLE" AS "FindingTitle",
  (SELECT "LPLA_2"."APPROVAL_DEFINITION_ID" AS "ApprovalDefinitionID"
FROM
 "AQD"."APP_APPROVAL" "LPLA_2"
WHERE
 ( ( ( "LPLA_1"."FINDING_ID" = "LPLA_2"."RECORD_ID"))) AND rownum <=  1) AS "ApprovalDefinitionID"
FROM
 "AQD"."QAOC_FINDING" "LPLA_1"

Assembly used is dated 3/09/2019 9:42:56 AM

"APPROVAL_DEFINITION_ID" VARCHAR2(38 CHAR) NOT NULL ENABLE,

Sometimes the exception is a little different

System.NullReferenceException: Object reference not set to an instance of an object.
   at SD.LLBLGen.Pro.ORMSupportClasses.ProjectionRow.GetGuid(Int32 i)
   at lambda_method(Closure , ProjectionRow , Int32[] )
   at SD.LLBLGen.Pro.LinqSupportClasses.DataProjectorToObjectList`1.<>c__DisplayClass4_0.<.ctor>b__0(Object[] r, Int32[] i)
   at SD.LLBLGen.Pro.LinqSupportClasses.DataProjectorToObjectList`1.AddRowToResults(Object[] rawProjectionResult)

Opps I was using my own typeconverter not the built in one. Switching to built in one fixed the error in the example I gave but adding an OrderBy bought it back again e.g.

(from p__0 in Finding
select new 
{
    FindingID = p__0.FindingID,
    ApprovalDefinitionID = p__0.Approvals.First().ApprovalDefinitionID, // ApprovalDefinitionID is a Guid
}).OrderBy(f=>f.FindingID)

Very strange...

Could you build the runtime from source, (the hotfix source) and check what's happening in QueryParameters.DetermineTypeConvertersToRun(), line 222 and further?

The old version has a single if statement, the new version performs a check on whether the expression is a scalarqueryexpression and if so it will proceed further and obtain the persistenceinfo for the field from there.

The null reference exception might be caused by an empty string which isn't converted to a guid by the converter (as oracle returns nulls as empty string), but that's guessing from my part.

Decompiling the released binary shows the code is in place in that one, so it's not some copy fluke somewhere.

(Code for clarity: )

/// <summary>
/// Determines the type converters to run during a projection fetch.
/// </summary>
/// <returns></returns>
public Dictionary<int, TypeConverter> DetermineTypeConvertersToRun()
{
    var typeConvertersToRun = new Dictionary<int, TypeConverter>();
    for(int i = 0; i < this.FieldPersistenceInfosForQuery.Length; i++)
    {
        var persistenceInfo = this.FieldPersistenceInfosForQuery[i];
        var field = this.FieldsForQuery[i];
        if(field.ExpressionToApply != null)
        {
            // it might be the field is a scalar query expression. This means the scalar query might have a type converter on the projection field. If that's the case, we have
            // to pick that type converter as it's needed for the projection.
            var expressionAsScalarExpression = field.ExpressionToApply as ScalarQueryExpression;
            if(expressionAsScalarExpression == null)
            {
                // nothing to do
                continue;
            }

            persistenceInfo = expressionAsScalarExpression.SelectFieldPersistenceInfo;
            field = expressionAsScalarExpression.SelectField;
        }
        if(persistenceInfo?.TypeConverterToUse != null && (field.AggregateFunctionToApply == AggregateFunction.None) && (field.ExpressionToApply == null))
        {
            typeConvertersToRun.Add(i, persistenceInfo.TypeConverterToUse);
        }
    }
    return typeConvertersToRun;
}

TomDog wrote:

Opps I was using my own typeconverter not the built in one. Switching to built in one fixed the error in the example I gave but adding an OrderBy bought it back again e.g.

(from p__0 in Finding
select new 
{
    FindingID = p__0.FindingID,
    ApprovalDefinitionID = p__0.Approvals.First().ApprovalDefinitionID, // ApprovalDefinitionID is a Guid
}).OrderBy(f=>f.FindingID)

I asked you this before, but you really have to be specific. 'the error', you talk about different errors in this thread: the conversion issue that's going wrong and a null reference exception. I assume 'the error' here means the original string->guid conversion doesn't work?

the conversion issue above can be because the conversion is in a derived table and therefore missed. So the field in the projection is a derived table referencing field, and there's code in place which will lift type converters up to the projection (so to the derived table referencing field) however the fix we added isn't there, it's in the code at the queryparameters looking at the type converters of this already processed query. I.o.w. the patch is at a level that's too late.

We'll see what we can do.

(edit) indeed:

Query: SELECT "LPA_L1"."Deptno", "LPA_L1"."FirstGuid" FROM (SELECT "LPLA_1"."DEPTNO" AS "Deptno", (SELECT "LPLA_2"."GUID_AS_STRING" AS "GuidAsString" FROM "SCOTT"."DEPTGUIDS" "LPLA_2" WHERE ( ( ( "LPLA_1"."DEPTNO" = "LPLA_2"."DEPTNO"))) AND rownum <=  1) AS "FirstGuid" FROM "SCOTT"."DEPT" "LPLA_1" WHERE ( ( ( "LPLA_1"."DEPTNO" = :p1)))) "LPA_L1" ORDER BY "LPA_L1"."Deptno" ASC
    Parameter: :p1 : Int16. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 10.

(edit) found it.

We fixed it. Running tests now and will then upload a new hotfix.

See hotfix build for 5.5.5 and 5.6.1. It's now applied as well to derived table targeting fields, so it should cover this and other situations we think can arise. It should be fixed now

Awesome sorted - I was worried about you being able to repro this.

It also works with our Typeconverter now that I've changed it to pass through DBNull in ConvertFrom. We use ours for historical reasons and because we want dashes e.g. 45a9601f-ece9-4c86-932f-24c325e18 rather than 45a9601fece94c86932f24c325e18902

Thanks again

Glad it's sorted! the code base is a bit vast so we didn't initially think there was one other spot where this could happen, your orderby repro made it clear that there was It was found quickly after that