Have Geek Brain

Linq to SQL - how to dynamically filter on unselected columns

Problem

Using the SQL reference, I would like to filter on the columns that I do not want to return in the select statement. This works great as long as the query is built right away. In situations where I try to create a dynamic where clause, I get a compile-time error because the column is not included in the selection.

Example

WORK

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'm not sure how to get around this. Any help is appreciated!

+1


a source to share


2 answers


Thanks for your input Stephen. Your suggestion got me thinking and my final decision was based on that. However, I found a way to reuse the same object using a Lambda expression to indicate the selection at the end (just like where clauses):



q = q.Select (Function (logItem) New log with {.LogID = logItem.LogID, .DateLogged = logItem.DateLogged}

0


a source


Can't you choose then? 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 it will still generate useful SQL.

+1


a source






More articles:


All Articles