none
Best way to Search Table RRS feed

  • Question

  • I've got a 75,000 record table (DeptItems) of items with a Description and a Department.  These are in an SDF database which I've built the EF model from.  I need to match an item Description in a sale to the item Description in a 75k items table and return the Department for that item. 

    My question is what would be the best and fastest way to do this as I'm adding items to the SaleItems table.  Do I go ahead and grab the entire table once at the start of the app and put it into a static list or dictionary and then do searches against that?  In the back of my mind I'm thinking all of this could be very slow. I've added an index unto the Description column of the DeptItems table. 

    Or is it faster to search against the entity model as it is somehow. From what I've read I don't want to use Linq because it is slow.

    Could someone point me in the right direction?

    Thanks

    Harry

    Monday, September 30, 2019 4:57 PM

All replies

  • Hi Harry,

    In addition to using Linq, you can also use SqlQuery to execute SQL query statements on known entities.

        using (var context = new MyDBContext())
        {
            var posts = context.Posts.SqlQuery("SELECT * FROM dbo.Posts").ToList();
        }

    Here is the document you can refer to.

    Database.SqlQuery Method

    Regards,

    Kyle


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 1, 2019 5:31 AM
    Moderator
  • the way I'm doing it now is this:

     internal List<DeptItem> GetDeptItems()
            {
                Entities pse = new Entities();
                List<DeptItem> deptItems = new List<DeptItem>();
                var DeptItems = from di in pse.DeptItems                           
                                select di;
                foreach (DeptItem deptItem in DeptItems)
                {
                    deptItems.Add(deptItem);
                }
                return deptItems;
            }

    Which works - tried to test your code and couldn't get the context set correctly.

    Harry

    Tuesday, October 1, 2019 7:11 PM