none
Problem with checking existance of an element in DB RRS feed

  • Question

  • Hi Everybody.

    I've got a problem which drives me mad and I hope that someone will have an idea, how to solve it.

    The problem occurs with code:

    using (AccountDataContext db = new AccountDataContext(ConfigurationManager.ConnectionStrings["Connection"].ConnectionString))
        {
          foreach (var item in items)
          {
            if (count != 0)
            {
              if (count == 5)
                break;
              int productID = CreateProduct(item);
    
              Products product = db.Products.Single(p => p.ProductID == productID);
              product.Description = item.ProductDesc;
              product.Inventory = item.ProductInventory;
              product.Weight = item.ProductWeight;
              product.Notes = item.ProductID.ToString();
              ProductManufacturer prodMan = new ProductManufacturer();
              prodMan.CreatedOn = DateTime.Now;
              prodMan.ManufacturerID = 222;
              prodMan.ProductID = productID;
              prodMan.DisplayOrder = 1;
              db.ProductManufacturers.InsertOnSubmit(prodMan);
              
              string[] categories = item.ProductCat.Split('|');
              int prevCatID = 0;
              for (int i = 0; i < categories.Length; i++)
              {
                Category category = new Category();
                category.AccountID = 5;
                category.CategoryName = categories[i].Trim();
                if (prevCatID != 0)
                {
                  category.ParentCategoryID = prevCatID;
                }
                else
                {
                  category.ParentCategoryID = null;
                }
                var lcat = db.Categories.Where(c => c.CategoryName == category.CategoryName && c.AccountID == category.AccountID && c.ParentCategoryID == category.ParentCategoryID);
                if (lcat.Count() == 0)
                {
                  db.Categories.InsertOnSubmit(category);
                  db.SubmitChanges();
                  prevCatID = category.CategoryID;
                }
                else
                {
                  Category exCategory = lcat.First();
                  prevCatID = exCategory.CategoryID;
                }
              }
              ProductCategory prodCat = new ProductCategory();
              prodCat.CategoryID = prevCatID;
              prodCat.ProductID = productID;
              db.ProductCategories.InsertOnSubmit(prodCat);
              db.SubmitChanges();
            }
    
            count++;
          }
        }
    

    All is around that the lCat does not achieve data added in previous iteration. I've tried to open the context inside the main foreach for item and it worked the same.

    If somebody has any idea, what to do with this, I would be very pleased for any solutions.

    Thanks,

    Michal

    Wednesday, August 10, 2011 8:56 AM

Answers

  • Hi,

    The data was in the database, that was the first thing that I've check. The problem was with comparing 2 nullable ints.

    Below I show the solution:

    var lcat = db.Categories.Where(c => c.CategoryName == category.CategoryName && c.AccountID == category.AccountID && c.ParentCategoryID.GetValueOrDefault().Equals(category.ParentCategoryID.GetValueOrDefault())).ToList();
    

    This solved the whole problem. I do not really know why comparing: c.ParentCategoryID == category.ParentCategoryID does not work. It seem to be logical but as it is shown in this example not every thing that seems to be logical is working.

    Thanks for your reply Larcolais

    Regards,

    Michal

    Friday, August 12, 2011 3:34 PM

All replies

  • Hello,

    Thanks for your post.

    From your code snippets and description, there are three places in where you have used InsertOnSubmit method. Here's my understanding. From your code, if it doesn't return anything from database side, you will add this record into database. Could you please check if the Category table has data on the database or not. I suggest you can check the underlying actions via SQL Profiler.

    Thanks,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, August 12, 2011 11:25 AM
  • Hi,

    The data was in the database, that was the first thing that I've check. The problem was with comparing 2 nullable ints.

    Below I show the solution:

    var lcat = db.Categories.Where(c => c.CategoryName == category.CategoryName && c.AccountID == category.AccountID && c.ParentCategoryID.GetValueOrDefault().Equals(category.ParentCategoryID.GetValueOrDefault())).ToList();
    

    This solved the whole problem. I do not really know why comparing: c.ParentCategoryID == category.ParentCategoryID does not work. It seem to be logical but as it is shown in this example not every thing that seems to be logical is working.

    Thanks for your reply Larcolais

    Regards,

    Michal

    Friday, August 12, 2011 3:34 PM