Linq to SQL - how to dynamically filter by columns not in select

Question

The Problem

Using Link to SQL I would like to filter by columns that I do not want returned in the select statement. This works fine as long as the query is built all at once. In situations where I try and build the where clause dynamically, I get a compile time error because the column is not included in the select.

---

Example

WORKS

Dim resultA = From logItem In dc.Log 
Where logItem.Message.Contains(searchText) 
Select logItem.LogID, logItem.DateLogged

DOES NOT WORK

Dim resultB = From logItem In dc.Log 
Select logItem.LogID, logItem.DateLogged

If (Not String.IsNullOrEmpty(searchText)) Then 
    resultB = q.Where(Function(logItem) 
    logItem.Message.Contains(searchText))
End If

---

I am not sure not how to get round this. Any help appreciated!

Answer 1

Can't you do the select afterwards? I don't know Vb, but something like:

Dim resultA = From logItem in dc.Log Select logItem

If (Not String.IsNullOrEmpty(searchText)) Then resultA = q.Where(Function(logItem) logItem.Message.Contains(searchText))

resultA = from logItem in resultA Select logItem.LogID, logItem.DateLogged

I think that will still generate useful SQL.

Answer 2

Thanks for your input Steven. Your suggestion got me thinking, and my final solution was based on this. However I found a way to reuse the same object by using a Lambda expression to specify a select at the end (in the same way as the where statements):

q = q.Select(Function(logItem) New Log With {.LogID = logItem.LogID, .DateLogged = logItem.DateLogged}