Linq to Sql - Select only certain information (with Predicate Builder)

advertisements

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