Massive performance issues from 6.1.0 upwards

[JStarkl]

Source/destination types

Source:

[System.CodeDom.Compiler.GeneratedCode("EF.Reverse.POCO.Generator", "2.24.0.0")]
public partial class Event : IChangeTrackedEntity, ITenantSpecificEntity
{
	public int Id { get; set; }   // id (Primary key)
	public int TenantId { get; set; }   // tenant_id
	public System.DateTime CreatedDatetime { get; set; }   // created_datetime
	public System.DateTime ChangedDatetime { get; set; }   // changed_datetime
	public int CreatedUserId { get; set; }   // created_user_id
	public int ChangedUserId { get; set; }   // changed_user_id
	public int Number { get; set; }   // number
	public System.DateTime Datetime { get; set; }   // datetime
	public int AreaManagerUserId { get; set; }   // area_manager_user_id
	public int? SalesAgentUserId { get; set; }   // sales_agent_user_id
	public int CouplesExpected { get; set; }   // couples_expected
	public int PersonsExpected { get; set; }   // persons_expected
	public int? CouplesParticipated { get; set; }   // couples_participated
	public int? CouplesWithoutHost { get; set; }   // couples_without_host
	public int? PersonsParticipated { get; set; }   // persons_participated
	public int PersonId { get; set; }   // person_id
	public bool ToBeConfirmedFlag { get; set; }   // to_be_confirmed_flag
	public int SalesAreaId { get; set; }   // sales_area_id
	public int EventTypeTenantId { get; set; }   // event_type_tenant_id
	public int LokiEventStatusId { get; set; }   // loki_event_status_id
	public int? ContactPermissionTextId { get; set; }   // contact_permission_text_id
	public string Comment { get; set; }   // comment
	public int AddressId { get; set; }   // address_id
	public System.DateTime? CrmImportedDatetime { get; set; }   // crm_imported_datetime
	public bool SendPostageFlag { get; set; }   // send_postage_flag
	public string CommentSalesAgent { get; set; }   // comment_sales_agent (length: 2000)
	public System.DateTime? AxGiftsExported { get; set; }   // ax_gifts_exported
	public bool MovedBySalesAgentFlag { get; set; }   // moved_by_sales_agent_flag
	public int? Revenue { get; private set; }   // revenue
	public int? TelemarketingAreaId { get; set; }   // telemarketing_area_id
	public System.DateTime? TravelExpenseCreatedDatetime { get; set; }   // travel_expense_created_datetime

	// Reverse navigation
	public virtual EventOk EventOk { get; set; } // event_ok.FK_event_ok_events
	public virtual System.Collections.Generic.ICollection<Coupon> Coupons { get; set; } // coupon.FK_event_coupon_event_id
	public virtual System.Collections.Generic.ICollection<EmailLog> EmailLogs { get; set; } // email_log.FK_email_log_event_id
	public virtual System.Collections.Generic.ICollection<EventBarcode> EventBarcodes { get; set; } // event_barcode.FK_event_barcode_event_id
	public virtual System.Collections.Generic.ICollection<EventCancellation> EventCancellations { get; set; } // event_cancellation.FK_event_cancellation_event_id
	public virtual System.Collections.Generic.ICollection<EventDocument> EventDocuments { get; set; } // event_document.FK_event_document_events
	public virtual System.Collections.Generic.ICollection<EventHostGift> EventHostGifts { get; set; } // event_host_gift.FK_event_host_gift_event_id
	public virtual System.Collections.Generic.ICollection<EventLock> EventLocks { get; set; } // event_lock.FK_event_lock_events
	public virtual System.Collections.Generic.ICollection<EventParticipantGift> EventParticipantGifts { get; set; } // event_participant_gift.FK_event_participant_gift_event_id
	public virtual System.Collections.Generic.ICollection<EventUserPath> EventUserPaths { get; set; } // event_user_path.FK_event_user_path_event_id
	public virtual System.Collections.Generic.ICollection<Order> Orders { get; set; } // orders.FK_orders_events
	public virtual System.Collections.Generic.ICollection<Route> Routes { get; set; } // route.FK_route_event_id
	public virtual System.Collections.Generic.ICollection<SmsLog> SmsLogs { get; set; } // sms_log.FK_sms_log_event_id
	public virtual System.Collections.Generic.ICollection<UserTravelExpens> UserTravelExpens { get; set; } // user_travel_expenses.FK_user_travel_expenses_event

	// Foreign keys
	public virtual ContactPermissionText ContactPermissionText { get; set; } // FK_events_contact_permission_text_id
	public virtual EventTypeTenant EventTypeTenant { get; set; } // FK_events_event_type_tenant
	public virtual HouseholdAddress HouseholdAddress { get; set; } // FK_events_address_id
	public virtual LokiEventStatus LokiEventStatus { get; set; } // FK_events_loki_event_status_id
	public virtual Person Person { get; set; } // FK_events_person_id
	public virtual SalesArea SalesArea { get; set; } // FK_events_sales_area_id
	public virtual Tenant Tenant { get; set; } // FK_events_tenant
	public virtual User AreaManagerUser { get; set; } // FK_events_area_manager_user_id
	public virtual User ChangedUser { get; set; } // FK_events_changed_user_id
	public virtual User CreatedUser { get; set; } // FK_events_created_user_id
	public virtual User SalesAgentUser { get; set; } // FK_events_sales_agent_user_id

	public Event()
	{
		ToBeConfirmedFlag = false;
		SendPostageFlag = false;
		MovedBySalesAgentFlag = false;
		Coupons = new System.Collections.Generic.List<Coupon>();
		EmailLogs = new System.Collections.Generic.List<EmailLog>();
		EventBarcodes = new System.Collections.Generic.List<EventBarcode>();
		EventCancellations = new System.Collections.Generic.List<EventCancellation>();
		EventDocuments = new System.Collections.Generic.List<EventDocument>();
		EventHostGifts = new System.Collections.Generic.List<EventHostGift>();
		EventLocks = new System.Collections.Generic.List<EventLock>();
		EventParticipantGifts = new System.Collections.Generic.List<EventParticipantGift>();
		EventUserPaths = new System.Collections.Generic.List<EventUserPath>();
		Orders = new System.Collections.Generic.List<Order>();
		Routes = new System.Collections.Generic.List<Route>();
		SmsLogs = new System.Collections.Generic.List<SmsLog>();
		UserTravelExpens = new System.Collections.Generic.List<UserTravelExpens>();
		InitializePartial();
	}

	partial void InitializePartial();
}

Target (all DTOs are subsets of their original generated class):

public class EventDTO
{
	public int Id { get; set; }
	public int Number { get; set; }
	public DateTime DateTime { get; set; }
	public virtual SalesAreaDTO SalesArea { get; set; }
	public virtual PersonDTO Person { get; set; }
	public int LokiEventStatusId { get; set; }
	public virtual UserDTO SalesAgent { get; set; }
	public virtual UserDTO AreaManager { get; set; }
	// Distance
	public virtual EventUserPathDTO SelectedSalesAgentPath { get; set; }
	public virtual List<EventUserPathDTO> PotentialSalesAgents { get; set; }
	public int AreaManagerUserId { get; set; }
	public int? SalesAgentUserId { get; set; }
	public int CouplesExpected { get; set; }
	public int PersonsExpected { get; set; }
	public int? CouplesParticipated { get; set; }
	public int? CouplesWithoutHost { get; set; }
	public int? PersonsParticipated { get; set; }
	public int SalesAreaId { get; set; }
	public int EventTypeTenantId { get; set; }
	public int? Revenue { get; set; }
	public bool ToBeConfirmedFlag { get; set; }
	public virtual EventTypeTenantDTO EventTypeTenant { get; set; }
	public virtual EventOkDTO EventOk { get; set; }
	public string Comment { get; set; }
	public bool SendPostageFlag { get; set; }
	public string CommentSalesAgent { get; set; }
	public virtual AddressDTO HouseholdAddress { get; set; }
	public virtual List<EventParticipantGiftDTO> EventParticipantGifts { get; set; }
	public virtual List<EventHostGiftDTO> EventHostGifts { get; set; }
	public virtual List<OrderDTO> Orders { get; set; }
	public virtual List<OrderEventDetailViewDTO> OrdersEventDetailView { get; set; }
	public virtual List<EventDocumentDTO> EventDocuments { get; set; }
	public virtual List<EventCancellationDTO> EventCancellations { get; set; }
}

Query:

return await lokiContext.Events
	.Where(e => e.Datetime >= fromDate
				&& e.Datetime < toDate
				&& (salesAreaId == null || salesAreaId == e.SalesAreaId)
				&& sizeTypeIds.Contains(e.EventTypeTenant.EventType.EventSizeTypeId)
				&& stateIds.Contains(e.LokiEventStatusId))
	.OrderBy(e => e.Datetime)
	.ProjectTo<EventDTO>(mapperService.GetMapperConfig(),
		e => e.Person.Phones,
		e => e.HouseholdAddress,
		e => e.SalesAgent,
		e => e.EventOk,
		e => e.EventTypeTenant,
		e => e.EventTypeTenant.EventType)
	.ToListAsync();

Mapping configuration

cfg.CreateMap<Event, EventDTO>()
	.ForMember(dto => dto.SalesAgent, expr => expr.MapFrom(e => e.SalesAgentUser))
	.ForMember(
		dto => dto.AreaManager,
		expr => expr.MapFrom(e => e.AreaManagerUser))
	.ForMember(
		dto => dto.SelectedSalesAgentPath,
		expr => expr.MapFrom(e => e.EventUserPaths.FirstOrDefault(path => e.SalesAgentUserId.HasValue && path.UserId == e.SalesAgentUserId.Value)))
	.ForMember(
		dto => dto.PotentialSalesAgents,
		expr => expr.MapFrom(e => e.EventUserPaths))
	.ExplicitExpandAllDestinationVirtual();

Version: 6.1.0 and upwards

Expected behavior

Till 6.0.2 it takes about two seconds between sending the request from the frontend and get the data for display

Actual behavior

Since 6.1.0 the query is very slow, sometimes results come by, sometimes running in a timeout while querying

The db request itself is as fast as before, but something seems to happen within the projection of the inclusion of the Child-Objects

[lbargaoanu]

So you're saying the queries in the db are exactly the same between the two versions and yet the performance is much worse? I find that hard to believe. I would double check. And if that's indeed the case, profile it and find out where that time is spent.

[lock]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.