Entity Framework Core ForEachAsync

Question

I am currently trying to optimize my application. AllProducts contains about 24k rows (entries).

List AllProducts;

Basically, I would like to replace the following (ForEachAsync() added):

AllProducts = db.tblMATProduct.Include(s => s.tblGrade).Include(s => s.tblMATProductState).Include(s => s.tblFormat).Include(s => s.tblProduct).ThenInclude(s => s.tblBatch).OrderBy(s => s.MATProductID).ToList();

with something like:

AllProducts = db.tblMATProduct.Include(s => s.tblGrade).Include(s => s.tblMATProductState).Include(s => s.tblFormat).Include(s => s.tblProduct).ThenInclude(s => s.tblBatch).ForEachAsync(// Do operations here).OrderBy(s => s.MATProductID).ToList();

All the Includes are necessary as I need to get info from all those tables starting from the main table tblMATProduct.

This would help me to remove the following operations which are very costly because of the unnecessary foreach (Building list with LINQ and then loading it again to modify its objects).

Basically, all the following code is used to modify objects from my List Allproducts and I'd like to move it in the ForEachAsync().

 private void SetProductsListInfo()
    {
        foreach (var product in AllProducts)
        {
            SetNumLotCount(product);
            SetProductTMD(product);
            SetLimitedQuantityTags(product);
            SetNFPATags(product);
        }
    }


    private void SetNumLotCount(tblMATProduct product)
    {
        {
            if (product.tblProduct != null && product.tblProduct.tblBatch != null)
            {
                product.ProductNumLotCount = "No. Lot (" + product.tblProduct.tblBatch.Count + ")";
            }
            else if (product.tblProduct == null)
            {

            }
            else
            {
                product.ProductNumLotCount = "No. Lot (" + 0 + ")";
            }

        }
    }


    private void SetProductTMD(tblMATProduct product)
    {

        product.ProductTMD = new tblProductTMD();
        product.DangerList = new List();
        product.ProductTransportClassList = new List();

        if (product.tblProduct != null)
        {
            product.ProductTMD = product.tblProduct.tblProductTMD.FirstOrDefault();
            if (product.ProductTMD != null) product.DangerList = product.ProductTMD.tblDanger.ToList();
            if (product.ProductTMD != null) product.ProductTransportClassList = product.ProductTMD.tblProductTransportClass.ToList();
        }

        product.DangerTypeList = new List();

        if (product.DangerList != null)
        {
            foreach (var danger in product.DangerList)
            {
                if (danger.tblDangerType != null && danger.tblDangerType.RulingSystemID == 2) product.DangerTypeList.Add(danger.tblDangerType);
            }

            foreach (var dangerType in product.DangerTypeList)
            {
                if (dangerType != null) dangerType.DangerIcon = dangerType.DangerTypeIcon;
            }
        }

        product.TransportClassList = new List();

        if (product.ProductTransportClassList != null)
        {
            foreach (var productTransportClass in product.ProductTransportClassList)
            {
                if (productTransportClass.tblTransportClass != null) product.TransportClassList.Add(productTransportClass.tblTransportClass);
            }
        }
    }


    private void SetLimitedQuantityTags(tblMATProduct product)
    {
        if (product.ProductTMD != null && product.ProductTMD.ProductTMDLimitedQty != null && product.ProductTMD.ProductTMDLimitedQty > 0) product.ProductLimitedQuantity = product.ProductTMD.ProductTMDLimitedQty.ToString();
    }

    private void SetNFPATags(tblMATProduct product)
    {

        if (product.tblProduct != null && product.tblProduct.tblProductNFPA != null)
        {
            foreach (var productNFPA in product.tblProduct.tblProductNFPA)
            {
                if (productNFPA.NFPATypeID == 1) product.ProductInflammabilityLevel = productNFPA.tblNFPAValue.NFPAValueCode;

                if (productNFPA.NFPATypeID == 2) product.ProductToxicityLevel = productNFPA.tblNFPAValue.NFPAValueCode;

                if (productNFPA.NFPATypeID == 3) product.ProductReactivityLevel = productNFPA.tblNFPAValue.NFPAValueCode;
            }

            if (product.ProductInflammabilityLevel == null) product.ProductInflammabilityLevel = "0";
            if (product.ProductToxicityLevel == null) product.ProductToxicityLevel = "0";
            if (product.ProductReactivityLevel == null) product.ProductReactivityLevel = "0";
        }
    }

Note that I am also open to other suggestions than using ForEachAsync().

Thanks in advance!

user_3034273 · Answer

Rather than trying to fit it into a comment I have a few questions and things you should consider - and perhaps you may even find your answer here:

I'd like to move it in the ForEachAsync().

Q: Is there a reason you specifically write "Async"? If you don't need it to be an asynchronous operation, then let's leave it out.

This is how you currently read data from your database:

AllProducts = db.tblMATProduct
    .Include(s => s.tblGrade)
    .Include(s => s.tblMATProductState)
    .Include(s => s.tblFormat)
    .Include(s => s.tblProduct)
    .ThenInclude(s => s.tblBatch)
    .OrderBy(s => s.MATProductID)
    .ToList();

Q: In the above "query" you are not filtering or paging anything, which means the ToList() call causes your query to read ALL data from that table AND any data connected in the included tables. You say you have 24.000 rows which means you are potentially loading A LOT of data into memory at the same time. Perhaps your data is not very complex meaning this is not an issue, but is this what you intend to do?

Basically, all the following code is used to modify objects from my List Allproducts

Q: Are all these modifications used to update the data in the database or just to show it in the UI? The line with product.ProductNumLotCount = "No. Lot (" + 0 + ")"; indicates to me that it's for UI purposes.

If my guess is correct, I would suggest you create some classes/data models separately for that instead of using the database models to show your data. That is, so you have separate data access models and business object models. This makes it easier for you to design your models only for the purpose they are needed for. The modification you are talking about is then better known as "mapping" from the data access model to the business object model.

This would help me to remove the following operations which are very costly because of the unnecessary foreach (Building list with LINQ and then loading it again to modify its objects).

Q: Why do you think the foreach loops are unnecessary? All the foreach loops - except the very first one in your snippet - are looping over lists in the properties of a product and not on the AllProducts list itself. One loop can not replace all of these.

Regarding "Building list with LINQ and then loading it again to modify its objects" - I'm guessing what you mean here is that you are first creating the list and then iterating through it to modify your objects. Instead what you want is to modify the object before the list is created, right?

For this you can use the LINQ Select extension method. However, because IQueryable.Select(...) expects an expression as argument you need to make your code work as an expression, which might be a little difficult for you in this case. In any case, unless you have proof of this causing performance issues I wouldn't advice you to spend time on trying to optimize that until later. Instead I would here point to my comment in point 3 and suggest you implement you "modification" as a mapping between data objects instead - separating the physical models from the business logic can help you avoid serious headaches down the line.

Entity Framework Core ForEachAsync

1 Answers