I am using Linq to Sql with Predicate Builder and am trying to optimize how much information is retrieved from the database. I would like to select only certain fields to display them in a gridview. When I select only what I want, the search parameters I add (see below) don't work, and neither does PredicateBuilder. Here's what I'm currently doing (that works, but gets EVERYTHING which is way too much info)
' Initial Setup ' Dim db As New MyDataContext() Dim results = From p In db.Products _ Select p ' Search ' If (testCase) Then results = results.Where(Function(p) p.SomeAttribute = 123) End If
If I change that to only select what I need, like this:
Dim results = From p In db.Products _ Select p.Name, p.SomethingElse
then I've noticed if the information is selected (ie I select p.SomeAttribute) then I can search (add the where clause) on that attribute, but if its not, I can't. And with predicate builder it only works if I select the entire item (ie select p). All this should be doing is creating SQL statements which don't have to select the attribute to search by it. How can I get this to work and select only what I need, but search by anything and keep prediate builder working? Any help MUCH APPRECIATED! Thanks
You could try to initially do a "select p" at the beginning, then add all your where clauses, and at the very end, select just what you need from it.
' Initial Setup ' Dim db As New MyDataContext() Dim results = From p In db.Products _ Select p ' Search ' If (testCase) Then results = results.Where(Function(p) p.SomeAttribute = 123) End If ' trim down the columns after you've added the wheres... Dim results2 = from p in results Select p.Name, p.SomethingElse