Remote Filter With Null Datetime

[alpergunay]

Hi,

Is there any way to filter rows with NULL datetime like selecting "Blanks" on string field ?

Thanks,
Best Regards

[AlekseyMartynov]

Absolutely.
You can try such a filter in the Sample project:

[alpergunay]

Thanks for the answer.

Although our datetime field is nullable, we are getting "Nullable value must have a value" when we want to use header filter. I copied the error message below. Do you have any idea about this problem ?

System.InvalidOperationException: Nullable object must have a value.
         at lambda_method(Closure , TransparentIdentifier`2 )
         at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider._GroupBy[TSource,TKey,TElement](IEnumerable`1 source, Func`2 keySelector, Func`2 elementSelector)+MoveNext()
         at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source, Int32& length)
         at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
         at System.Linq.OrderedEnumerable`1.GetEnumerator()+MoveNext()
         at System.Linq.Enumerable.SelectIPartitionIterator`2.MoveNext()
         at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()

[AlekseyMartynov]

Can be an EF Core bug.
I see that they have open issues with similar stack traces:

• Simple query filter breaks simple projection dotnet/efcore#13517
• 'Nullable object must have a value' with bool? value == true dotnet/efcore#14040

As a possible workaround, try setting loadOptions.RemoteGrouping = false before the DataSourceLoader.Load call.

[alpergunay]

Is there any way to disable date grouping on header filter (year, month, day) for a specific column ?

[AlekseyMartynov]

It's possible to provide a custom data source for the column header filter.

Example with an array:

{
    dataField: "orderDate",
    dataType: "date",
    headerFilter: {
        dataSource: [
            { text: "7/5/1996", value: new Date("1996-07-05") },
            { text: "7/8/1996", value: new Date("1996-07-08") }
        ]
    }
}

In this case, interval grouping is not applied.

[alpergunay]

We have millions of rows in a table and disabling remote grid is not an option.

[AlekseyMartynov]

OK, I'd like to help debug the failing LINQ query.

Would you please share the related query string and the source collection used in DataSourceLoader?
Based on this info, I can construct a direct query against the source. If we see that it fails too, you'll be able to submit it to EF Core issue tracker for further assistance.

You can post here or submit a private question to DevExpress Support Center.

public object Orders(DataSourceLoadOptions loadOptions) {
    // 1. Copy the value of the queryString variable
    var queryString = HttpUtility.UrlDecode(Request.QueryString.ToString());

    // 2. Show a code snippet that describes the source collection used in DataSourceLoader
    var source = _nwind.Orders;
    return DataSourceLoader.Load(source, loadOptions);
}

[alpergunay]

Thank you very much for your help. Below you can find the requested information. Please let me know if you have any question.

Query String :
?group=[{"selector":"productionDate","groupInterval":"year","isExpanded":true},{"selector":"productionDate","groupInterval":"month","isExpanded":true},{"selector":"productionDate","groupInterval":"day","isExpanded":false}]&recordStatusId=1&_=1549385248739

Linq Query :

var listQuery = from s in _dbContext.Stocks
                            join ca in _dbContext.Accounts on s.AccountId equals ca.Id
                            join im in _dbContext.ItemMasters on s.ItemMasterId equals im.Id
                            join ipk in _dbContext.ItemPackTypes on s.ItemPackTypeId equals ipk.Id
                            join pk in _dbContext.PackTypes on ipk.PackTypeId equals pk.Id
                            join l in _dbContext.Locations on s.LocationId equals l.Id
                            join sl in _dbContext.StorageLocations on s.StorageLocationId equals sl.Id into s_sl
                            from sl in s_sl.DefaultIfEmpty()
                            join c in _dbContext.Countries on s.CountryId equals c.Id into s_c
                            from c in s_c.DefaultIfEmpty()
                            join b in _dbContext.HandlingUnits on s.BoxId equals b.Id into s_b
                            from b in s_b.DefaultIfEmpty()
                            join p in _dbContext.HandlingUnits on s.PalletId equals p.Id into s_p
                            from p in s_p.DefaultIfEmpty()
                            join cm in _dbContext.Campaigns on s.CampaignId equals cm.Id into s_cm
                            from cm in s_cm.DefaultIfEmpty()
                            join sa in _dbContext.Accounts on s.SupplierAccountId equals sa.Id into s_sa
                            from sa in s_sa.DefaultIfEmpty()
                            join dr in _dbContext.DamageReasons on s.DamageReasonId equals dr.Id into grd_dr
                            from dr in grd_dr.DefaultIfEmpty()
                            join drt in _dbContext.DamageReasonTypes on s.DamageReasonTypeId equals drt.Id into grd_drt
                            from drt in grd_drt.DefaultIfEmpty()
                            join gr in _dbContext.GoodReceipts on s.GoodreceiptId equals gr.Id
                            join grSt in _dbContext.OrderStatus on gr.OrderStatusId equals grSt.Id
                            join ss in _dbContext.StockStatuses on s.StockStatusId equals ss.Id
                            join rs in _dbContext.RecordStatus on s.RecordStatusId equals rs.Id
                            join ls in _dbContext.LocationStatus on l.LocationStatusId equals ls.Id
                            into grd_ls
                            from ls in grd_ls.DefaultIfEmpty()
                            join lls in _dbContext.LocationLockReasons on l.LocationLockReasonId equals lls.Id
                            into grd_lls
                            from lls in grd_lls.DefaultIfEmpty()
                            where s.SiteId == _user.SiteId
                             && (model.AccountId == null || s.AccountId == model.AccountId)
                            orderby s.ChangeTime descending
                            select new StockResponseModel
                            {
                                Id = s.Id,
                                CustomerAccount = ca.Description,
                                ItemMasterId = s.ItemMasterId,
                                ItemMaster = im.Description,
                                ItemCode = im.Code,
                                ItemPackTypeId = s.ItemPackTypeId,
                                ItemPackType = pk.Description,
                                Quantity = s.Quantity,
                                FreeQuantity = s.FreeQuantity,
                                Location = l.Code,
                                StorageLocation = sl.Description,
                                LotNumber = s.LotNumber,
                                ProductionDate = s.ProductionDate ,
                                ExpireDate = s.ExpireDate ,
                                ShelfLife = s.ShelfLife,
                                Country = c.Description,
                                Season = s.Season,
                                Box = b.Number,
                                Pallet = p.Number,
                                Campaign = cm.Description,
                                SerialNo = s.SerialNo,
                                Imei = s.Imei,
                                SupplierAccount = sa.Description,
                                DamageReason = dr.Description,
                                DamageReasonType = drt.Description,
                                GoodreceiptOrderNumber = gr.OrderNumber,
                                GoodReceiptOrderStatus = grSt.Description,
                                StockStatus = ss.Description,
                                Var01String = s.Var01String,
                                Var02String = s.Var02String,
                                Var03String = s.Var03String,
                                Var04String = s.Var04String,
                                Var05String = s.Var05String,
                                Var01Time = s.Var01Time,
                                Var02Time = s.Var02Time,
                                Var03Time = s.Var03Time,
                                Var01Decimal = s.Var01Decimal,
                                Var02Decimal = s.Var02Decimal,
                                Var03Decimal = s.Var03Decimal,
                                RecordStatusId = s.RecordStatusId,
                                RecordStatus = rs.Description,
                                LocationStatus = ls.Description,
                                LocationLockReason = lls.Description
                            };

Response Model :

 public class StockResponseModel
    {
        public long Id { get; set; }
        public string CustomerAccount { get; set; }
        public long ItemMasterId { get; set; }
        public string ItemMaster { get; set; }
        public string ItemCode { get; set; }
        public long ItemPackTypeId { get; set; }
        public string ItemPackType { get; set; }
        public int Quantity { get; set; }
        public int FreeQuantity { get; set; }
        public string Location { get; set; }
        public string StorageLocation { get; set; }
        public string LotNumber { get; set; }
        public DateTime? ProductionDate { get; set; }
        public DateTime? ExpireDate { get; set; }
        public int? ShelfLife { get; set; }
        public string Country { get; set; }
        public string Season { get; set; }
        public string Box { get; set; }
        public string Pallet { get; set; }
        public string Campaign { get; set; }
        public string SerialNo { get; set; }
        public string Imei { get; set; }
        public string SupplierAccount { get; set; }
        public string DamageReason { get; set; }
        public string DamageReasonType { get; set; }
        public string GoodreceiptOrderNumber { get; set; }
        public string GoodReceiptOrderStatus { get; set; }
        public string StockStatus { get; set; }
        public string Var01String { get; set; }
        public string Var02String { get; set; }
        public string Var03String { get; set; }
        public string Var04String { get; set; }
        public string Var05String { get; set; }
        public DateTime? Var01Time { get; set; }
        public DateTime? Var02Time { get; set; }
        public DateTime? Var03Time { get; set; }
        public decimal? Var01Decimal { get; set; }
        public decimal? Var02Decimal { get; set; }
        public decimal? Var03Decimal { get; set; }
        public long RecordStatusId { get; set; }
        public string RecordStatus { get; set; }
        public string LocationStatus { get; set; }
        public string LocationLockReason { get; set; }
    }

[AlekseyMartynov]

Thanks.

This is the intermediate query that DataSourceLoader executes internally:

var dateGroups = listQuery
    .GroupBy(obj => new {
        Y = (int?)obj.OrderDate.Value.Year,
        M = (int?)obj.OrderDate.Value.Month,
        D = (int?)obj.OrderDate.Value.Day
    })
    .OrderBy(g => g.Key.Y)
    .ThenBy(g => g.Key.M)
    .ThenBy(g => g.Key.D)
    .Select(g => new {
        Count = g.Count(),
        g.Key.Y,
        g.Key.M,
        g.Key.D
    })
    .ToArray();

Please check whether it runs OK or also fails.

[alpergunay]

Hi,
We found the problem. In our query there was a type conversion to "StockResponseModel". We cleared this and used anonymous type then problem has been solved. EF generated correct query.

Thank you very much for your help.

[AlekseyMartynov]

Great. Closing this.

[AlekseyMartynov]

Related EF Core issue:
dotnet/efcore#12284