提高将展平的LINQ2SQL结果转换为3级分层对象的性能

Question

我从MSSQL2008DB中通过存储过程调用检索到了一个大型数据集。dataset是平面化分层数据(Events>InventorySpaces>ScheduledInventorySpaces)或(Parent>Child>GrandChild)的300,000+行。

出于处理原因，我需要将其转换为分层对象模型，并对其执行分析。

我在创建hierarchical对象时遇到了处理延迟的问题，我想了解一下社区对如何加快速度的看法。PLINQ？Lambda Joins？等?

类的定义如下：

    public class Evt
{
    public int NetCodeID { get; set; }
    public int EventID { get; set; }
    public int SiteID { get; set; }
    public bool HomeGame { get; set; }
    public string Days { get; set; }
    public int DayBit { get; set; }
    public int EventIDRC { get; set; }
    public List<InvSpace> InvSpaces { get; set; }

    public Evt()
    {
    }

    public Evt(int netcodeid, int eventid, int siteid, bool homegame, string days, int daybit, int eventidrc)
    {
        this.NetCodeID = netcodeid;
        this.EventID = eventid;
        this.SiteID = siteid;
        this.HomeGame = homegame;
        this.Days = days;
        this.DayBit = daybit;
        this.EventIDRC = eventidrc;
        this.InvSpaces = new List<InvSpace>();
    }

    public Evt(int netcodeid, int eventid, int siteid, bool homegame, string days, int daybit, int eventidrc, List<InvSpace> invspaces)
    {
        this.NetCodeID = netcodeid;
        this.EventID = eventid;
        this.SiteID = siteid;
        this.HomeGame = homegame;
        this.Days = days;
        this.DayBit = daybit;
        this.EventIDRC = eventidrc;
        this.InvSpaces = invspaces;
    }

}

class EvtResultEqualityComparer : IEqualityComparer<USP_GetAvailableScheduledInventorySpacesResult>
{
    public bool Equals(USP_GetAvailableScheduledInventorySpacesResult x, USP_GetAvailableScheduledInventorySpacesResult y)
    {
        return Equals(x.EventID, y.EventID);
    }

    public int GetHashCode(USP_GetAvailableScheduledInventorySpacesResult x)
    {
        return x.EventID.GetHashCode();
    }
}

    public class InvSpace
{
    public int InventorySpaceID { get; set; }
    public string InventoryName { get; set; }
    public int OrderNumber { get; set; }
    public int InventoryClearanceTypeBit { get; set; }
    public int InventoryClearanceTypeID { get; set; }
    public int InventoryProductTypeID { get; set; }
    public int InventoryTypeID { get; set; }
    public int AvailableLength { get; set; }
    public List<string> InventoryGroupAdvertiserCategories { get; set; }
    public List<string> AdvertiserCategories { get; set; }
    public int? Positions { get; set; }
    public List<ScheduledInvSpace> ScheduledInvSpaces { get; set; }

    public InvSpace()
    {
    }

    public InvSpace(int inventoryspaceid, string inventoryname, int ordernumber, int inventoryclearancetypebit, int inventoryclearancetypeid, int inventoryproducttypeid, int inventorytypeid)
    {
        this.InventorySpaceID = inventoryspaceid;
        this.InventoryName = inventoryname;
        this.OrderNumber = ordernumber;
        this.InventoryClearanceTypeBit = inventoryclearancetypebit;
        this.InventoryClearanceTypeID = inventoryclearancetypeid;
        this.InventoryProductTypeID = inventoryproducttypeid;
        this.InventoryTypeID = inventorytypeid;
        this.InventoryGroupAdvertiserCategories = new List<string>();
        this.AdvertiserCategories = new List<string>();
        this.ScheduledInvSpaces = new List<ScheduledInvSpace>();
        this.AvailableLength = ScheduledInvSpaces.Count*5;
    }

    public InvSpace(int inventoryspaceid, string inventoryname, int ordernumber, int inventoryclearancetypebit, int inventoryclearancetypeid, int inventoryproducttypeid, int inventorytypeid, string inventorygroupadvertisercategories, string advertisercategories, int positions)
    {
        this.InventorySpaceID = inventoryspaceid;
        this.InventoryName = inventoryname;
        this.OrderNumber = ordernumber;
        this.InventoryClearanceTypeBit = inventoryclearancetypebit;
        this.InventoryClearanceTypeID = inventoryclearancetypeid;
        this.InventoryProductTypeID = inventoryproducttypeid;
        this.InventoryTypeID = inventorytypeid;
        this.InventoryGroupAdvertiserCategories = inventorygroupadvertisercategories != null
                                                    ? inventorygroupadvertisercategories.Split('|').ToList()
                                                    : new List<string>();
        this.AdvertiserCategories = advertisercategories != null
                                        ? advertisercategories.Split('|').ToList()
                                        : new List<string>();
        this.Positions = positions;
        this.ScheduledInvSpaces = new List<ScheduledInvSpace>();
        this.AvailableLength = ScheduledInvSpaces.Count * 5;
    }

    public InvSpace(int inventoryspaceid, string inventoryname, int ordernumber, int inventoryclearancetypebit, int inventoryclearancetypeid, int inventoryproducttypeid, int inventorytypeid, string inventorygroupadvertisercategories, string advertisercategories, int positions, List<ScheduledInvSpace> scheduledinvspaces)
    {
        this.InventorySpaceID = inventoryspaceid;
        this.InventoryName = inventoryname;
        this.OrderNumber = ordernumber;
        this.InventoryClearanceTypeBit = inventoryclearancetypebit;
        this.InventoryClearanceTypeID = inventoryclearancetypeid;
        this.InventoryProductTypeID = inventoryproducttypeid;
        this.InventoryTypeID = inventorytypeid;
        this.InventoryGroupAdvertiserCategories = inventorygroupadvertisercategories != null
                                                    ? inventorygroupadvertisercategories.Split('|').ToList()
                                                    : new List<string>();
        this.AdvertiserCategories = advertisercategories != null
                                        ? advertisercategories.Split('|').ToList()
                                        : new List<string>();
        this.Positions = positions;
        this.ScheduledInvSpaces = scheduledinvspaces;
        this.AvailableLength = ScheduledInvSpaces.Count * 5;
    }
}

class InvSpaceResultEqualityComparer : IEqualityComparer<USP_GetAvailableScheduledInventorySpacesResult>
{
    public bool Equals(USP_GetAvailableScheduledInventorySpacesResult x, USP_GetAvailableScheduledInventorySpacesResult y)
    {
        return Equals(x.InventorySpaceID, y.InventorySpaceID) && Equals(x.EventID, y.EventID);
    }

    public int GetHashCode(USP_GetAvailableScheduledInventorySpacesResult x)
    {
        return x.InventorySpaceID.GetHashCode();
    }
}

    public class ScheduledInvSpace
{
    public int ScheduledInventorySpaceID { get; set; }
    public int SlotID { get; set; }
    public int Length { get; set; }
    public int? ContractOrderID { get; set; }
    public int? RotationID { get; set; }
    public int InventorySequence { get; set; }
    public string InventoryGroupAdvertiserCategoryRule { get; set; }
    public string AdvertiserCategoryRule { get; set; }
    public int? PositionRule { get; set; }
    public string BumpRule { get; set; }
    public string ClearanceRule { get; set; }
    public bool? Bonus { get; set; }

    public ScheduledInvSpace()
    {
    }

    public ScheduledInvSpace(int scheduledinventoryspaceid, int slotid, int length, int inventorysequence, string inventorygroupadvertisercategoryrule, string advertisercategoryrule, int? positionrule, string bumprule, string clearancerule, bool? bonus)
    {
        this.ScheduledInventorySpaceID = scheduledinventoryspaceid;
        this.SlotID = slotid;
        this.Length = length;
        this.ContractOrderID = null;
        this.RotationID = null;
        this.InventorySequence = inventorysequence;
        this.InventoryGroupAdvertiserCategoryRule = inventorygroupadvertisercategoryrule;
        this.AdvertiserCategoryRule = advertisercategoryrule;
        this.PositionRule = positionrule;
        this.BumpRule = bumprule;
        this.ClearanceRule = clearancerule;
        this.Bonus = bonus;
    }

    public ScheduledInvSpace(int scheduledinventoryspaceid, int slotid, int length, int? contractorderid, int? rotationid, int inventorysequence, string inventorygroupadvertisercategoryrule, string advertisercategoryrule, int? positionrule, string bumprule, string clearancerule, bool? bonus)
    {
        this.ScheduledInventorySpaceID = scheduledinventoryspaceid;
        this.SlotID = slotid;
        this.Length = length;
        this.ContractOrderID = contractorderid;
        this.RotationID = rotationid;
        this.InventorySequence = inventorysequence;
        this.InventoryGroupAdvertiserCategoryRule = inventorygroupadvertisercategoryrule;
        this.AdvertiserCategoryRule = advertisercategoryrule;
        this.PositionRule = positionrule;
        this.BumpRule = bumprule;
        this.ClearanceRule = clearancerule;
        this.Bonus = bonus;
    }
}

class SheduledInvSpaceResultEqualityComparer : IEqualityComparer<USP_GetAvailableScheduledInventorySpacesResult>
{
    public bool Equals(USP_GetAvailableScheduledInventorySpacesResult x, USP_GetAvailableScheduledInventorySpacesResult y)
    {
        return Equals(x.ScheduledInventorySpaceID, y.ScheduledInventorySpaceID);
    }

    public int GetHashCode(USP_GetAvailableScheduledInventorySpacesResult x)
    {
        return x.ScheduledInventorySpaceID.GetHashCode();
    }
}

我使用以下代码填充此层次结构：

List<Evt>games = results
                            .Distinct(new EvtResultEqualityComparer())
                            .Select(e => 
                                new Evt(e.NetCodeID.Value, e.EventID.Value, e.SiteID.Value, e.HomeGame.Value, e.Days, e.DayBit.Value, e.EventIDRC.Value, results
                                    .Distinct(new InvSpaceResultEqualityComparer())
                                    .Where(i => i.EventID.Value == e.EventID.Value && i.InventoryProductTypeID.Value == (ProductTypes[i.EventIDRC.Value % ProductTypes.Count]))
                                    .Select(i => 
                                        new InvSpace(i.InventorySpaceID.Value, i.InventoryName, i.OrderNumber.Value, i.InventoryClearanceTypeBit.Value, i.InventoryClearanceTypeID.Value, i.InventoryProductTypeID.Value, i.InventoryTypeID.Value, i.InventoryGroupAdvertiserCategoryRule, i.AdvertiserCategoryRule, i.PositionRule.GetValueOrDefault(0), results
                                            .Distinct(new SheduledInvSpaceResultEqualityComparer())
                                            .Where(s => s.EventID.Value == e.EventID.Value && s.InventorySpaceID.Value == i.InventorySpaceID.Value)
                                            .Select(s => 
                                                new ScheduledInvSpace(s.ScheduledInventorySpaceID.Value, s.SlotID.Value, s.Length.Value, s.ContractOrderID, s.RotationID, s.InventorySequence.Value, s.InventoryGroupAdvertiserCategoryRule, s.AdvertiserCategoryRule, s.PositionRule.GetValueOrDefault(0), s.BumpRule, s.ClearanceRule, s.Bonus)).ToList()
                                                )).Where(i => i.AvailableLength >= length).ToList())).ToList();

Answer 1

解决方案是使用LINQ group by。如果您避免使用Lambda表达式group by，而只使用select from group by，则更容易阅读。