asp.net LINQ vs Datatable

Question

I have datagrid in asp.net page. Also theer are filters and search button for grid. I have following possible option for coding

1. Apply filters for grid inside stored procedure
2. Fetch all data for grid and apply filters + search text using LINQ
3. Fetch all data for grid in datatable and apply filter + search text as a datatable filters.

I am not able to decide which one is best way of coding. Also i am using 3 layer architecture and hence not able to decide which layed is best suitable to apply filters.

Please help?

Answer 1

2.Fetch all data for grid and apply filters + search text using LINQ

When you are using LINQ and has a remote datasource you can use IQuerable you describe what you want ==> that you build a Expression tree that when it is executed is interpreted (to SQL in your case) (compare IEnumerable vs I Queryable)

My vote: So have a try on using Linq as it has some advantages a) type safe b) my experience is that LINQ helps you create good structured solutions where you have decoraters like Take(10).OrderBy(xx).Skip(30)

The disadvantage I have found is that you get less control on what SQL is created and you need to spend some time on tuning...

Good books c# 3.0 in a NutShell LinqPad LINQ in Action

Answer 2

Hey,

If you have a lot of data, applying a filter at the database (only selecting the exact number of records you need) or in your database query is a good idea. For instance, if you have 3 million records, and you only show 50 records at a time, only loading the 50 records you need, and using the LINQ .Skip().Take() methods to page is a good idea. If you decide to use stored procs, that's viable too, and SP's work in LINQ and DataTable's.

Filters maybe harder to apply outside of the database; this can be made easy in data tables or LINQ. It depends how easy or hard it will take the effort to apply filters, whether they could be easily applied in the DB query, or if you need to apply filters in your code-behind by loading all the data and looping through.

Whether you go DataTables or LINQ, either is a viable approach. LINQ is the newer technology and here to stay; DataSets will be around for a while, but don't expect any new dataset features because ADO.NET Entity Framework is the new dataset.

Hope this makes any sense.