The best solution I found was to go ahead and create a Table-Valued Function in SQL that produces the results, such as ::
CREATE function [dbo].[getMatches](@textStr nvarchar(50)) returns @MatchTbl table(
Fullname nvarchar(50) null,
ID nvarchar(50) null
)
as begin
declare @SearchStr nvarchar(50);
set @SearchStr = '%' + @textStr + '%';
insert into @MatchTbl
select (LName + ', ' + FName + ' ' + MName) AS FullName, ID = ID from employees where LName like @SearchStr;
return;
end
GO
select * from dbo.getMatches('j')
Then, you simply drag the function into your LINQ.dbml designer and call it like you do your other objects. The LINQ even knows the columns of your stored function. I call it out like this ::
Dim db As New NobleLINQ
Dim LNameSearch As String = txt_searchLName.Text
Dim hlink As HyperLink
For Each ee In db.getMatches(LNameSearch)
hlink = New HyperLink With {.Text = ee.Fullname & "<br />", .NavigateUrl = "?ID=" & ee.ID}
pnl_results.Controls.Add(hlink)
Next
Incredibly simple and really utlizes the power of SQL and LINQ in the application...and you can, of course, generate any table valued function you want for the same effects!