grid filtering broken with null value

ADMIN

Eyup

Posted on: 03 Nov 2021 09:16

Hello John,

Thank you for providing this additional info.

Regards,
Eyup
Progress Telerik

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

John

Posted on: 27 Oct 2021 13:42

well i have a mssql server. I can post the exact code i was using and i just reverted and reproduced the issue.

private ContentResult Read([DataSourceRequest] DataSourceRequest request, ModelStateDictionary modelState, int id, int categoryID)
{
var data = db.CarrierCompliances.Where(m => m.ID == id || id == 0)
.Include(m => m.Carrier)
.Include(m => m.Carrier.CarrierType)
.Include(m => m.CarrierComplianceType)
.Select(m => new DocumentComplianceViewModel
{
CategoryID = (int)ObjectDef.TYPE.CarrierCompliance,
ID = m.ID,
CarrierID = m.CarrierID,
Carrier = new IdName { ID = m.CarrierID, Name = (m.Carrier.DeleteDateUTC == null) ? m.Carrier.Name : Strings.DELETED + " " + m.Carrier.Name },
IDNumber = ((m.Carrier.DeleteDateUTC == null) ? "" : Strings.DELETED + " ") + m.Carrier.IDNumber,
DriverID = 0,
TruckID = 0,
TrailerID = 0,
DriverName = null,
CarrierComplianceTypeID = m.CarrierComplianceTypeID,
ComplianceTypeID = 0,
TruckComplianceTypeID = 0,
TrailerComplianceTypeID = 0,
ComplianceType = new IdName { ID = m.CarrierComplianceTypeID, Name = m.CarrierComplianceType.Name },
CarrierType = new IdName { ID = m.Carrier.CarrierTypeID, Name = m.Carrier.CarrierType.Name },
DocumentName = m.DocumentName,
DocumentDate = m.DocumentDate,
FileSize = SqlFunctions.DataLength(m.Document),
IsDocTypeRequired = m.CarrierComplianceType.IsRequired,
ExpirationDate = m.ExpirationDate,
Notes = m.Notes,
DeleteDateUTC = m.DeleteDateUTC,
DeletedByUser = m.DeletedByUser,
LastChangeDateUTC = m.LastChangeDateUTC,
LastChangedByUser = m.LastChangedByUser,
CreateDateUTC = m.CreateDateUTC,
CreatedByUser = m.CreatedByUser,
DocumentStateID = null,
DocumentNumber = null,
Endorsements = null,
Restrictions = null
}).Union(
db.DriverCompliances
.Include(m => m.Driver)
.Include(m => m.Driver.Carrier)
.Include(m => m.Driver.Carrier.CarrierType)
.Include(m => m.ComplianceType)
.Select(m => new DocumentComplianceViewModel
{
CategoryID = (int)ObjectDef.TYPE.DriverCompliance,
ID = m.ID,
CarrierID = m.Driver.CarrierID,
Carrier = new IdName { ID = m.Driver.CarrierID, Name = ((m.Driver.Carrier.DeleteDateUTC == null) ? "" : Strings.DELETED + " ") + m.Driver.Carrier.Name },
IDNumber = m.Driver.IDNumber,
DriverID = m.DriverID,
TruckID = 0,
TrailerID = 0,
DriverName = ((m.Driver.DeleteDateUTC == null) ? "" : Strings.DELETED + " ") + m.Driver.FirstName + " " + m.Driver.LastName ,
CarrierComplianceTypeID = 0,
ComplianceTypeID = m.ComplianceTypeID,
TruckComplianceTypeID = 0,
TrailerComplianceTypeID = 0,
ComplianceType = new IdName { ID = m.ComplianceTypeID, Name = m.ComplianceType.Name },
CarrierType = new IdName { ID = m.Driver.Carrier.CarrierTypeID, Name = m.Driver.Carrier.CarrierType.Name },
DocumentName = m.DocumentName,
DocumentDate = m.DocumentDate,
FileSize = SqlFunctions.DataLength(m.Document),
IsDocTypeRequired = m.ComplianceType.IsRequired,
ExpirationDate = m.ExpirationDate,
Notes = m.Notes,
DeleteDateUTC = m.DeleteDateUTC,
DeletedByUser = m.DeletedByUser,
LastChangeDateUTC = m.LastChangeDateUTC,
LastChangedByUser = m.LastChangedByUser,
CreateDateUTC = m.CreateDateUTC,
CreatedByUser = m.CreatedByUser,
DocumentStateID = m.DocumentStateID,
DocumentNumber = m.DocumentNumber,
Endorsements = m.Endorsements,
Restrictions = m.Restrictions
})).Union(
db.TruckCompliances
.Include(m => m.Truck)
.Include(m => m.Truck.Carrier)
.Include(m => m.Truck.Carrier.CarrierType)
.Include(m => m.TruckComplianceType)
.Select(m => new DocumentComplianceViewModel
{
CategoryID = (int)ObjectDef.TYPE.TruckCompliance,
ID = m.ID,
CarrierID = m.Truck.CarrierID,
Carrier = new IdName { ID = m.Truck.CarrierID, Name = ((m.Truck.Carrier.DeleteDateUTC == null) ? "" : Strings.DELETED + " ") + m.Truck.Carrier.Name },
IDNumber = ((m.Truck.DeleteDateUTC == null) ? "" : Strings.DELETED + " ") + m.Truck.IDNumber,
DriverID = 0,
TruckID = m.TruckID,
TrailerID = 0,
DriverName = null,
CarrierComplianceTypeID = 0,
ComplianceTypeID = 0,
TruckComplianceTypeID = m.TruckComplianceTypeID,
TrailerComplianceTypeID = 0,
ComplianceType = new IdName { ID = m.TruckComplianceTypeID, Name = m.TruckComplianceType.Name },
CarrierType = new IdName { ID = m.Truck.Carrier.CarrierTypeID, Name = m.Truck.Carrier.CarrierType.Name },
DocumentName = m.DocumentName,
DocumentDate = m.DocumentDate,
FileSize = SqlFunctions.DataLength(m.Document),
IsDocTypeRequired = m.TruckComplianceType.IsRequired,
ExpirationDate = m.ExpirationDate,
Notes = m.Notes,
DeleteDateUTC = m.DeleteDateUTC,
DeletedByUser = m.DeletedByUser,
LastChangeDateUTC = m.LastChangeDateUTC,
LastChangedByUser = m.LastChangedByUser,
CreateDateUTC = m.CreateDateUTC,
CreatedByUser = m.CreatedByUser,
DocumentStateID = null,
DocumentNumber = null,
Endorsements = null,
Restrictions = null
})).Union(
db.TrailerCompliances
.Include(m => m.Trailer)
.Include(m => m.Trailer.Carrier)
.Include(m => m.Trailer.Carrier.CarrierType)
.Include(m => m.TrailerComplianceType)
.Select(m => new DocumentComplianceViewModel
{
CategoryID = (int)ObjectDef.TYPE.TrailerCompliance,
ID = m.ID,
CarrierID = m.Trailer.CarrierID,
Carrier = new IdName { ID = m.Trailer.CarrierID, Name = ((m.Trailer.Carrier.DeleteDateUTC == null) ? "" : Strings.DELETED + " ") + m.Trailer.Carrier.Name },
IDNumber = ((m.Trailer.DeleteDateUTC == null) ? "" : Strings.DELETED + " ") + m.Trailer.IDNumber,
DriverID = 0,
TruckID = 0,
TrailerID = m.TrailerID,
DriverName = null,
CarrierComplianceTypeID = 0,
ComplianceTypeID = 0,
TruckComplianceTypeID = 0,
TrailerComplianceTypeID = m.TrailerComplianceTypeID,
ComplianceType = new IdName { ID = m.TrailerComplianceTypeID, Name = m.TrailerComplianceType.Name },
CarrierType = new IdName { ID = m.Trailer.Carrier.CarrierTypeID, Name = m.Trailer.Carrier.CarrierType.Name },
DocumentName = m.DocumentName,
DocumentDate = m.DocumentDate,
FileSize = SqlFunctions.DataLength(m.Document),
IsDocTypeRequired = m.TrailerComplianceType.IsRequired,
ExpirationDate = m.ExpirationDate,
Notes = m.Notes,
DeleteDateUTC = m.DeleteDateUTC,
DeletedByUser = m.DeletedByUser,
LastChangeDateUTC = m.LastChangeDateUTC,
LastChangedByUser = m.LastChangedByUser,
CreateDateUTC = m.CreateDateUTC,
CreatedByUser = m.CreatedByUser,
DocumentStateID = null,
DocumentNumber = null,
Endorsements = null,
Restrictions = null
}))
.Where(m => (m.ID == id && m.CategoryID == categoryID) || id == 0)
;
var result = data.ToDataSourceResult(request, ModelState);

The generated query looks like this (yeah it's obnoxious. This page used to be four pages but they wanted the data combined into one grid).

exec sp_executesql N'SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
COUNT(1) AS [A1]
FROM ( SELECT DISTINCT
[UnionAll3].[C1] AS [C1],
[UnionAll3].[C2] AS [C2],
[UnionAll3].[C3] AS [C3],
[UnionAll3].[C4] AS [C4],
[UnionAll3].[C5] AS [C5],
[UnionAll3].[C6] AS [C6],
[UnionAll3].[C7] AS [C7],
[UnionAll3].[C8] AS [C8],
[UnionAll3].[C9] AS [C9],
[UnionAll3].[C10] AS [C10],
[UnionAll3].[C11] AS [C11],
[UnionAll3].[C12] AS [C12],
[UnionAll3].[C13] AS [C13],
[UnionAll3].[C14] AS [C14],
[UnionAll3].[C15] AS [C15],
[UnionAll3].[C16] AS [C16],
[UnionAll3].[C17] AS [C17],
[UnionAll3].[C18] AS [C18],
[UnionAll3].[C19] AS [C19],
[UnionAll3].[C20] AS [C20],
[UnionAll3].[C21] AS [C21],
[UnionAll3].[C22] AS [C22],
[UnionAll3].[C23] AS [C23],
[UnionAll3].[C24] AS [C24],
[UnionAll3].[C25] AS [C25],
[UnionAll3].[C26] AS [C26],
[UnionAll3].[C27] AS [C27],
[UnionAll3].[C28] AS [C28],
[UnionAll3].[C29] AS [C29],
[UnionAll3].[C30] AS [C30],
[UnionAll3].[C31] AS [C31],
[UnionAll3].[C32] AS [C32],
[UnionAll3].[C33] AS [C33],
[UnionAll3].[C34] AS [C34]
FROM (SELECT
[Distinct2].[C1] AS [C1],
[Distinct2].[C2] AS [C2],
[Distinct2].[C3] AS [C3],
[Distinct2].[C4] AS [C4],
[Distinct2].[C5] AS [C5],
[Distinct2].[C6] AS [C6],
[Distinct2].[C7] AS [C7],
[Distinct2].[C8] AS [C8],
[Distinct2].[C9] AS [C9],
[Distinct2].[C10] AS [C10],
[Distinct2].[C11] AS [C11],
[Distinct2].[C12] AS [C12],
[Distinct2].[C13] AS [C13],
[Distinct2].[C14] AS [C14],
[Distinct2].[C15] AS [C15],
[Distinct2].[C16] AS [C16],
[Distinct2].[C17] AS [C17],
[Distinct2].[C18] AS [C18],
[Distinct2].[C19] AS [C19],
[Distinct2].[C20] AS [C20],
[Distinct2].[C21] AS [C21],
[Distinct2].[C22] AS [C22],
[Distinct2].[C23] AS [C23],
[Distinct2].[C24] AS [C24],
[Distinct2].[C25] AS [C25],
[Distinct2].[C26] AS [C26],
[Distinct2].[C27] AS [C27],
[Distinct2].[C28] AS [C28],
[Distinct2].[C29] AS [C29],
[Distinct2].[C30] AS [C30],
[Distinct2].[C31] AS [C31],
[Distinct2].[C32] AS [C32],
[Distinct2].[C33] AS [C33],
[Distinct2].[C34] AS [C34]
FROM ( SELECT DISTINCT
[UnionAll2].[C1] AS [C1],
[UnionAll2].[C2] AS [C2],
[UnionAll2].[C3] AS [C3],
[UnionAll2].[C4] AS [C4],
[UnionAll2].[C5] AS [C5],
[UnionAll2].[C6] AS [C6],
[UnionAll2].[C7] AS [C7],
[UnionAll2].[C8] AS [C8],
[UnionAll2].[C9] AS [C9],
[UnionAll2].[C10] AS [C10],
[UnionAll2].[C11] AS [C11],
[UnionAll2].[C12] AS [C12],
[UnionAll2].[C13] AS [C13],
[UnionAll2].[C14] AS [C14],
[UnionAll2].[C15] AS [C15],
[UnionAll2].[C16] AS [C16],
[UnionAll2].[C17] AS [C17],
[UnionAll2].[C18] AS [C18],
[UnionAll2].[C19] AS [C19],
[UnionAll2].[C20] AS [C20],
[UnionAll2].[C21] AS [C21],
[UnionAll2].[C22] AS [C22],
[UnionAll2].[C23] AS [C23],
[UnionAll2].[C24] AS [C24],
[UnionAll2].[C25] AS [C25],
[UnionAll2].[C26] AS [C26],
[UnionAll2].[C27] AS [C27],
[UnionAll2].[C28] AS [C28],
[UnionAll2].[C29] AS [C29],
[UnionAll2].[C30] AS [C30],
[UnionAll2].[C31] AS [C31],
[UnionAll2].[C32] AS [C32],
[UnionAll2].[C33] AS [C33],
[UnionAll2].[C34] AS [C34]
FROM (SELECT
[Distinct1].[C1] AS [C1],
[Distinct1].[C2] AS [C2],
[Distinct1].[C3] AS [C3],
[Distinct1].[C4] AS [C4],
[Distinct1].[C5] AS [C5],
[Distinct1].[C6] AS [C6],
[Distinct1].[C7] AS [C7],
[Distinct1].[C8] AS [C8],
[Distinct1].[C9] AS [C9],
[Distinct1].[C10] AS [C10],
[Distinct1].[C11] AS [C11],
[Distinct1].[C12] AS [C12],
[Distinct1].[C13] AS [C13],
[Distinct1].[C14] AS [C14],
[Distinct1].[C15] AS [C15],
[Distinct1].[C16] AS [C16],
[Distinct1].[C17] AS [C17],
[Distinct1].[C18] AS [C18],
[Distinct1].[C19] AS [C19],
[Distinct1].[C20] AS [C20],
[Distinct1].[C21] AS [C21],
[Distinct1].[C22] AS [C22],
[Distinct1].[C23] AS [C23],
[Distinct1].[C24] AS [C24],
[Distinct1].[C25] AS [C25],
[Distinct1].[C26] AS [C26],
[Distinct1].[C27] AS [C27],
[Distinct1].[C28] AS [C28],
[Distinct1].[C29] AS [C29],
[Distinct1].[C30] AS [C30],
[Distinct1].[C31] AS [C31],
[Distinct1].[C32] AS [C32],
[Distinct1].[C33] AS [C33],
[Distinct1].[C34] AS [C34]
FROM ( SELECT DISTINCT
[UnionAll1].[C1] AS [C1],
[UnionAll1].[ID] AS [C2],
[UnionAll1].[CarrierID] AS [C3],
[UnionAll1].[CarrierID1] AS [C4],
[UnionAll1].[C2] AS [C5],
[UnionAll1].[C3] AS [C6],
[UnionAll1].[C4] AS [C7],
[UnionAll1].[C5] AS [C8],
[UnionAll1].[C6] AS [C9],
[UnionAll1].[C7] AS [C10],
[UnionAll1].[CarrierComplianceTypeID] AS [C11],
[UnionAll1].[C8] AS [C12],
[UnionAll1].[C9] AS [C13],
[UnionAll1].[C10] AS [C14],
[UnionAll1].[CarrierComplianceTypeID1] AS [C15],
[UnionAll1].[Name] AS [C16],
[UnionAll1].[CarrierTypeID] AS [C17],
[UnionAll1].[Name1] AS [C18],
[UnionAll1].[DocumentName] AS [C19],
[UnionAll1].[DocumentDate] AS [C20],
[UnionAll1].[C11] AS [C21],
[UnionAll1].[IsRequired] AS [C22],
[UnionAll1].[ExpirationDate] AS [C23],
[UnionAll1].[Notes] AS [C24],
[UnionAll1].[DeleteDateUTC] AS [C25],
[UnionAll1].[DeletedByUser] AS [C26],
[UnionAll1].[LastChangeDateUTC] AS [C27],
[UnionAll1].[LastChangedByUser] AS [C28],
[UnionAll1].[CreateDateUTC] AS [C29],
[UnionAll1].[CreatedByUser] AS [C30],
[UnionAll1].[C12] AS [C31],
[UnionAll1].[C13] AS [C32],
[UnionAll1].[C14] AS [C33],
[UnionAll1].[C15] AS [C34]
FROM (SELECT
100 AS [C1],
[Extent1].[ID] AS [ID],
[Extent1].[CarrierID] AS [CarrierID],
[Extent1].[CarrierID] AS [CarrierID1],
CASE WHEN ([Extent2].[DeleteDateUTC] IS NULL) THEN [Extent2].[Name] ELSE N''[DEACTIVATED] '' + CASE WHEN ([Extent2].[Name] IS NULL) THEN N'''' ELSE [Extent2].[Name] END END AS [C2],
CASE WHEN (CASE WHEN ([Extent2].[DeleteDateUTC] IS NULL) THEN N'''' ELSE N''[DEACTIVATED] '' END IS NULL) THEN N'''' WHEN ([Extent2].[DeleteDateUTC] IS NULL) THEN N'''' ELSE N''[DEACTIVATED] '' END + CASE WHEN ([Extent2].[IDNumber] IS NULL) THEN N'''' ELSE [Extent2].[IDNumber] END AS [C3],
0 AS [C4],
0 AS [C5],
0 AS [C6],
CAST(NULL AS varchar(1)) AS [C7],
[Extent1].[CarrierComplianceTypeID] AS [CarrierComplianceTypeID],
0 AS [C8],
0 AS [C9],
0 AS [C10],
[Extent1].[CarrierComplianceTypeID] AS [CarrierComplianceTypeID1],
[Extent3].[Name] AS [Name],
[Extent2].[CarrierTypeID] AS [CarrierTypeID],
[Extent4].[Name] AS [Name1],
[Extent1].[DocumentName] AS [DocumentName],
[Extent1].[DocumentDate] AS [DocumentDate],
CAST(DATALENGTH([Extent1].[Document]) AS int) AS [C11],
[Extent3].[IsRequired] AS [IsRequired],
[Extent1].[ExpirationDate] AS [ExpirationDate],
[Extent1].[Notes] AS [Notes],
[Extent1].[DeleteDateUTC] AS [DeleteDateUTC],
[Extent1].[DeletedByUser] AS [DeletedByUser],
[Extent1].[LastChangeDateUTC] AS [LastChangeDateUTC],
[Extent1].[LastChangedByUser] AS [LastChangedByUser],
[Extent1].[CreateDateUTC] AS [CreateDateUTC],
[Extent1].[CreatedByUser] AS [CreatedByUser],
CAST(NULL AS int) AS [C12],
CAST(NULL AS varchar(1)) AS [C13],
CAST(NULL AS varchar(1)) AS [C14],
CAST(NULL AS varchar(1)) AS [C15]
FROM [dbo].[tblCarrierCompliance] AS [Extent1]
INNER JOIN [dbo].[viewCarrier] AS [Extent2] ON [Extent1].[CarrierID] = [Extent2].[ID]
INNER JOIN [dbo].[tblCarrierComplianceType] AS [Extent3] ON [Extent1].[CarrierComplianceTypeID] = [Extent3].[ID]
INNER JOIN [dbo].[tblCarrierType] AS [Extent4] ON [Extent2].[CarrierTypeID] = [Extent4].[ID]
WHERE ( NOT (( CAST(CHARINDEX(LOWER(N''[DEACTIVATED]''), LOWER(CASE WHEN ([Extent2].[DeleteDateUTC] IS NULL) THEN [Extent2].[Name] ELSE N''[DEACTIVATED] '' + CASE WHEN ([Extent2].[Name] IS NULL) THEN N'''' ELSE [Extent2].[Name] END END)) AS int)) > 0)) AND ( NOT (( CAST(CHARINDEX(LOWER(N''[DEACTIVATED]''), LOWER(CAST(NULL AS varchar(1)))) AS int)) > 0)) AND ([Extent1].[DeleteDateUTC] IS NULL) AND ([Extent1].[ID] = @p__linq__0 OR @p__linq__1 = 0)
UNION ALL
SELECT
101 AS [C1],
[Extent5].[ID] AS [ID],
[Extent6].[CarrierID] AS [CarrierID],
[Extent6].[CarrierID] AS [CarrierID1],
CASE WHEN (CASE WHEN ([Extent7].[DeleteDateUTC] IS NULL) THEN N'''' ELSE N''[DEACTIVATED] '' END IS NULL) THEN N'''' WHEN ([Extent7].[DeleteDateUTC] IS NULL) THEN N'''' ELSE N''[DEACTIVATED] '' END + CASE WHEN ([Extent7].[Name] IS NULL) THEN N'''' ELSE [Extent7].[Name] END AS [C2],
[Extent6].[IDNumber] AS [IDNumber],
[Extent5].[DriverID] AS [DriverID],
0 AS [C3],
0 AS [C4],
CASE WHEN (CASE WHEN ([Extent6].[DeleteDateUTC] IS NULL) THEN N'''' ELSE N''[DEACTIVATED] '' END IS NULL) THEN N'''' WHEN ([Extent6].[DeleteDateUTC] IS NULL) THEN N'''' ELSE N''[DEACTIVATED] '' END + CASE WHEN ([Extent6].[FirstName] IS NULL) THEN N'''' ELSE [Extent6].[FirstName] END + N'' '' + CASE WHEN ([Extent6].[LastName] IS NULL) THEN N'''' ELSE [Extent6].[LastName] END AS [C5],
0 AS [C6],
[Extent5].[ComplianceTypeID] AS [ComplianceTypeID],
0 AS [C7],
0 AS [C8],
[Extent5].[ComplianceTypeID] AS [ComplianceTypeID1],
[Extent8].[Name] AS [Name],
[Extent7].[CarrierTypeID] AS [CarrierTypeID],
[Extent9].[Name] AS [Name1],
[Extent5].[DocumentName] AS [DocumentName],
[Extent5].[DocumentDate] AS [DocumentDate],
CAST(DATALENGTH([Extent5].[Document]) AS int) AS [C9],
[Extent8].[IsRequired] AS [IsRequired],
[Extent5].[ExpirationDate] AS [ExpirationDate],
[Extent5].[Notes] AS [Notes],
[Extent5].[DeleteDateUTC] AS [DeleteDateUTC],
[Extent5].[DeletedByUser] AS [DeletedByUser],
[Extent5].[LastChangeDateUTC] AS [LastChangeDateUTC],
[Extent5].[LastChangedByUser] AS [LastChangedByUser],
[Extent5].[CreateDateUTC] AS [CreateDateUTC],
[Extent5].[CreatedByUser] AS [CreatedByUser],
[Extent5].[DocumentStateID] AS [DocumentStateID],
[Extent5].[DocumentNumber] AS [DocumentNumber],
[Extent5].[Endorsements] AS [Endorsements],
[Extent5].[Restrictions] AS [Restrictions]
FROM [dbo].[tblDriverCompliance] AS [Extent5]
INNER JOIN [dbo].[tblDriver] AS [Extent6] ON [Extent5].[DriverID] = [Extent6].[ID]
INNER JOIN [dbo].[viewCarrier] AS [Extent7] ON [Extent6].[CarrierID] = [Extent7].[ID]
INNER JOIN [dbo].[tblDriverComplianceType] AS [Extent8] ON [Extent5].[ComplianceTypeID] = [Extent8].[ID]
INNER JOIN [dbo].[tblCarrierType] AS [Extent9] ON [Extent7].[CarrierTypeID] = [Extent9].[ID]
WHERE ( NOT (( CAST(CHARINDEX(LOWER(N''[DEACTIVATED]''), LOWER(CASE WHEN (CASE WHEN ([Extent6].[DeleteDateUTC] IS NULL) THEN N'''' ELSE N''[DEACTIVATED] '' END IS NULL) THEN N'''' WHEN ([Extent6].[DeleteDateUTC] IS NULL) THEN N'''' ELSE N''[DEACTIVATED] '' END + CASE WHEN ([Extent6].[FirstName] IS NULL) THEN N'''' ELSE [Extent6].[FirstName] END + N'' '' + CASE WHEN ([Extent6].[LastName] IS NULL) THEN N'''' ELSE [Extent6].[LastName] END)) AS int)) > 0)) AND ([Extent5].[DeleteDateUTC] IS NULL) AND ( NOT (( CAST(CHARINDEX(LOWER(N''[DEACTIVATED]''), LOWER(CASE WHEN (CASE WHEN ([Extent7].[DeleteDateUTC] IS NULL) THEN N'''' ELSE N''[DEACTIVATED] '' END IS NULL) THEN N'''' WHEN ([Extent7].[DeleteDateUTC] IS NULL) THEN N'''' ELSE N''[DEACTIVATED] '' END + CASE WHEN ([Extent7].[Name] IS NULL) THEN N'''' ELSE [Extent7].[Name] END)) AS int)) > 0))) AS [UnionAll1]
) AS [Distinct1]
UNION ALL
SELECT
102 AS [C1],
[Extent10].[ID] AS [ID],
[Extent11].[CarrierID] AS [CarrierID],
[Extent11].[CarrierID] AS [CarrierID1],
CASE WHEN (CASE WHEN ([Extent12].[DeleteDateUTC] IS NULL) THEN N'''' ELSE N''[DEACTIVATED] '' END IS NULL) THEN N'''' WHEN ([Extent12].[DeleteDateUTC] IS NULL) THEN N'''' ELSE N''[DEACTIVATED] '' END + CASE WHEN ([Extent12].[Name] IS NULL) THEN N'''' ELSE [Extent12].[Name] END AS [C2],
CASE WHEN (CASE WHEN ([Extent11].[DeleteDateUTC] IS NULL) THEN N'''' ELSE N''[DEACTIVATED] '' END IS NULL) THEN N'''' WHEN ([Extent11].[DeleteDateUTC] IS NULL) THEN N'''' ELSE N''[DEACTIVATED] '' END + CASE WHEN ([Extent11].[IDNumber] IS NULL) THEN N'''' ELSE [Extent11].[IDNumber] END AS [C3],
0 AS [C4],
[Extent10].[TruckID] AS [TruckID],
0 AS [C5],
CAST(NULL AS varchar(1)) AS [C6],
0 AS [C7],
0 AS [C8],
[Extent10].[TruckComplianceTypeID] AS [TruckComplianceTypeID],
0 AS [C9],
[Extent10].[TruckComplianceTypeID] AS [TruckComplianceTypeID1],
[Extent13].[Name] AS [Name],
[Extent12].[CarrierTypeID] AS [CarrierTypeID],
[Extent14].[Name] AS [Name1],
[Extent10].[DocumentName] AS [DocumentName],
[Extent10].[DocumentDate] AS [DocumentDate],
CAST(DATALENGTH([Extent10].[Document]) AS int) AS [C10],
[Extent13].[IsRequired] AS [IsRequired],
[Extent10].[ExpirationDate] AS [ExpirationDate],
[Extent10].[Notes] AS [Notes],
[Extent10].[DeleteDateUTC] AS [DeleteDateUTC],
[Extent10].[DeletedByUser] AS [DeletedByUser],
[Extent10].[LastChangeDateUTC] AS [LastChangeDateUTC],
[Extent10].[LastChangedByUser] AS [LastChangedByUser],
[Extent10].[CreateDateUTC] AS [CreateDateUTC],
[Extent10].[CreatedByUser] AS [CreatedByUser],
CAST(NULL AS int) AS [C11],
CAST(NULL AS varchar(1)) AS [C12],
CAST(NULL AS varchar(1)) AS [C13],
CAST(NULL AS varchar(1)) AS [C14]
FROM [dbo].[tblTruckCompliance] AS [Extent10]
INNER JOIN [dbo].[tblTruck] AS [Extent11] ON [Extent10].[TruckID] = [Extent11].[ID]
INNER JOIN [dbo].[viewCarrier] AS [Extent12] ON [Extent11].[CarrierID] = [Extent12].[ID]
INNER JOIN [dbo].[tblTruckComplianceType] AS [Extent13] ON [Extent10].[TruckComplianceTypeID] = [Extent13].[ID]
INNER JOIN [dbo].[tblCarrierType] AS [Extent14] ON [Extent12].[CarrierTypeID] = [Extent14].[ID]
WHERE ( NOT (( CAST(CHARINDEX(LOWER(N''[DEACTIVATED]''), LOWER(CAST(NULL AS varchar(1)))) AS int)) > 0)) AND ([Extent10].[DeleteDateUTC] IS NULL) AND ( NOT (( CAST(CHARINDEX(LOWER(N''[DEACTIVATED]''), LOWER(CASE WHEN (CASE WHEN ([Extent12].[DeleteDateUTC] IS NULL) THEN N'''' ELSE N''[DEACTIVATED] '' END IS NULL) THEN N'''' WHEN ([Extent12].[DeleteDateUTC] IS NULL) THEN N'''' ELSE N''[DEACTIVATED] '' END + CASE WHEN ([Extent12].[Name] IS NULL) THEN N'''' ELSE [Extent12].[Name] END)) AS int)) > 0))) AS [UnionAll2]
) AS [Distinct2]
UNION ALL
SELECT
103 AS [C1],
[Extent15].[ID] AS [ID],
[Extent16].[CarrierID] AS [CarrierID],
[Extent16].[CarrierID] AS [CarrierID1],
CASE WHEN (CASE WHEN ([Extent17].[DeleteDateUTC] IS NULL) THEN N'''' ELSE N''[DEACTIVATED] '' END IS NULL) THEN N'''' WHEN ([Extent17].[DeleteDateUTC] IS NULL) THEN N'''' ELSE N''[DEACTIVATED] '' END + CASE WHEN ([Extent17].[Name] IS NULL) THEN N'''' ELSE [Extent17].[Name] END AS [C2],
CASE WHEN (CASE WHEN ([Extent16].[DeleteDateUTC] IS NULL) THEN N'''' ELSE N''[DEACTIVATED] '' END IS NULL) THEN N'''' WHEN ([Extent16].[DeleteDateUTC] IS NULL) THEN N'''' ELSE N''[DEACTIVATED] '' END + CASE WHEN ([Extent16].[IDNumber] IS NULL) THEN N'''' ELSE [Extent16].[IDNumber] END AS [C3],
0 AS [C4],
0 AS [C5],
[Extent15].[TrailerID] AS [TrailerID],
CAST(NULL AS varchar(1)) AS [C6],
0 AS [C7],
0 AS [C8],
0 AS [C9],
[Extent15].[TrailerComplianceTypeID] AS [TrailerComplianceTypeID],
[Extent15].[TrailerComplianceTypeID] AS [TrailerComplianceTypeID1],
[Extent18].[Name] AS [Name],
[Extent17].[CarrierTypeID] AS [CarrierTypeID],
[Extent19].[Name] AS [Name1],
[Extent15].[DocumentName] AS [DocumentName],
[Extent15].[DocumentDate] AS [DocumentDate],
CAST(DATALENGTH([Extent15].[Document]) AS int) AS [C10],
[Extent18].[IsRequired] AS [IsRequired],
[Extent15].[ExpirationDate] AS [ExpirationDate],
[Extent15].[Notes] AS [Notes],
[Extent15].[DeleteDateUTC] AS [DeleteDateUTC],
[Extent15].[DeletedByUser] AS [DeletedByUser],
[Extent15].[LastChangeDateUTC] AS [LastChangeDateUTC],
[Extent15].[LastChangedByUser] AS [LastChangedByUser],
[Extent15].[CreateDateUTC] AS [CreateDateUTC],
[Extent15].[CreatedByUser] AS [CreatedByUser],
CAST(NULL AS int) AS [C11],
CAST(NULL AS varchar(1)) AS [C12],
CAST(NULL AS varchar(1)) AS [C13],
CAST(NULL AS varchar(1)) AS [C14]
FROM [dbo].[tblTrailerCompliance] AS [Extent15]
INNER JOIN [dbo].[tblTrailer] AS [Extent16] ON [Extent15].[TrailerID] = [Extent16].[ID]
LEFT OUTER JOIN [dbo].[viewCarrier] AS [Extent17] ON [Extent16].[CarrierID] = [Extent17].[ID]
INNER JOIN [dbo].[tblTrailerComplianceType] AS [Extent18] ON [Extent15].[TrailerComplianceTypeID] = [Extent18].[ID]
LEFT OUTER JOIN [dbo].[tblCarrierType] AS [Extent19] ON [Extent17].[CarrierTypeID] = [Extent19].[ID]
WHERE ( NOT (( CAST(CHARINDEX(LOWER(N''[DEACTIVATED]''), LOWER(CAST(NULL AS varchar(1)))) AS int)) > 0)) AND ([Extent15].[DeleteDateUTC] IS NULL) AND ( NOT (( CAST(CHARINDEX(LOWER(N''[DEACTIVATED]''), LOWER(CASE WHEN (CASE WHEN ([Extent17].[DeleteDateUTC] IS NULL) THEN N'''' ELSE N''[DEACTIVATED] '' END IS NULL) THEN N'''' WHEN ([Extent17].[DeleteDateUTC] IS NULL) THEN N'''' ELSE N''[DEACTIVATED] '' END + CASE WHEN ([Extent17].[Name] IS NULL) THEN N'''' ELSE [Extent17].[Name] END)) AS int)) > 0))) AS [UnionAll3]
) AS [Distinct3]
WHERE (([Distinct3].[C2] = @p__linq__2) AND ([Distinct3].[C1] = @p__linq__3)) OR (0 = @p__linq__4)
) AS [GroupBy1]',N'@p__linq__0 int,@p__linq__1 int,@p__linq__2 int,@p__linq__3 int,@p__linq__4 int',@p__linq__0=0,@p__linq__1=0,@p__linq__2=0,@p__linq__3=0,@p__linq__4=0

The field that is breaking is DriverName. I don't actually see in linq which field that is, but i believe it is c6. the field has a doesnotcontain [DEACTIVATED] filter on it. That is the filter that is filtering out null values on the field. If i change the above code to driverName = "", from driverNaem = null, it fixes it.

that doesn't apply the

ADMIN

Eyup

Posted on: 27 Oct 2021 08:31

Hello John,

Yeah, generally I test the scenarios with dummy LINQ collection and usually it is more than enough.

Can you specify what kind of database you have in mind - SQL, EF, Oracle?

Regards,
Eyup
Progress Telerik

Remote troubleshooting is now easier with Telerik Fiddler Jam. Get the full context to end-users' issues in just three steps! Start your trial here - https://www.telerik.com/fiddler-jam.

John

Posted on: 20 Oct 2021 13:14

i could not reproduce it with your sample. I suspect it's because i'm tied to a datasource and this isn't. As you know there's a different version of todatasourceresult for linq for objects vs link for datasets. It seems to be working in the linq for objects example. Do you have a sample with a database?

ADMIN

Eyup

Posted on: 19 Oct 2021 08:35

Hi John,

Thank you for sharing your specific solution with our community.

Can you reproduce this behavior with the attached sample? Could you modify it to demonstrate the problem and send it back to us for further investigation?

Regards,
Eyup
Progress Telerik

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

Attached Files:

KendoGridBasicAppNew.zip