Linq to Sql - Only select certain information (w/ Predicate Builder)

Question

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

Answer 1

You can't modify the "select list" (this is how I understood your question. I might have misunderstood it) with predicate builder (which builds boolean expressions). You should manually use stuff in System.Linq.Expressions namespace to do that but I suggest using Dynamic LINQ instead.

Answer 2

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

Answer 3

Sounds like you are doing the where on the projection and not the original Product. Do the projection Select p.Name, p.SomethingElse at the end after all the search criteria has been applied.