none
How to implement sales order data loading properly using EF? RRS feed

  • Question

  • Hello,

    I have a case that I cannot solve in any elegant way.

    1. There is a DB containing million rows of sales orders.

    2. There is a view for browsing sales orders with a few filters like search by order number and date range.

    3. When the view is first accessed its empty. The user will search for an order.

    4. I make the search DB.SalesOrders.Where(x => x.OrderNumber == number) and it returns one order.

    5. User adds a new order DB.SalesOrders.Add(newOrder);

    6. Now, if user makes a seach using the new order number it will not be found because I'm not searching from DB.Local.

    The problem is that if I search from local, then the first search would not work. On the other hand I cannot make Local aware of all million rows in the database. How this kind of scenario should be implemented? Can I somehow combine database and Local results into one?

    Wednesday, July 30, 2014 5:50 AM

Answers

  • It doesn't work because I have IDs assigned by DB and adding multiple orders causes multiple orders in Local having ID = 0. But the idea might still work. I could make a search to DB first and add Local search after that with the same filters. It shouldn't affect performance too much because the Local search happens in memory. I'm not sure however if I get double results for some rows and I'm not sure if Distinct() will fix them because the types are some proxies or something...

    Edit: Ok Distinct() works so problem solved. Its not pretty fix but enough for me at least for this case.

    • Edited by Asgard Wednesday, July 30, 2014 12:26 PM
    • Marked as answer by Fred BaoModerator Thursday, August 7, 2014 6:39 AM
    Wednesday, July 30, 2014 10:57 AM

All replies

  • Hello,

    >>How this kind of scenario should be implemented?

    I am wondering after user adds a new order, do you save it to database? If you already save it to database, I think you do not worry about user would not find the new added one. If you do not save it, I feel strange why you do not save it.

    >>Can I somehow combine database and Local results into one?

    My idea is that you can firstly query the databasae, if there is not any record, then to query the local by using if else block.

    If I misunderstand, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, July 30, 2014 8:31 AM
    Moderator
  • I do not add stuff directly to database because of two reasons:

    - I want to provide save/undo functionality

    - I have other features that "test" the new entities before saving. For example in case of sales orders, user can create a set of them and see how they would affect inventories, production, deliveries and so on. If user is not happy with the results (or is just experimenting), he can just undo the changes and they are never saved to the database.

    Wednesday, July 30, 2014 9:05 AM
  • Hello,

    Okey, I do not consider so much.

    >>Can I somehow combine database and Local results into one?

    Using the if-else block as I mentioned in my original post. Or if you do not want to much code, using the Union() method when doing the query as:

      int OrderID = 1;
    
    
                    Order order = db.Orders.Find(OrderID);
    
    
                    Order OrderNew = new Order() { OrderID = 2, OrderName = "2" };
    
    
                    db.Orders.Add(OrderNew);
    
    
                    db.Database.Log = Console.Write;
    
    
                    var result = (from orderDB in db.Orders
    
                                  where orderDB.OrderID == 2
    
                                  select orderDB).ToList().Union(from orderLocal in db.Orders.Local
    
                                                                 where orderLocal.OrderID == 2
    
                                                                 select orderLocal).ToList();
    

    This would find the newly added record whose id is 2 and is not saved into db. Also, it would not load all remained records to memory. You can have a try.

    If this does not work for you, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, July 30, 2014 9:56 AM
    Moderator
  • It doesn't work because I have IDs assigned by DB and adding multiple orders causes multiple orders in Local having ID = 0. But the idea might still work. I could make a search to DB first and add Local search after that with the same filters. It shouldn't affect performance too much because the Local search happens in memory. I'm not sure however if I get double results for some rows and I'm not sure if Distinct() will fix them because the types are some proxies or something...

    Edit: Ok Distinct() works so problem solved. Its not pretty fix but enough for me at least for this case.

    • Edited by Asgard Wednesday, July 30, 2014 12:26 PM
    • Marked as answer by Fred BaoModerator Thursday, August 7, 2014 6:39 AM
    Wednesday, July 30, 2014 10:57 AM