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?
As far as I see，you 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
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)
Well……In my mind I think you need Dynamic LINQ by using CreateQuery<T> method，this will let you dynamic pass a string as its parameter value to query things out——And what's more important——CreateQuery<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);
}
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 sample，but what I want to say to you is you can combine strings' splits together with if……statements and then pass the combined whole string as a value in the CreateQuery's parameter to get what you want：-）
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
blued
Member
125 Points
163 Posts
VB.NET LINQ to SQL Dynamic Where Clause and Joins
Apr 12, 2012 02:01 PM|LINK
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 FunctionIf 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?
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: VB.NET LINQ to SQL Dynamic Where Clause and Joins
Apr 14, 2012 01:30 AM|LINK
Hello：）
As far as I see，you 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 Functionblued
Member
125 Points
163 Posts
Re: VB.NET LINQ to SQL Dynamic Where Clause and Joins
Apr 16, 2012 07:50 AM|LINK
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 -
Sorry if I've misunderstood what you mean?
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: VB.NET LINQ to SQL Dynamic Where Clause and Joins
Apr 16, 2012 07:58 AM|LINK
Well……In my mind I think you need Dynamic LINQ by using CreateQuery<T> method，this will let you dynamic pass a string as its parameter value to query things out——And what's more important——CreateQuery<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); }blued
Member
125 Points
163 Posts
Re: VB.NET LINQ to SQL Dynamic Where Clause and Joins
Apr 18, 2012 08:40 AM|LINK
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?
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: VB.NET LINQ to SQL Dynamic Where Clause and Joins
Apr 18, 2012 08:52 AM|LINK
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 UsingThis is only a sample，but what I want to say to you is you can combine strings' splits together with if……statements and then pass the combined whole string as a value in the CreateQuery's parameter to get what you want：-）
blued
Member
125 Points
163 Posts
Re: VB.NET LINQ to SQL Dynamic Where Clause and Joins
Apr 18, 2012 09:11 AM|LINK
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.FormComplimentIDbecome something that fits with
Dim queryString As String = "SELECT VALUE contact FROM AdventureWorksEntities.Contacts " & _ " AS contactThis 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 IfCathy Mi - M...
Member
741 Points
165 Posts
Microsoft
Re: VB.NET LINQ to SQL Dynamic Where Clause and Joins
Apr 24, 2012 08:13 PM|LINK
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