How to write this query in LightSpeed LINQ? (I need IQueryable<> as result for bind to DevExpress Grid Server Mode)
SELECT C.Id
, C.Number
, ORG.Number
FROM Certificate C
LEFT JOIN Certificate ORG ON C.SourceId = Org.SourceId AND ORG.Version = 1
I can write it with LINQ2SQL as
from c in Certificates
join o in Certificates on c.SourceId equals o.SourceId into cj
from oc in cj.Where(j => j.Version == 1).DefaultIfEmpty()
select new {c.Id, c.Number, OrgNumber = oc.Number};
and generated SQL is
DECLARE @p0 Int = 1
SELECT [t0].[Id], [t0].[Number], [t1].[Number] AS [OrgNumber]
FROM [Certificate] AS [t0]
LEFT OUTER JOIN [Certificate] AS [t1] ON ([t1].[Version] = @p0) AND ([t0].[SourceId] = [t1].[SourceId])
But above code can't run in LightSpeed and throw IndexOutOfRange Exception.
Here are StackTrace
at System.Data.SqlClient.SqlDataReader.CheckDataIsReady(Int32 columnIndex, Boolean allowPartiallyReadColumn, Boolean permitAsync, String methodName)
at System.Data.SqlClient.SqlDataReader.TryReadColumn(Int32 i, Boolean setTimeout, Boolean allowPartiallyReadColumn)
at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i)
at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)
at System.Data.SqlClient.SqlDataReader.get_Item(Int32 i)
at Mindscape.LightSpeed.Linq.ProjectedTypeBuilder.GetValue(Int32 expressionIndex, IDataRecord record, Type targetType)
at Mindscape.LightSpeed.Linq.ProjectedTypeBuilder.CreateInstanceFromReader(NewExpression newExpression, IDataRecord record)
at Mindscape.LightSpeed.Linq.AnonymousTypeBuilder.Build(LightSpeedContext context, IDataRecord record)
at Mindscape.LightSpeed.Linq.Plan.SingleQueryPlan.ProjectManyNative(IUnitOfWork unitOfWork)
at Mindscape.LightSpeed.Linq.Plan.SingleQueryPlan.ExecuteImmediate(IUnitOfWork unitOfWork, Type returnType)
at Mindscape.LightSpeed.Linq.LinqQueryProvider.Execute(Expression expression)
at Mindscape.LightSpeed.Linq.LinqQueryProvider.System.Linq.IQueryProvider.Execute(Expression expression)
at Mindscape.LightSpeed.Linq.LinqQuery`1.System.Collections.IEnumerable.GetEnumerator()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
and LightSpeed's generated SQL as
SELECT
t0.Id AS [t0.Id],
t0.Number AS [t0.Number]
FROM
Certificate t0
LEFT OUTER JOIN
Certificate t2
ON
t0.SourceId = t2.SourceId AND t2.DeletedOn IS NULL
WHERE
t0.DeletedOn IS NULL
