使用查找表和连接表选择许多列/字段.内存不足异常

我有一个高度规范化的房地产数据库，用于存储清单数据。由于字段的数量巨大，主数据分布在3个表(listing、listingdatacmons和ListingDataOthers)之间，然后还有几个连接表用于功能、类型等。

应用程序的用户使用GUI来定义标准/字段，系统将生成(使用动态linq) Where, Select和OrderBy语句。问题是，当Select语句使用许多连接和/或查找表时，我得到一个Out Of Memory Exception。

下面是一个将抛出错误的强类型(为了更容易阅读)查询示例。即使它只通过MLS号返回一条记录，也会抛出OOM异常。

var ListingResult = context.Listings
    .Where(a => a.MLSNumber == "123456" || a.MLSNumber == "654321")
    .Select(a => new
    {
        //--- select some data from the Listings table
        a.MLSNumber,
        a.DateLastUpdated,
        a.DateLastImageUpdated,
        a.Address,
        a.ZipCode,
        a.DaysOnMarket,
        a.DisplayOnInternet,
        a.DisplayReviews,
        a.AuctionYN,
        a.ListPrice,
        a.LeasePrice,
        a.SystemID,
        a.DateLastPriceChange,
        a.DateLastStatusChange,
        a.DisplayAddressOnlineYN,
        a.ListingID,
        Status = a.Status.Name,
        PropertyType = a.PropertyType.Name,
        PropertyStyle = a.PropertyStyle.Name,
        Country = a.Country.Name,
        State = a.State.Name,
        County = a.County.Name,
        City = a.City.Name,
        SaleType = a.SaleType.Name,
        //--- select some data from the ListingDataCommons table
        BathsFull = a.ListingDataCommon.BathsFull,
        BathsHalf = a.ListingDataCommon.BathsHalf,
        Beds = a.ListingDataCommon.Beds,
        FireplaceYN = a.ListingDataCommon.FireplaceYN,
        GarageSpaces = a.ListingDataCommon.GarageSpaces,
        HOAYN = a.ListingDataCommon.HOAYN,
        LotAcres = a.ListingDataCommon.LotAcres,
        NewConstructionYN = a.ListingDataCommon.NewConstructionYN,
        PetsAllowedYN = a.ListingDataCommon.PetsAllowedYN,
        PetsMaxWeight = a.ListingDataCommon.PetsMaxWeight,
        PetsMaxNumber = a.ListingDataCommon.PetsMaxNumber,
        RemarksPublic = a.ListingDataCommon.RemarksPublic,
        SqftHeated = a.ListingDataCommon.SqftHeated,
        SubdivisionName = a.ListingDataCommon.SubdivisionName,
        Taxes = a.ListingDataCommon.Taxes,
        TaxYear = a.ListingDataCommon.TaxYear,
        YearBuilt = a.ListingDataCommon.YearBuilt,
        AirConditioning = a.ListingDataCommon.AirConditioning.Name,
        ConstructionStatus = a.ListingDataCommon.ConstructionStatus.Name,
        HousingForOlder = a.ListingDataCommon.HousingForOlder.Name,
        //--- select some data from the ListingDataOthers table
        CDDFee = a.ListingDataOther.CDDFee,
        CDDFeeYN = a.ListingDataOther.CDDFeeYN,
        CondoFee = a.ListingDataOther.CondoFee,
        HOAFee = a.ListingDataOther.HOAFee,
        HomesteadYN = a.ListingDataOther.HomesteadYN,
        LotDimensions = a.ListingDataOther.LotDimensions,
        LotSqft = a.ListingDataOther.LotSqft,
        NumberBays = a.ListingDataOther.NumberBays,
        NumberBuildings = a.ListingDataOther.NumberBuildings,
        NumberFloors = a.ListingDataOther.NumberFloors,
        NumberHotelRooms = a.ListingDataOther.NumberHotelRooms,
        NumberOffices = a.ListingDataOther.NumberOffices,
        NumberRestrooms = a.ListingDataOther.NumberRestrooms,
        ProjectedCompletionDate = a.ListingDataOther.ProjectedCompletionDate,
        SchoolElementary = a.ListingDataOther.SchoolElementary,
        SchoolMiddle = a.ListingDataOther.SchoolMiddle,
        SchoolHigh = a.ListingDataOther.SchoolHigh,
        SizePorch = a.ListingDataOther.SizePorch,
        SizeBedMaster = a.ListingDataOther.SizeBedMaster,
        SizeBed2 = a.ListingDataOther.SizeBed2,
        SizeBed3 = a.ListingDataOther.SizeBed3,
        SizeBed4 = a.ListingDataOther.SizeBed4,
        SizeBed5 = a.ListingDataOther.SizeBed5,
        SizeBonusRoom = a.ListingDataOther.SizeBonusRoom,
        SizeDinette = a.ListingDataOther.SizeDinette,
        SizeDiningRoom = a.ListingDataOther.SizeDiningRoom,
        SizeFamilyRoom = a.ListingDataOther.SizeFamilyRoom,
        SizeGreatRoom = a.ListingDataOther.SizeGreatRoom,
        SizeKitchen = a.ListingDataOther.SizeKitchen,
        SizeLivingRoom = a.ListingDataOther.SizeLivingRoom,
        SizeStudio = a.ListingDataOther.SizeStudio,
        SizeStudyDen = a.ListingDataOther.SizeStudyDen,
        SqftTotalBldg = a.ListingDataOther.SqftTotalBldg,
        TotalUnits = a.ListingDataOther.TotalUnits,
        VirtualTourLink = a.ListingDataOther.VirtualTourLink,
        WaterAccessYN = a.ListingDataOther.WaterAccessYN,
        WaterExtrasYN = a.ListingDataOther.WaterExtrasYN,
        WaterFrontageYN = a.ListingDataOther.WaterFrontageYN,
        WaterViewYN = a.ListingDataOther.WaterViewYN,
        ZipCodePlusFour = a.ListingDataOther.ZipCodePlusFour,
        Zoning = a.ListingDataOther.Zoning,
        AWCRemarks = a.ListingDataOther.AWCRemarks,
        ArchitecturalStyle = a.ListingDataOther.ArchitecturalStyle.Name,
        CondoFeeSchedule = a.ListingDataOther.TimeFrame.Name,
        FrontExposure = a.ListingDataOther.FrontExposure.Name,
        Foundation = a.ListingDataOther.Foundation.Name,
        Furnishing = a.ListingDataOther.Furnishing.Name,
        HOASchedule = a.ListingDataOther.TimeFrame.Name,
        MobileHomeWidth = a.ListingDataOther.MobileHomeWidth.Name,
        //--- select some data from the junction tables (which in turn use lookup tables)
        AdditionalRooms = a.ListingAdditionalRooms.Select(b => b.AdditionalRoom.Name),
        AppliancesIncluded = a.ListingAppliances.Select(b => b.Appliance.Name),
        CommunityFeatures = a.ListingCommunityFeatures.Select(b => b.CommunityFeature.Name),
        ExteriorConstructions = a.ListingExteriorConstructions.Select(b => b.ExteriorConstruction.Name),
        ExteriorFeatures = a.ListingExteriorFeatures.Select(b => b.ExteriorFeature.Name),
        Financings = a.ListingFinancings.Select(b => b.Financing.Name),
        FireplaceDescriptions = a.ListingFireplaceDescriptions.Select(b => b.FireplaceDescription.Name),
        FloorCoverings = a.ListingFloorCoverings.Select(b => b.FloorCovering.Name),
        FuelTypes = a.ListingFuelTypes.Select(b => b.FuelType.Name),
        GarageFeatures = a.ListingGarageFeatures.Select(b => b.GarageFeature.Name),
        GarageTypes = a.ListingGarageTypes.Select(b => b.GarageType.Name),
        HeatTypes = a.ListingHeatTypes.Select(b => b.HeatType.Name),
        InteriorFeatures = a.ListingInteriorFeatures.Select(b => b.InteriorFeature.Name),
        KitchenFeatures = a.ListingKitchenFeatures.Select(b => b.KitchenFeature.Name),
        LeaseIncludes = a.ListingLeaseIncludes.Select(b => b.LeaseInclude.Name),
        MasterBathFeatures = a.ListingMasterBathFeatures.Select(b => b.MasterBathFeature.Name),
        ParkingOptions = a.ListingParkingOptions.Select(b => b.ParkingOption.Name),
        PoolFeatures = a.ListingPoolFeatures.Select(b => b.PoolFeature.Name),
        PoolTypes = a.ListingPoolTypes.Select(b => b.PoolType.Name),
        PropertyUses = a.ListingPropertyUses.Select(b => b.PropertyUse.Name),
        RoofTypes = a.ListingRoofTypes.Select(b => b.RoofType.Name),
        WaterAccessTypes = a.ListingWaterAccessTypes.Select(b => b.WaterType.Name),
        WaterExtraTypes = a.ListingWaterExtraTypes.Select(b => b.WaterExtraType.Name),
        WaterFrontageTypes = a.ListingWaterFrontageTypes.Select(b => b.WaterType.Name),
        WaterViewTypes = a.ListingWaterViewTypes.Select(b => b.WaterType.Name),
    })
    .OrderBy(a => a.MLSNumber)
    .ToList();

是否有更好的方法来组织它?即使在查询中调用.ToString()来查看生成的SQL也会抛出OOM异常。

更新:

针对@Gert Arnold，你能进一步解释一下为什么数据库没有规范化吗?让我们以Status的字段为例，这里有Status = a.Status.Name。数据库中有一个名为Statuses的表，其中有2列StatusID和Name，数据将是1|Active, 2|Pending, 3|Sold。Listings表上的字段是StatusID，它包含对Statuses表上StatusID字段的引用。为了获得实际的名称而不是状态的ID，我必须执行a.Status.Name。这与PropertyType, PropertyStyle, Country, State, County, City, SaleType的结构完全相同。

然后，对于ListingDataCommons和ListingDataOthers表，它们以与Listings表1:1的关系创建。创建它们是因为一个清单有数百个字段，而不是将它们转储到一个巨大的表中，而是根据查询每个字段的频率对它们进行拆分。在这些表中，有一些列引用查找表的ID，而不是重复的字符串值，正如上面的状态所解释的那样。

然后是连接表，如ListingAdditionalRooms，具有1:Many关系，其中一个列表可以有许多额外的房间。ListingAdditionalRooms表(和所有其他连接表)有2列(ListingID | AdditionalRoomID)引用Listings表和AdditionalRooms表中各自的记录。

如果这是你见过的最糟糕的数据库设计之一，你建议如何改进它?我应该有一个Listings表，有近300列，通过记录重复存储字符串值吗?这似乎不是一个好的解决方案。请简要描述一下你将如何去做(Listings表有数百万条记录)。不是要图表，只是简单的解释。

对于建议，将其分成更小的块并在2个请求中请求数据似乎确实解决了这个问题(在一个请求中请求连接表数据，在另一个请求中请求所有其他数据)。

关于从未在任何UI中显示的数据量，这是不正确的。虽然这个查询只是测试限制，但这对于向用户显示清单的完整细节是绝对必要的。

我期待你对数据库结构的建议。