How to optimize linq query for speed?

c#, linq, wpf

Solution

Tips to optimize speed:

- reducing columns reduces bandwidth required

- reducing rows, but introducing paging, reduced bandwidth by much more (usually)

- turning off change-tracking and identity-management (for example `ObjectTrackingEnabled` in LINQ-to-SQL) will reduce overheads post-processing the data

- using a pre-compiled query can sometimes help reduce pre-processing overheads

... but frankly, when we had this problem, we solved it "once and for all" by writing "dapper", and going old-school:

var list = connection.Query<CustomerViewModel>(
    "select {some specific cols} from Customers").ToList();

where `CustomerViewModel` is a simple POCO type not related to LINQ etc that just has the required values, for example:

class CustomerViewModel {
   public int Id {get;set;}
   public string Name {get;set;}
   // ...
}

This cuts out all unnecessary overheads, and is ideal when you just want to display data; additionally, the parameterization and materialization layers are very optimised (with strategy-caching, for optimum performance).

Problem

I have a linq query which fetches all records from the customer table to an observable collection as below: ``` customerList = new ObservableCollection<customer>(dbContext.customers); dgRecords1.ItemsSource = customerList; ``` the list is bound to a datagrid. The customer table contains many fields nearly hundred. But I display only a few fields on the datagrid. My question is whether bringing of only selected fields from the database using linq query increase the speed the customer screen? I need to filter and sometimes delete records from this list. Which is the best way to select few fields into observable collection, Can someone give some sample linq queries?

Original source