LINQ to SQL Query Optimization?
You're on the right track, but LINQ to SQL won't understand CustomFunction() so you'll need to switch to LINQ to Objects with AsEnumerable() before you can call it. You can also use let to capture the result of CustomFunction() once for use elsewhere in the query: var listFromSql = from p in Products join cat in Product_SubCategory_Mapping on p. UserId equals cat.
UserId join sub in SubCategories on cat. SubCategoryId equals sub. SubCategoryId where sub.
CategoryId == 19 select p; var list = from p in listFromSql.AsEnumerable() let trend = CustomFunction(p. ID) where trend > 100 select new { p, p. Vendor, trend } Update: To answer your listed questions: This is relatively straightforward SQL, so what L2SQL generates should be fine.
You can use L2SQL logging or SQL Profiler to confirm that the SQL is good enough See let above Skip() and Take() on an IQueryable (like listFromSql above) will translate into the appropriate SQL, limiting the result set sent across the wire Skip() and Take() on an IEnumerable just enumerate the sequence to get the requested results, but operate on the full result set returned from SQL.
You're on the right track, but LINQ to SQL won't understand CustomFunction() so you'll need to switch to LINQ to Objects with AsEnumerable() before you can call it. You can also use let to capture the result of CustomFunction() once for use elsewhere in the query: var listFromSql = from p in Products join cat in Product_SubCategory_Mapping on p. UserId equals cat.
UserId join sub in SubCategories on cat. SubCategoryId equals sub. SubCategoryId where sub.
CategoryId == 19 select p; var list = from p in listFromSql.AsEnumerable() let trend = CustomFunction(p. ID) where trend > 100 select new { p, p. Vendor, trend }; Update: To answer your listed questions: This is relatively straightforward SQL, so what L2SQL generates should be fine.
You can use L2SQL logging or SQL Profiler to confirm that the SQL is good enough. See let above. Skip() and Take() on an IQueryable (like listFromSql above) will translate into the appropriate SQL, limiting the result set sent across the wire.Skip() and Take() on an IEnumerable just enumerate the sequence to get the requested results, but operate on the full result set returned from SQL.
Forgot to mention that CustomFunction is a custom function in database . Still LINQ statement won't be able to recognize it? I used LINQPAD where it worked fine .
– Sonesh Dabhi Aug 29 at 4:01 I've never used L2SQL with a custom function - if it works in LINQPAD I expect it would work everywhere. Does it work with let? – dahlbyk Aug 29 at 4:04 Yes I used it like this : (from you in Users join cat in BusinessCategories on u.
UserId equals cat. UserId join sub in SubCategories on cat. SubCategoryId equals sub.
SubCategoryId let trend=CalculateDistance(u. Zipcode,"07306") where sub. CategoryId == 19 && trendBusinesses,trend}).
Skip(0). Take(10) – Sonesh Dabhi Aug 29 at 4:08 LINQ to SQL supports Scalar and Table Value Functions. EF has more limitations here.
– Jim Wooley Aug 29 at 14:50.
Since CustomFunction is a ScalarFunction in the database, LINQ to SQL should be able to evaulate it effectively. You may want to use LET to pull the value once, but check the generated SQL and the Query Execution Plan to see if it offers any improvement or if SQL Server automatically makes the appropriate optimizations internally. Var list = (from p in Products join cat in Product_SubCategory_Mapping on p.
UserId equals cat. UserId join sub in SubCategories on cat. SubCategoryId equals sub.
SubCategoryId let trend = CustomFunction(p. ID) where sub. CategoryId == 19 && trend > 100 select new { p, p.
Vendor, trend }) . Skip((pageNum - 1) * pageSize) . Take(pageSize); If there are associations between your elements, you may want to use them rather than joins.It doesn't change the generated query (much), but may be a bit more maintainable since the joins are abstracted out by the associations established in the model.
Var list = (from p in Products from cat in p. Product_SubCategory_Mappings let trend = CustomFunction(p.ID) where cat.SubCategory. CategoryId == 19 && trend > 100 select new { Product = p, p.
Vendor, trend}) . Skip(pageNum - 1) * pageSize) . Take(pageSize).
Thanks for pointing out about using associations . It surely looks a lot more cleaner. – Sonesh Dabhi Aug 30 at 23:03.
I discussed how you can easily misuse LINQ To SQL and bring your application to a crawl by causing performance issues via too many roundtrips to the database when accessing lazy-loaded properties. Most people familiar with O/R Mappers will recognize that classic problem. LINQ to SQL – Performance Tradeoffs: Less Database Roundtrips but Duplicated Data in Each Row?
The DataContext in LINQ To SQL has a property called LoadOptions which gives you the ability to fine tune your queries and decide when to prefetch properties and associations that are normally lazy-loaded. For example, if one wants to grab a Blog Entity and prefetch the Categories Associated with it that are normally lazy-loaded, you have the option of specifying the prefetch via the LoadOptions Property and a Lambda Expression. The end result is that via DataLoadOptions you can do some serious query tuning for performance optimization in your applications.
The whole process has made me more thrilled about LINQ To SQL than ever before.
How about something like var records = from rec in _DB. Records from user in _DB.Users. Where(x => x.
ManagerId == managerId || x. Id == managerId) where rec. UserId == user.Id select rec.
How about something like; var records = from rec in _DB. Records from user in _DB.Users. Where(x => x.
ManagerId == managerId || x. Id == managerId) where rec. UserId == user.Id select rec.
I think this looks a good shout, although the Where statement would have to change to user. ManagerID == managerId || user. ID == managerID) as the manager can have records aswell.
– James Nov 17 '09 at 15:52 @James: fixed, I think this should work. No idea what the performance implications are though, perhaps do some benchmark tests? – Kirschstein Nov 17 '09 at 16:17 Had to tweak it a little but all in all this helped me sort it out, thanks.
Yeah I will do some benchmarking aswell to test whether it is the most efficient way. – James Nov 17 '09 at 19:48.
Also, Once you find a LINQ2SQL solution for this one, make sure to use SQL profiler to see the execution plan and tweak as needed. HTH.
In between these two projections is a call to the AsEnumerable() operator that shifts processing at that point from a LINQ to SQL query into a locally executed one. Note   The AsEnumerable() operator, unlike ToList() and ToArray(), does not cause execution of the query. It is still deferred.
The AsEnumerable() operator merely changes the static typing of the query, turning a IQueryable (IQueryable (ofT) in Visual Basic) into an IEnumerable (IEnumerable (ofT) in Visual Basic), tricking the compiler into treating the rest of the query as locally executed. It is common in many applications to execute structurally similar queries many times. In such cases, it is possible to increase performance by compiling the query once and executing it several times in the application with different parameters.
This result is obtained in LINQ to SQL by using the CompiledQuery class. The Compile method returns a delegate that can be cached and executed afterward several times by just changing the input parameters. LINQ to SQL does not actually execute queries; the relational database does.
LINQ to SQL translates the queries you wrote into equivalent SQL queries and sends them to the server for processing. Because execution is deferred, LINQ to SQL is able to examine your entire query even if assembled from multiple parts. Since the relational database server is not actually executing IL (aside from the CLR integration in SQL Server 2005); the queries are not transmitted to the server as IL.
They are in fact transmitted as parameterized SQL queries in text form. Of course, SQL—even T-SQL with CLR integration—is incapable of executing the variety of methods that are locally available to your program. Therefore the queries you write must be translated into equivalent operations and functions that are available inside the SQL environment.
Most methods and operators on . Net Framework built-in types have direct translations into SQL. Some can be produced out of the functions that are available.
The ones that cannot be translated are disallowed, generating run-time exceptions if you try to use them. There is a section later in the document that details the framework methods that are implemented to translate into SQL. LINQ to SQL is more than just an implementation of the standard query operators for relational databases.
In addition to translating queries, it is a service that manages your objects throughout their lifetime, aiding you in maintaining the integrity of your data and automating the process of translating your modifications back into the store. In a typical scenario, objects are retrieved through one or more queries and then manipulated in some way or another until the application is ready to send the changes back to the server.
You can use sub-selects or joins: http://stackoverflow.com/questions/527819/linq-sub-select to get rid of iterations.
I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.