CODEWITHSUNDEEP
wpflinqsqlite
androider
androider

Reputation: 992

Linq Query very slow

 var data= (from a in context.Table1
              where a.Id== id
              select a);
  return data.ToList();

I have the above linq query in wpf project . The above query fetches about 0.2-0.3 million rows from SQLite database. The result fetching is fast but the while doing data.ToList(), it takes lots of time because of the no. of rows and hence my UI waits for 15-20 seconds to see the result. How should I proceed to solve this issue.

Also, if the above table contains foreign keys then while accessing the child element, it throws ObjectDisposeedException. I guess its due to lazy loading, so I tried to set the child elements explicitly. It worked but then my process got even more slower

Upvotes: 1

Views: 329

Answers (2)

bram_sharp
bram_sharp

Reputation: 36

Before you use the ".ToList()" means that the code you are still in mode "IQueryable", and when using ".ToList()" means you access the physical database. You can take some of the data in a way ".Take(x)" and ".Page(y)" on LINQ. You can try this method:

int page = 1;
int size = 10
var customers = tx.Customers
               .Where(x => x.....)
               .Skip((page - 1) * 10).Take(size)
               .ToList();

Upvotes: 0

Usman
Usman

Reputation: 3278

Of course the LINQ command "executes" in no time, below is just represents the query. 

 var data= (from a in context.Table1
              where a.Id== id
              select a);

The query is only executed once you iterate over it as return data.ToList(); which is exactly what the ToList method does.

I would advise you to use the Skip and Take operators in your pagers to narrow down the result queried from the database. Doing this, you only request the 10 or 20 elements or whatever you need, resulting in a much smoother experience.

Doing pagination will surely optimize your retrieval performance also..

Upvotes: 2

Related Questions