VB.NET LINQ to SQL Dynamic Where Clause and Joins

I'm pretty new to LINQ to SQL and have come across a problem with dynamic Where clauses and Joins. For exampe, if I have 2 parameters, one of them optional, I need to find a way to include/exclude the optional parameter from my query. In SQL I would build up statement as a string and then execute it. I have read an example by ScottGU - http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx - which handles a single table but I can't find a way to do this with multiple joins.

Here is the code I am using

Public Function GetCompByFacilityID(ByVal intFacilityID As Integer, ByVal strUsername As String) As Object
        Dim dc As New DCMainDataContext
        Dim query1 = Nothing

        Try
            query1 = (From cmp In dc.CMP_Comp _
                         Join fac In dc.Facilities On cmp.FacilityID Equals fac.FacilityID _
                         Join usrfac In dc.UserFacilities On fac.FacilityID Equals usrfac.FacilityID _
                         Join cmpfrm In dc.LU_FormOfComp On cmp.FormComplimentID Equals cmpfrm.FormComplimentID _
                         Where usrfac.Username = strUsername And fac.FacilityID = intFacilityID _
                         Select cmp.FacilityID, fac.FacilityName, cmp.CompDate, cmpfrm.FormComp, cmp.ReceivedFrom _
                         Order By FacilityName).ToList
        Catch ex As Exception
            Throw ex
        Finally
            dc = Nothing
        End Try
        Return query1
    End Function

If the value of intFacility ID is -1 I want to exclude this from the statement altogether. Does anyone have an example of how to do this?

Re: VB.NET LINQ to SQL Dynamic Where Clause and Joins

Hello

As far as I seeyou can use if statement to deal with the problem

Public Function GetCompByFacilityID(ByVal intFacilityID As Integer, ByVal strUsername As String) As Object
        Dim dc As New DCMainDataContext
        Dim query1 = Nothing

        Try
 If(Your Conditional Thing here) Then
 
 End If
            query1 = (From cmp In dc.CMP_Comp _
                         Join fac In dc.Facilities On cmp.FacilityID Equals fac.FacilityID _
                         Join usrfac In dc.UserFacilities On fac.FacilityID Equals usrfac.FacilityID _
                         Join cmpfrm In dc.LU_FormOfComp On cmp.FormComplimentID Equals cmpfrm.FormComplimentID _
                         Where usrfac.Username = strUsername And fac.FacilityID = intFacilityID _
                         Select cmp.FacilityID, fac.FacilityName, cmp.CompDate, cmpfrm.FormComp, cmp.ReceivedFrom _
                         Order By FacilityName).ToList
        Catch ex As Exception
            Throw ex
        Finally
            dc = Nothing
        End Try
        Return query1
    End Function

Re: VB.NET LINQ to SQL Dynamic Where Clause and Joins

Do you mean writing the query out multiple times? So for each condition (optional parameter) I would have the whole query again but with the inclusion of the optional parameter? This is what I'm trying to avoid really. If I have 20 parameters and then want to change part of the query itself it becomes time consuming. Certainly a step back from what is available in SQL. This is what I would do in SQL -

create proc sp_example
@strUsername varchar(50),
@intFacility varchar(50),
@param3 varchar(50),
@param4 varchar(50)
as

declare @sql varchar(500)
set @sql = '
select
	field1, field2
from
	tableName
where
	Username = ' + @strUsername

if @intFacility <> '-1'
set @sql = @sql + ' and Facility = ' + @intFacility
if @param3 <> ''
set @sql = @sql + ' and field3 = ' + @param3
if @param4 <> ''
set @sql = @sql + ' and field4 = ' + @param4

exec(@sql)

Sorry if I've misunderstood what you mean?

Re: VB.NET LINQ to SQL Dynamic Where Clause and Joins

WellIn my mind I think you need Dynamic LINQ by using CreateQuery<T> methodthis will let you dynamic pass a string as its parameter value to query things outAnd what's more importantCreateQuery<T> 's parameter can be combined by string splits

Sample

using (AdventureWorksEntities context =
    new AdventureWorksEntities())
{
    string queryString =
        @"SELECT VALUE contact FROM AdventureWorksEntities.Contacts
            AS contact WHERE contact.FirstName = @fn";

    ObjectQuery<Contact> contactQuery =
        context.CreateQuery<Contact>(queryString,
            new ObjectParameter("fn", "Frances"));

    // Iterate through the collection of Contact items.
    foreach (Contact result in contactQuery)
        Console.WriteLine("First Name: {0}, Last Name: {1}",
        result.FirstName, result.LastName);
}

Re: VB.NET LINQ to SQL Dynamic Where Clause and Joins

Do you have a sample of this in vb.net? I'm not sure how I would use this to select multiple fields from multiple tables?

Re: VB.NET LINQ to SQL Dynamic Where Clause and Joins

blued

Do you have a sample of this in vb.net? I'm not sure how I would use this to select multiple fields from multiple tables?

Hello again

This is the VB.NET sample

Using context As New AdventureWorksEntities()
    Dim queryString As String = "SELECT VALUE contact FROM AdventureWorksEntities.Contacts " & _
            " AS contact WHERE contact.FirstName = @fn"

    Dim contactQuery As ObjectQuery(Of Contact) = context.CreateQuery(Of Contact)(queryString, New ObjectParameter("fn", "Frances"))

    ' Iterate through the collection of Contact items.
    For Each result As Contact In contactQuery
        Console.WriteLine("First Name: {0}, Last Name: {1}", result.FirstName, result.LastName)
    Next
End Using

This is only a samplebut what I want to say to you is you can combine strings' splits together with ifstatements and then pass the combined whole string as a value in the CreateQuery's parameter to get what you want-

Re: VB.NET LINQ to SQL Dynamic Where Clause and Joins

Hi, I appreciate the C# code can be converted to VB, but I thought there might have been something in more detail on MSDN or similar? My searches aren't turning up much and it is how to put together a query that would return the same results as my original query that I'm having trouble with. I understand that you can use if...statements to build up the query but how does

query = From cmp In dc.CMP_Comp  _
                         Join fac In dc.Facilities On cmp.FacilityID Equals fac.FacilityID  _
                         Join usrfac In dc.UserFacilities On fac.FacilityID Equals usrfac.FacilityID  _
                        Join cmpfrm In dc.LU_FormOfComp On cmp.FormComplimentID Equals cmpfrm.FormComplimentID

become something that fits with

Dim queryString As String = "SELECT VALUE contact FROM AdventureWorksEntities.Contacts " & _ 
            " AS contact

This looks more like SQL than LINQ. Can I do something like the following?

Dim queryString As String =  "From cmp In dc.CMP_Comp "
                 queryString &= "Join fac In dc.Facilities On cmp.FacilityID Equals fac.FacilityID " 
                 queryString &= "Join usrfac In dc.UserFacilities On fac.FacilityID Equals usrfac.FacilityID " 
                 queryString &= "Join cmpfrm In dc.LU_FormOfComp On cmp.FormComplimentID Equals cmpfrm.FormComplimentID "
                 queryString &= "Select cmp.FacilityID, fac.FacilityName, cmp.CompDate, cmpfrm.FormComp, cmp.ReceivedFrom "
                 queryString &= "Order By FacilityName"
                 queryString &= "Where usrfac.Username=" & strUsername 

If intFacilityID <> -1 Then           
               queryString &= "  And fac.FacilityID = " & intFacilityID 
End If

Re: VB.NET LINQ to SQL Dynamic Where Clause and Joins

Hi,

Have you taken a look at the information discussed here? It explains the different types of queries supported in the Entity Framework:

Querying a Conceptual Model http://msdn.microsoft.com/en-us/library/bb738642.aspx

Perhaps you could make use of an Alias, as discussed here:

Query Builder Methods http://msdn.microsoft.com/en-us/library/bb896238.aspx

Thanks,

Cathy Miller