Witam serdecznie :)
Próbuję pobrać obiekty z bazy danych i umieścić je w kontrolce DataTable. Napisałem metodę serwisu i zastanawiam się, czy jak danych będzie zbyt wiele, to czy w pamięci nie będę miał zbyt wiele danych, bowiem Skip() i Take() wykonuję dopiero w kolejnej instrukcji. Możecie spojrzeć na to fachowym okiem?
Alternatywnie, czy da się w EFCore wybrać kierunek sortowania coś na zasadzie OrderBy(x => x.Group, eSortDirection.Ascending) ?. Wtedy zmieściłbym wszystko w jednym zapytaniu. A może macie jakieś porady co to tego wycinka kodu?
public class Vehicle
{
public Guid Id { get; set; }
public string Group { get; set; }
public string Producer { get; set; }
public string Model { get; set; }
public string RegistrationNumber { get; set; }
public int YearOfProduction { get; set; }
public string ChassisNumber { get; set; }
public eTypeOfDrive TypeOfDrive { get; set; }
public double AllowedPackage { get; set; }
public eVehicleMarkingType VehicleMarkingType { get; set; }
public int NumberOfSeats { get; set; }
public string TypeOfPurchase { get; set; }
public string Description { get; set; }
public Guid? DrivingPrivilegesDefinitionId { get; set; }
public virtual DrivingPrivilegesDefinition DrivingPrivilegesDefinition { get; set; }
// =======================================================================
public bool HasTowingHitch { get; set; }
public eFuelType FuelType { get; set; }
public eUnitOfFuel UnitOfFuel { get; set; }
public virtual List<VehicleInstitutionAssociate> VehicleInstitutionAssociates { get; set; }
// Określić startowy przebieg
// Wprowadzić termin badania technicznego
}
public class VehicleFilters : BaseFilters<eSortPropertyNames>
{
public enum eSortPropertyNames
{
Group,
Producer,
Model,
RegistrationNumber,
YearOfProduction,
ChassisNumber,
TypeOfDrive,
AllowedPackage,
VehicleMarkingType,
NumberOfSeats,
TypeOfPurchase,
Description,
Institution,
NoSort
}
public string Group { get; set; }
public string Producer { get; set; }
public string Model { get; set; }
public string RegistrationNumber { get; set; }
public int? MinYearOfProduction { get; set; }
public int? MaxYearOfProduction { get; set; }
public string ChassisNumber { get; set; }
public eTypeOfDrive? TypeOfDrive { get; set; }
public double? MinAllowedPackage { get; set; }
public double? MaxAllowedPackage { get; set; }
public eVehicleMarkingType? VehicleMarkingType { get; set; }
public int? MinNumberOfSeats { get; set; }
public int? MaxNumberOfSeats { get; set; }
public string TypeOfPurchase { get; set; }
public string Description { get; set; }
public Guid[] VehiclesId { get; set; }
public Guid[] InstitutionsId { get; set; }
public Guid[] DrivingPrivilegeDefinitionsId { get; set; }
}
public async Task<FilterResult<Vehicle>> GetAllForViewAsync(VehicleFilters filters)
{
IEnumerable<Vehicle> vehicles = await _dbContext.Vehicles
.Include(x => x.DrivingPrivilegesDefinition)
.Include(x => x.VehicleInstitutionAssociates)
.Where(x => string.IsNullOrEmpty(filters.GlobalFilter) ? true : (
(!string.IsNullOrEmpty(x.Group) && x.Group.Contains(filters.GlobalFilter)) ||
(!string.IsNullOrEmpty(x.Producer) && x.Producer.Contains(filters.GlobalFilter)) ||
(!string.IsNullOrEmpty(x.Model) && x.Model.Contains(filters.GlobalFilter)) ||
(!string.IsNullOrEmpty(x.RegistrationNumber) && x.RegistrationNumber.Contains(filters.GlobalFilter)) ||
(!string.IsNullOrEmpty(x.ChassisNumber) && x.ChassisNumber.Contains(filters.GlobalFilter))
))
.Where(x => string.IsNullOrEmpty(filters.Group) ? true : x.Group.Contains(filters.Group))
.Where(x => string.IsNullOrEmpty(filters.Producer) ? true : x.Group.Contains(filters.Producer))
.Where(x => string.IsNullOrEmpty(filters.Model) ? true : x.Group.Contains(filters.Model))
.Where(x => string.IsNullOrEmpty(filters.RegistrationNumber) ? true : x.Group.Contains(filters.RegistrationNumber))
.Where(x => !filters.MinYearOfProduction.HasValue ? true : x.YearOfProduction >= filters.MinYearOfProduction.Value)
.Where(x => !filters.MaxYearOfProduction.HasValue ? true : x.YearOfProduction <= filters.MaxYearOfProduction.Value)
.Where(x => string.IsNullOrEmpty(filters.ChassisNumber) ? true : x.ChassisNumber.Contains(filters.ChassisNumber))
.Where(x => !filters.TypeOfDrive.HasValue ? true : x.TypeOfDrive == filters.TypeOfDrive.Value)
.Where(x => !filters.MinAllowedPackage.HasValue ? true : x.AllowedPackage >= filters.MinAllowedPackage.Value)
.Where(x => !filters.MaxAllowedPackage.HasValue ? true : x.AllowedPackage <= filters.MaxAllowedPackage.Value)
.Where(x => !filters.VehicleMarkingType.HasValue ? true : x.VehicleMarkingType == filters.VehicleMarkingType.Value)
.Where(x => !filters.MinNumberOfSeats.HasValue ? true : x.NumberOfSeats >= filters.MinNumberOfSeats.Value)
.Where(x => !filters.MaxNumberOfSeats.HasValue ? true : x.NumberOfSeats <= filters.MaxNumberOfSeats.Value)
.Where(x => string.IsNullOrEmpty(filters.TypeOfPurchase) ? true : x.TypeOfPurchase.Contains(filters.TypeOfPurchase))
.Where(x => string.IsNullOrEmpty(filters.Description) ? true : x.Description.Contains(filters.Description))
.Where(x => filters.VehiclesId == null ? true : filters.VehiclesId.Contains(x.Id))
.Where(x => filters.DrivingPrivilegeDefinitionsId == null ? true : (x.DrivingPrivilegesDefinitionId.HasValue && filters.DrivingPrivilegeDefinitionsId.Contains(x.DrivingPrivilegesDefinitionId.Value)))
.Where(x => filters.InstitutionsId == null ? true : (x.VehicleInstitutionAssociates.Count() > 0 && filters.InstitutionsId.Contains(x.VehicleInstitutionAssociates.OrderByDescending(x => x.AssociateTime).Select(x => x.InstitutionId).First())))
.ToArrayAsync();
var filteredLength = vehicles.Count();
switch (filters.SortPropertyName)
{
case VehicleFilters.eSortPropertyNames.NoSort:
vehicles = (filters.SortOrder == Microsoft.Data.SqlClient.SortOrder.Ascending) ?
vehicles.OrderBy(x => x.Group + x.Producer + x.Model + x.RegistrationNumber).Skip(filters.Skip).Take(filters.Length) :
vehicles.OrderByDescending(x => x.Group + x.Producer + x.Model + x.RegistrationNumber).Skip(filters.Skip).Take(filters.Length);
break;
case VehicleFilters.eSortPropertyNames.AllowedPackage:
vehicles = (filters.SortOrder == Microsoft.Data.SqlClient.SortOrder.Ascending) ?
vehicles.OrderBy(x => x.AllowedPackage).Skip(filters.Skip).Take(filters.Length) :
vehicles.OrderByDescending(x => x.AllowedPackage).Skip(filters.Skip).Take(filters.Length);
break;
}
return await Task.FromResult(new FilterResult<Vehicle>(vehicles))
}