none
EF Core Order IQueryable after Union RRS feed

  • Question

  • Using EF Core 2.1

    I couldn't get my query to sort until I ToListed each IQueryable in the Union. It worked until I tried to project to a new object or sort. It gave an error that a property was missing on one of the entities, even though the IQueryable had already been projected to a DTO and I was no longer using the entity directly. 

    The code below works fine but take out the ToList on each element within the union and it fails again. Error Message - "InvalidOperationException: The property '' on entity type 'OtherVehicle' could not be found. Ensure that the property exists and has been included in the model.

    return _context.Vehicle
    	.WhereIf(!string.IsNullOrWhiteSpace(Search), v => v.RegistrationNum.Contains(Search.Replace(" ", "").Trim()))
    	.Join(_context.OtherVehicle
    		.Include(v => v.OtherInspectionList)
    		.Where(v => v.OtherInspectionList.InspectionDate >= FromDate && v.OtherInspectionList.InspectionDate <= ToDate)
    		, t => t.RegistrationNum, s => s.Registration, (veh, inspection) => new VehicleBaseDTO
    		{
    			Id = inspection.OtherInspectionList.Id,
    			InspectionID = (int)inspection.OtherInspectionListID,
    			Registration = inspection.Registration,
    			InspectionDate = (DateTime)inspection.OtherInspectionList.InspectionDate,
    			InspectionSource = "AAA"
    		})
    		.ToList()
    		.Union(_context.Vehicle
    			.WhereIf(!string.IsNullOrWhiteSpace(Search), v => v.RegistrationNum.Contains(Search.Replace(" ", "").Trim()))
    			.Join(_context.CollDelJob
    				.Include(v => v.FDVehicle)
    				.Where(v => v.InspectionDate >= FromDate && v.InspectionDate <= ToDate && v.VehicleId != null)
    				, t => t.RegistrationNum, c => c.FDVehicle.RegistrationNum, (veh, inspection) => new VehicleBaseDTO
    				{
    					Id = (int)inspection.Id,
    					InspectionID = (int)inspection.Id,
    					Registration = inspection.FDVehicle.RegistrationNum,
    					InspectionDate = (DateTime)inspection.InspectionDate,
    					InspectionSource = "PDA"
    				}))
    				.ToList()
    	.Select(u => new VehicleBaseDTO
    	{
    		Id = (int)u.Id,
    		InspectionID = (int)u.Id,
    		Registration = u.Registration,
    		InspectionDate = (DateTime)u.InspectionDate,
    		InspectionSource = u.InspectionSource
    	})
    	.OrderBy(v => v.Registration)
    	.Distinct()
    	.PagedResults(page, pageSize);

    Sorting works fine elsewhere in the application on IQueryables and IQueryables projected to a new type so why not after the union?


    Thursday, May 31, 2018 9:49 PM

All replies

  • A ToList() instances a List<T> of a concrete objects, the DTO.

    https://msdn.microsoft.com/en-us/library/bb342261(v=vs.110).aspx

    <copy>

    The ToList<TSource>(IEnumerable<TSource>) method forces immediate query evaluation and returns a 

    <end>

    Thursday, May 31, 2018 10:00 PM
  • Hi jonathanforster,

    >>Sorting works fine elsewhere in the application on IQueryables and IQueryables projected to a new type so why not after the union?

    Could you please share your model class and related DTO class, which I could reproduce the issue on my side. it will be beneficial to resolve the issue.

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 4, 2018 1:37 AM
    Moderator
  • public class Vehicle
        {
            [Key]
            public string RegistrationNum { get; set; }
            public CollDelJob CollDelJob { get; set; }
        }

     public partial class OtherVehicle
        {
            public int Id { get; set; }
            public int? VehicleId { get; set; }
            public string Registration { get; set; }
            public int? OtherInspectionListId { get; set; }
            public string Make { get; set; }
            public string MakeCode { get; set; }
            public string Model { get; set; }
            public string ModelCode { get; set; }
            public string VariantDescription { get; set; }
            public string VariantCode { get; set; }
            public string Colour { get; set; }
            public string ColourCode { get; set; }
            public string IntColourDescription { get; set; }
            public string IntColourCode { get; set; }
            public int? ModelYear { get; set; }
            public string BuildMonth { get; set; }
            public string BodyPlan { get; set; }
            public string BodyPlanCode { get; set; }
            public string SpecCode { get; set; }
            public string FuelType { get; set; }
            public string FuelTypeCode { get; set; }
            public string Transmission { get; set; }
            public string TransmissionCode { get; set; }
            public string GearBox { get; set; }
            public string GearBoxCode { get; set; }
            public string OdoUnitCode { get; set; }
            public string Delivery { get; set; }
            public string DeliveryCode { get; set; }
            public string DriverSideDescription { get; set; }
            public string DriverSideCode { get; set; }
            public string Gears { get; set; }
            public decimal? EngineSize { get; set; }
            public string EngineSizeUnit { get; set; }
            public int? Mileage { get; set; }
            public string ChassisNo { get; set; }
            public string EngineNo { get; set; }
            public DateTime? RegDate { get; set; }
            public int? MfrYear { get; set; }
            public string Notes { get; set; }
            public string ManfColourCode { get; set; }
            public string ManfColourDescription { get; set; }
            public int? Doors { get; set; }
            public int? Seats { get; set; }
            public int? NumberPlates { get; set; }
            public int? PaintType { get; set; }
            public string IntColourTrim { get; set; }
    
            public OtherInspectionList OtherInspectionList { get; set; }
        }

    public class CollDelJob { [Key] public int CollDelJobID { get; set; } public DateTime InspectionDate { get; set; } public int? VehicleId { get; set; } public int? EngineerID { get; set; } public string Address { get; set; } public string PostCode { get; set; } public string ContactPhoneNumber { get; set; } public int ActualStartOdoReading { get; set; } public int? AccidentID { get; set; } public int Status { get; set; }

    }

     public class VehicleBaseDTO
        {
            public int Id { get; set; }
            public string Registration { get; set; }
            public DateTime InspectionDate { get; set; }
            public int InspectionID { get; set; }
            public string InspectionSource { get; set; }
        }
    Monday, June 4, 2018 9:00 AM
  • Hi jonathanforster,

    Based on your description and related code, I create a simple demo, but I encounter some errors, could you please share a simple demo, which could reproduce the issue via OneDrive.

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 6, 2018 5:20 AM
    Moderator