C#•13mo ago

✅ Is it possible to simplify this litedb query?

All I want is to get total count, count of not delivered, not read, not confirmed. Actually I don't is it possible to do in 1 query

var totalNotificationsCount = await _repository
    .Query()
    .Where(model => DateTimeOffset.Now <= model.ExpiresAt)
    .Where(model => model.To
        .Contains(operatorName))
    .Count();

var notDeliveredNotificationsCount = await _repository
    .Query()
    .Where(model => DateTimeOffset.Now <= model.ExpiresAt)
    .Where(model => model.To
        .Contains(operatorName))
    .Where(model => !model.MessageState.IsDelivered)
    .Count();

var notReadNotificationsCount = await _repository
    .Query()
    .Where(model => DateTimeOffset.Now <= model.ExpiresAt)
    .Where(model => model.To
        .Contains(operatorName))
    .Where( model => model.MessageState.IsDelivered 
           && !model.MessageState.IsRead )
    .Count();

var notConfirmedNotificationsCount = await _repository
    .Query()
    .Where(model => DateTimeOffset.Now <= model.ExpiresAt)
    .Where(model => model.To
        .Contains(operatorName))
    .Where( model => model.MessageState.IsDelivered 
           && model.MessageState.IsRead
           && !model.ConfirmationState.RequiresConfirmation)
    .Count();

var totalNotificationsCount = await _repository
    .Query()
    .Where(model => DateTimeOffset.Now <= model.ExpiresAt)
    .Where(model => model.To
        .Contains(operatorName))
    .Count();

var notDeliveredNotificationsCount = await _repository
    .Query()
    .Where(model => DateTimeOffset.Now <= model.ExpiresAt)
    .Where(model => model.To
        .Contains(operatorName))
    .Where(model => !model.MessageState.IsDelivered)
    .Count();

var notReadNotificationsCount = await _repository
    .Query()
    .Where(model => DateTimeOffset.Now <= model.ExpiresAt)
    .Where(model => model.To
        .Contains(operatorName))
    .Where( model => model.MessageState.IsDelivered 
           && !model.MessageState.IsRead )
    .Count();

var notConfirmedNotificationsCount = await _repository
    .Query()
    .Where(model => DateTimeOffset.Now <= model.ExpiresAt)
    .Where(model => model.To
        .Contains(operatorName))
    .Where( model => model.MessageState.IsDelivered 
           && model.MessageState.IsRead
           && !model.ConfirmationState.RequiresConfirmation)
    .Count();

3 Replies

SWEETPONYOP•13mo ago

I can write one query:

var basicQuery = _repository                                
    .Query()                                                
    .Where(model => DateTimeOffset.Now <= model.ExpiresAt)
    .Where(model => model.To                               
        .Contains(operatorName));

var basicQuery = _repository                                
    .Query()                                                
    .Where(model => DateTimeOffset.Now <= model.ExpiresAt)
    .Where(model => model.To                               
        .Contains(operatorName));

and then filter it.. I don't know

Omnissiah•13mo ago

after you've define basicQuery you can use that to make the others

var totalNotificationsCount = await basicQuery.CountAsync();
var notDeliveredNotificationsCount = await baicQuery
    .Where(model => !model.MessageState.IsDelivered)
    .CountAsync();
// and so on

var totalNotificationsCount = await basicQuery.CountAsync();
var notDeliveredNotificationsCount = await baicQuery
    .Where(model => !model.MessageState.IsDelivered)
    .CountAsync();
// and so on

SWEETPONYOP•13mo ago

thanks

Gaming

Programming

✅ Is it possible to simplify this litedb query?