TL DR
- Filter your main table (Prod) with
.Where or at least use .Take() to limit the number of rows to something normal to display on the screen - Drop
.AsEnumerable() - you materialize entire tables into memory - Add foreign keys to your table, restore your DBML and use navigation instead of explicit joins
- Be careful what you put in the
Select projection - ddlShift = objTransactionGeneralController.GetAllShift() will be called for each row in the result set.
In detail
By applying .AsEnumerable() to your collections, for example:
var result = (from pr in db.Prods.AsEnumerable() join s in db.Shifts.AsEnumerable() on pr.ShiftID equals s.ShiftId join m in db.Modules.AsEnumerable() on pr.ModuleID equals m.ModuleId select new ...
Your current code leads to 3 explicit requests to Sql Server, each of which will load the entire table into memory: (for example, use Sql Profiler or LinqPad , etc.)
SELECT [t0].[ModuleId], ... other columns FROM [dbo].[Module] AS [t0]; SELECT [t0].[ShiftId], ... other columns FROM [dbo].[Shift] AS [t0]; SELECT [t0].[ProdID], [t0].[ShiftID], [t0].[ModuleID], ... other columns FROM [dbo].[Prod] AS [t0];
Given that you don't have a WHERE predicate at all, this might not be so much slower than a database connection. However, in general, doing this is not very good, because:
Using .AsEnumerable() , you remove the ability of Linq2Sql to parse the IQueryable expression IQueryable in native Sql. Generally, combining and filtering in a database will be faster and require less memory than doing this in memory. Assuming that Prod , Shift and Module are Linq.Table<> s, the solution here is to simply remove .AsEnumerable() - this will allow Linq to use IQueryable extension methods to combine, filter, aggregate, etc.
It is unusual to retrieve all the rows in a table and display them all at the same time on the same screen if the table size does not have a small number of rows. Usually you apply some kind of filter to the table.
As with the Bhaarat comment, if you correctly configured your foreign keys between tables (as your code example implies, it seems that there are developed join keys), when you import your tables into Linq2Sql DBML, you will also get navigation between entities and therefore, it will not be necessary to explicitly enter tables.
Put this in the Select project - ddlShift = objTransactionGeneralController.GetAllShift() will be called for each row in the result set. It looks expensive. Do it once, save the result in a local variable and, if necessary, specify it in each of the forecasts. Or change your ViewModel so that it does not repeat the link to each line, if necessary once.
Lazy loading can be a performance problem (problem 1 to N) - disable this in the DataContext with db.DeferredLoadingEnabled = false , and instead explicitly specify the depth of the graph to load with the corresponding LoadWith<> operators
Your code will now look like this:
using (var db = new DataClasses1DataContext()) { // Switch off Lazy Loading in favour of eager loading db.DeferredLoadingEnabled = false; var ds = new DataLoadOptions(); ds.LoadWith<Prod>(p => p.Shift); ds.LoadWith<Prod>(s => s.Module); // Do this once, not in a tight loop var ddlShift = objTransactionGeneralController.GetAllShift(); var ddlModule = objTransactionGeneralController.GetAllModule(); var result = db.Prods .Where(p => p.ProdID > 5 && p.ProdID < 10) // Apply some kind of filtering .Take(1000) // And / Or Limit the rows to something sane .Select(pr => new GlobalModel() { prodModelIndex = pr, // These 2 fields are actually redundant, as we now have navigation fields // for these off prodModelIndex shiftModel = s, moduleModel = m, ddlShift = ddlShift, ddlModule = ddlModule }).ToList(); return PartialView(result); }
And the generated SQL will be the only query with stricter row restrictions:
SELECT TOP 1000 [t0].[ProdID], [t0].[ShiftID], [t0].[ModuleID], [t1].[ShiftId] AS [ShiftId2], [t2].[ModuleId] AS [ModuleId2] FROM [dbo].[Prod] AS [t0] INNER JOIN [dbo].[Shift] AS [t1] ON [t0].[ShiftID] = ([t1].[ShiftId]) INNER JOIN [dbo].[Module] AS [t2] ON [t0].[ModuleID] = ([t2].[ModuleId]) WHERE [t0].[ProdID] BETWEEN 5 AND 10;