CODEWITHSUNDEEP
c#
Mohammad zinatee
Mohammad zinatee

Reputation: 23

Using contain In where statement in Entity Framework make performance too low

I have three tables in database :

Customer, SalesManTabels, CustomerSalesManTabels

Now I use this code in Entity Framework and C# to get all customer from the Customer table except customers that are contained in the CustomerSalesmansTabel for the same salesman:

 List<CustomerSalesManTabel> CustomerSalesManList = new List<CustomerSalesManTabel>();
    List<Customer> CustomerList = new List<Customer>();
    MedicalCustomersDBEntities PuplicEntityForSave = new MedicalCustomersDBEntities();
     private void LoadCustomerSalesManToList()
     {  
      IEnumerable<CustomerSalesManTabel> Cus = from a in PuplicEntityForSave.CustomerSalesManTabels.Include("SalesManTabel") select a;
      CustomerSalesManList.AddRange(Cus);  
     }

     private void LoadCustomerToList()
     {
      MedicalCustomersDBEntities md = new MedicalCustomersDBEntities();
      IEnumerable<Customer> Cus = from a in md.Customers select a;
      CustomerList.AddRange(Cus);
      }

    IEnumerable<Guid?> CustomerSalesManIEnumerable = CustomerSalesManList.AsEnumerable().Where(s => s.SalesManId == SalesManId).Select(s => s.CustomerId);

     var Cus = from nb in CustomerList
      where CustomerSalesManIEnumerable.Contains(nb.Id) == false
      select nb;

    checkedListBoxControlNonSelected.ValueMember = "Id";
    checkedListBoxControlNonSelected.DisplayMember = "FirstName";
    checkedListBoxControlNonSelected.DataSource = Cus.ToList<Customer>();

This code works, but my problem with Contains because I have a huge data, I have 12000 Customer when I use Contains it takes too long when I assigned "cus" to DataSourceof checklistbox

I want another way to do code like this but with high performance ?

Upvotes: 0

Views: 537

Answers (2)

Guffa
Guffa

Reputation: 700342

To start with, you are looping through a lot more than you need, because you are not realising the result in CustomerSalesManIEnumerable, so each time you use Contains it looks in the entire result from CustomerSalesManList (which is realised by AsEnumerable).

Realising the result as a list gives you less data to wade through. As you want to look for items in the result, you want a collection which uses a hash so that you get a fast lookup, like a HashSet:

HashSet<Guid?> CustomerSalesManIEnumerable = new HashSet(CustomerSalesManList.AsEnumerable().Where(s => s.SalesManId == SalesManId).Select(s => s.CustomerId));

You should however consider if this is possible to do in the database instead.

Upvotes: 0

SLaks
SLaks

Reputation: 887453

You're downloading all of the lists to the client, then filtering them in-memory.
This defeats the purpose of the Entity Framework.

You should run your queries directly against your DataContext:

from c in entities.Customers
where !entites.CustomerSalesManTabels.Any(s => c.Id == s.CustomerId)
select c

Upvotes: 3

Related Questions