Click here to Skip to main content
15,879,844 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
We have inline queries and dynamic SQL created. Our Function is like this
VB
Public Function SQL_GetEvents_LinqToSql(Optional Top As String = "", Optional lSelect As List(Of String) = Nothing, Optional lJoins As List(Of String) = Nothing, Optional Where As String = "DateStart > GETDATE() AND Active = 1 AND es.DeletedOn IS NULL", Optional OrderBy As String = "EventTitle") As DataTable
    Dim sSelect As String = ""
    If Not lSelect Is Nothing AndAlso lSelect.Count > 0 Then
        sSelect = ", " & String.Join(", ", lSelect.ToArray())
    End If
    If lJoins Is Nothing Then lJoins = New List(Of String)
    lJoins.Add(" LEFT OUTER JOIN Images i2 ON e.EventID = i2.EventID AND i2.AzureURL <> '' AND i2.ImageType = 'Logo' ")
    Dim sJoins As String = ""
    If Not lJoins Is Nothing AndAlso lJoins.Count > 0 Then
        sJoins = " " & String.Join(" ", lJoins.ToArray())
    End If
    Dim SQL As String = ""
    SQL &= " SELECT " & Top
    SQL &= " i2.AzureURL,"
    SQL &= " es.EventSeriesID,"
    SQL &= " e.EventID,"
    SQL &= " es.SeriesTitle AS EventTitle, "
    SQL &= " es.SEOPageName,"
    SQL &= " es.Description,"
    SQL &= " es.UserID,"
    SQL &= " es.Website,"
    SQL &= " es.URL_Registration,"
    SQL &= " es.DiscountURL,"
    SQL &= " es.Promocode,"
    SQL &= " es.PromocodeDesc,"
    SQL &= " es.bHasKidsCourse,"
    SQL &= " es.KidsCourseDescription,"
    SQL &= " es.YoutubeEmbed,"
    SQL &= " e.DateStart,"
    SQL &= " e.DateEnd,"
    SQL &= " e.Address,"
    SQL &= " e.City,"
    SQL &= " e.State,"
    SQL &= " e.Province,"
    SQL &= " e.CountryID,"
    SQL &= " e.AD_Expires,"
    SQL &= " e.bInheritFromEventSeries"
    SQL &= sSelect

    SQL &= " FROM Events e JOIN EventSeries es ON e.EventSeriesID = es.EventSeriesID "
    SQL &= sJoins
    SQL &= "WHERE e.bInheritFromEventSeries = 1 "

    If Where <> "" Then Sql &= " AND " & Where

    Sql &= " UNION "

    Sql &= " SELECT " & Top
    Sql &= " i2.AzureURL,"
    Sql &= " e.EventSeriesID,"
    Sql &= " e.EventID,"
    Sql &= " e.EventTitle,"
    Sql &= " e.SEOPageName,"
    Sql &= " e.Description,"
    Sql &= " e.UserID,"
    Sql &= " e.Website,"
    Sql &= " e.URL_Registration,"
    Sql &= " e.DiscountURL,"
    Sql &= " e.Promocode,"
    Sql &= " e.PromocodeDesc,"
    Sql &= " e.bHasKidsCourse,"
    Sql &= " e.KidsCourseDescription,"
    Sql &= " e.YoutubeEmbed,"
    Sql &= " e.DateStart,"
    Sql &= " e.DateEnd,"
    Sql &= " e.Address,"
    Sql &= " e.City,"
    Sql &= " e.State,"
    Sql &= " e.Province,"
    Sql &= " e.CountryID,"
    Sql &= " e.AD_Expires,"
    Sql &= " e.bInheritFromEventSeries"
    Sql &= sSelect.Replace(" es.", " e.")

    Sql &= " FROM Events e "
    Sql &= sJoins
    Sql &= " WHERE e.bInheritFromEventSeries = 0 "
    If Where <> "" Then Sql &= (" AND " & Where).Replace(" es.", " e.")

    If OrderBy <> "" Then Sql &= " ORDER BY " & OrderBy
    Return Sql
End Function

Now I want to use LINQ as I dbml file with correct table association.
Now I want to make use of Linq like the following adding filterextra

http://stackoverflow.com/questions/13995212/technique-for-dynamically-adding-a-join-to-a-linq-query[^]


Please tell me its urgent
Posted
Updated 3-Mar-15 19:31pm
v2

1 solution

 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900