I want to create a product feed using the Merge() function from OrmLite but getting an out of memory exception. Our SQL Server database is quite big and we have multiple nested tables.
Could that be the source of the exception? What would be a good solution for loading a huge amount of data at once? Or what is the best practice in this case?
I've also tried to loop the products and then load the needed data for each product, but that took forever.
var webshops = DbConnection.Select<Webshop>();
var languages = DbConnection.Select<Language>();
var products = DbConnection.Select<Product>();
var productCategories = DbConnection.Select<ProductCategory>();
var productManufacturers = DbConnection.Select<ProductManufacturer>();
var productSpecificationAttributes = DbConnection.Select<ProductSpecificationAttribute>();
var productPictures = DbConnection.Select<ProductPicture>();
var productTranslations = DbConnection.Select<ProductTranslation>();
var productTierPrices = DbConnection.Select<ProductTierPrice>();
var productPurchasePrices = DbConnection.Select<ProductPurchasePrice>();
var stockItems = DbConnection.Select<StockItem>();
var groupedProducts = DbConnection.Select<GroupedProduct>();
var categories = DbConnection.Select<Category>();
var categoryTranslations = DbConnection.Select<CategoryTranslation>();
var manufacturers = DbConnection.Select<Manufacturer>();
var manufacturerTranslations = DbConnection.Select<ManufacturerTranslation>();
var manufacturerTierPrices = DbConnection.Select<ManufacturerTierPrice>();
var specificationAttributes = DbConnection.Select<SpecificationAttribute>();
var specificationAttributeTranslations = DbConnection.Select<SpecificationAttributeTranslation>();
var specificationAttributeOptions = DbConnection.Select<SpecificationAttributeOption>();
var specificationAttributeOptionTranslations = DbConnection.Select<SpecificationAttributeOptionTranslation>();
var stockUnits = DbConnection.Select<StockUnit>(x => x.StockUnitStatus == StockUnitStatus.In);
#region merge
webshops.Merge(languages);
manufacturers.Merge(manufacturerTranslations);
manufacturers.Merge(manufacturerTierPrices);
categories.Merge(categoryTranslations);
specificationAttributes.Merge(specificationAttributeTranslations);
specificationAttributeOptions.Merge(specificationAttributes);
specificationAttributeOptions.Merge(specificationAttributeOptionTranslations);
productCategories.Merge(categories);
productManufacturers.Merge(manufacturers);
productSpecificationAttributes.Merge(specificationAttributeOptions);
products.Merge(productCategories);
products.Merge(productManufacturers);
products.Merge(productSpecificationAttributes);
products.Merge(productTranslations);
products.Merge(productPictures);
products.Merge(productTierPrices);
products.Merge(productPurchasePrices);
products.Merge(stockItems);
products.Merge(groupedProducts);
#endregion
varfor everything? It makes your code unreadable for us and even you should get confused about the types. I guessDbConnection.Selectreturns aSystem.Data.DataTable, correct?productsis or what you're doing with the data and how you're looping over it) but you could look at chunking the data/ loop, i.e. load 100 products at a time instead of 1/ all